VLOOKUP only finds the first entry in a table...

[Geoff Taylor]

... so how would I find the second entry?


I can do it with VBA but I'd like to be able to do it with formulas if possible.

 

[NBVC]

Something like:

=INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100="X",ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($C$2:$C2)))

confirmed with CTRL+SHIFT+ENTER and copied down.

where your lookup value is "X", looking in A2:A100 and extracting from B2:B100.

Formula extracts each consecutive match until you get errors... which you can hide by wrapping IFERROR() around the formula first.

 

[Geoff Taylor]

I haven't been back to this board since I posted this question - I just wanted to say "Thanks"