on vlookup

[venky24]

vlookup function returns the first value that is encountered. Is there any option to get the data related to 3rd occurance or so. to clear my query i did like to use the following example.


I got this list of bills along with vendor codes

1 VEN1122 MAY PAID-3/7

2 VEN1122 JUN

3 VEN1122 JUL

and so on.


Now using vlookup if i search for VEN1122 i will get the resultant as REC11. Is there a possibility to get the details of second (or for that matter 3 or 4) occurance


Thanks for your responses and help. In anticipation.

 

[Luke M]

You could use a helper column with this formula: (I'll assume you insert it as a new col B)

=A1&"__"&COUNTIF($A$1:$A1,A1)


Then, change your VLOOKUP to reference the helper column and change it to:

=VLOOKUP("VEN1122"&"__"&3,B:C,2,FALSE)


I used text strings, but it could easily incorporate cell references:

=VLOOKUP(D1&"__"&E1,B:C,2,FALSE)

 

[prasaddn]

check this link for more explanation of what Luke M is suggesting.


http://chandoo.org/wp/2010/11/10/vlookup-second-value/


Regards,

Prasad DN