VLOOKUP

  • Thread starter Thread starter daver676
  • Start date Start date
D

daver676

Can I use VLOOKUP to sort through all records of the same
criteria. Ex.

I want VLOOKUP to find the number in the secong column =
234 in the second column:

same 123
same 123
same 234

Using an IF statement with a VLOOKUP function or two, how
could I get Excel to find the 234, instead of 123. Or is
there another funstion I could use?

Wow, that sounds confusing, but I hope someone can help!

Thanks

Dave
 
daver676 said:
Can I use VLOOKUP to sort through all records of the same
criteria. Ex.

I want VLOOKUP to find the number in the secong column =
234 in the second column:

same 123
same 123
same 234

Using an IF statement with a VLOOKUP function or two, how
could I get Excel to find the 234, instead of 123. Or is
there another funstion I could use?
Click to expand...

Here's an approach using only built-in functions. This is an array formula.

=INDEX(Rng,SMALL(IF(INDEX(Rng,0,1)=LookupValue,
ROW(Rng)-CELL("Row",Rng)+1),Instance),ColumnIndex)

where Rng is the multiple column range, LookupValue is the placeholder for
your 'same' value, Instance would be 3 in your example, and ColumnIndex
would be 2 in your 2-column range.
 
Back
Top