VLookup - Choose Which Occurence of Lookup value to bring back

[CHACHING]

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 ????

 

[Sheeloo]

You can add an occurence number to the lookup value and then use that to do
the lookup...

To get the occurence no. you can type this formula in any column (say Col B)
row 1 and copy down
=COUNTIF($A$1:A1,A1)
then you can can concatenate A & B in Col C and use that as the lookup col.

VLOOKUP itself does not allow you to pick the occurence no. it returns the
first match.

 

[Domenic]

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.