Provide Second and Third Lookup Value with same Unique Value

[Thomas Kuriakose]

Respected Sirs,

Kindly let me know how to get the second and third lookup value from a range when the unique identifier is the same.

Kindly find attached the workbook with the requirement.

Thank you very much,

with regards,
thomas

 

[Hui]

Thomas

have you read: http://chandoo.org/wp/2010/11/10/vlookup-second-value/
see it implemented here:

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this link and for the solution provided in it.

I did not read this previously.

Thanks once again for sharing this.

with regards,
thomas

 

[Hui]

I have attached its implementation above

 

[bosco_yip]

One formula way without helper :

1] It is generally use INDEX/SMALL to return Nth occurrence.

In F2, array formula ( confirm enter with pressing SHIFT+CTRL+ENTER 3 keys) copy down :

=IF(C2="","",INDEX(B$2:B$7,SMALL(IF(A$2:A$7=D2,ROW(A$2:A$7)-ROW(A$2)+1),LEFT(C2))))

2] It is a bit complicated use VLOOKUP to return Nth occurrence.

In G2, array formula ( confirm enter with pressing SHIFT+CTRL+ENTER 3 keys) copy down :

=IF(C2="","",VLOOKUP(D2&LEFT(C2),IF({1,0},A$2:A$7&COUNTIF(INDIRECT("A2:A"&ROW(A$2:A$7)),D2),B$2:B$7),2,0))

3] Please see attached file

Regards
Bosco

 

[Thomas Kuriakose]

Respected Hui Sir,

Thank you very much once for the implementation.

Respected David Sir,

Thank you very much for the two additional methods provided.

I would like to thank all once again for making Chandoo, the best forum for all the amazing solutions provided, for the wealth of expertise and for all the knowledge shared.

with regards,
thomas