Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Vlookup To Return Multiple Values

1. I agreed to these rules
Join Date
14th April 2007
Posts
17

## Vlookup To Return Multiple Values

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

Excel Video Tutorials / Excel Dashboards Reports

2. Six Strings Guest

## Re: Vlookup To Return Multiple Values

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

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Vlookup To Return Multiple Values

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?

4. Super M‌oderator
Join Date
4th July 2004
Location
Posts
2,371

## Re: Vlookup To Return Multiple Values

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!

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
14th January 2011
Posts
5

## Re: Vlookup To Return Multiple Values

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?

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Vlookup To Return Multiple Values

tig08ger,

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno