Vlookup based on designated occurrence of value

  • Thread starter Thread starter bill9340
  • Start date Start date
B

bill9340

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
 
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 3r
occurrence of 'Red' in Col A.

Does that help?

•••••••••••
Regards,
Ro
 
That is way above my level, I would have never figured it out. It
worked like a charm, thanks!
 
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))
Click to expand...
....

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.
 
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))
Click to expand...
....

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.
 
Back
Top