Vlookup based on designated occurrence of value
 

Easier to show than explain, assume first column has colors & second has
letters:


Red A
Blue B
Red C
Yellow D
Red E
Blue F

What set of functions can I put in a cell so that it will look for,
let's say, the third row that Red occurs in, then return the value E
from the adjacent column.

Thank you in advance
Bill


--
bill9340
------------------------------------------------------------------------
bill9340's Profile: http://www.excelforum.com/member.php...o&userid=28516
View this thread: http://www.excelforum.com/showthread...hreadid=481724

Vlookup based on designated occurrence of value
 

Using your data in cells A1:B6 . . .
D1: Red
D2: 3

D3:
=INDEX($B$1:$B$6,MATCH($D$2,--COUNTIF(INDIRECT("$A$1:$A$"&ROW(1:6)),$D$1),0))

Note: Commit that array formula by holding down the [Ctrl] and [Shift]
keys when you press [Enter].

That function will return the Col B value that corresponds to the 3rd
occurrence of 'Red' in Col A.

Does that help?

•••••••••••
Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=481724

Vlookup based on designated occurrence of value
 

That is way above my level, I would have never figured it out. It
worked like a charm, thanks!


--
bill9340
------------------------------------------------------------------------
bill9340's Profile: http://www.excelforum.com/member.php...o&userid=28516
View this thread: http://www.excelforum.com/showthread...hreadid=481724

Vlookup based on designated occurrence of value
 

Ron Coderre wrote...
Using your data in cells A1:B6 . . .
D1: Red
D2: 3

D3:
=INDEX($B$1:$B$6,MATCH($D$2,--COUNTIF(INDIRECT("$A$1:$A$"&ROW(1:6)),$D$1),0))
....

You could use a shorter formula that avoids volatile function calls.

=INDEX($B:$B,SMALL(IF($A$1:$A$6=$D$1,ROW($A$1:$A$6 )),$D$2))

Also an array formula.

Vlookup based on designated occurrence of value
 

Ron Coderre wrote...
Using your data in cells A1:B6 . . .
D1: Red
D2: 3

D3:
=INDEX($B$1:$B$6,MATCH($D$2,--COUNTIF(INDIRECT("$A$1:$A$"&ROW(1:6)),$D$1),0))
....

You could use a shorter formula that avoids volatile function calls.

=INDEX($B:$B,SMALL(IF($A$1:$A$6=$D$1,ROW($A$1:$A$6 )),$D$2))

Also an array formula.