Vlookup To Return Multiple Values

[adnanriaz]

Is there a way where i can vlookup a column and return all matches if there are multiple values?

[Six Strings]

There was a thread on this just within the last couple of days or so.

Please read the responses from Shg in this thread.

[shg]

Please read the responses from Shg in this thread.

Thanks for the plug, Six Strings. In that post, I provided a formula for looking up catenated cells in catenated columns -- not what the OP is looking for here, by my reading.

I think the OP here is looking to return the 1st, 2nd, 3rd ... match of a VLOOKUP (or the equivalent). I expect it could be cobbled together by using INDEX and MATCH, using some kind of catenation with COUNTIF. I tried briefly, but couldn't finish it. A helper column would certainly work.

adnanriaz, can you post an example?

[Domenic]

Assuming that A2:B10 contains the lookup table, D2 contains the lookup value, and the corresponding values from Column B are to be returned, try the following...

E2:

=COUNTIF(A2:A10,D2)

F2, copied down:

=IF(ROWS(F$2:F2)<=$E$2,INDEX($A$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(F$2:F2)),2),"")

...confirmed with CONTROL+SHIFT+ENTER. Note that the number 2 at the end of the formula determines the column from which to return the corresponding values. In this case, the corresponding values are to be returned from the second column or Column B. See the attached file.

Hope this helps!

[tig08ger]

I found this and it works great for what I am trying to do. However, I found that if my table information is on another sheet (which is where I need it to be) I can't get it to work. Any ideas?

[AAE]

tig08ger,

Please do not post your question in threads started by others - - this is known as thread hijacking.
Always start a new thread for *YOUR* question. If you find it helpful to clarify your needs you can include a link to this ( or any other ) thread.

When starting a new thread be sure to give it a search-friendly title that aptly describes your need.