In article ,
CHACHING wrote:

I have a table where the lookup value appears multiple times .....

ie Table Looks like this

Value Ref
3 A
5 Z
6 B
7 C
3 F
5 G

With standard lookup functionality, If I looked-up value 3, I would bring
back "A"
I'd like a method where I can specify which occurence of "3" in the table it
brings back data from. (ie to be able to do a lookup & specify to bring back
data from the second record of "3" rather than the default first)

Any Ideas ????


Assumptions:

A2:B7 contains the data

D2 contains the lookup value

E2 contains the occurrence of interest

Formula:

=INDEX(B2:B7,SMALL(IF(A2:A7=D2,ROW(A2:A7)-ROW(A2)+1),E2))

....confirmed with CONTROL+SHIFT+ENTER.

--
Domenic
http://www.xl-central.com