MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return list with VLOOKUP


Posted by Alex Shahidi on January 10, 2002 11:02 AM

How do I return a list with VLOOKUP? Ex:

1 2
A. Jan Name1
B. Feb Name2
C. Jan Name3

Desired result:
Name1
Name3

I want to return both name1 and name3 with a lookup function (not a filter) that looks up Jan. Thank you.

Alex


Posted by Barrie Davidson on January 10, 2002 11:08 AM

You can't...

Lookup functions will only return the first instance. The only way to get what you want is using filters.

Sorry :(
BarrieBarrie Davidson

Posted by Juan Pablo G. on January 10, 2002 11:16 AM

Wait a sec...

i answered a similar question yesterday, here's what i got. I assumed the data is in E1:F7 and the lookup value (Jan) in this case is in A1. Put this formula, for example, in B1 and drag down.

=IF(COUNTIF($E$1:$E$7,$A$1)>ROW(A1)-1,VLOOKUP($A$1,$F$7:INDIRECT("E"&MATCH($A$1,$E$1:$E$7,0)+ROW(A1)-1),2,0),"")

Juan Pablo G.

Posted by Barrie Davidson on January 10, 2002 12:02 PM

Juan, what about...

If the table looks like this:

Jan Name1
Feb Name2
Jan Name3
Jan Name4

Your formula will give me:
B1 Name1
B2 Name3
B3 Name3

So it kind of works, but won't given the situation above (more than 2 matches). Of course, I'm sure you'll be able to come up with a solution and I look forward to seeing if it can be done.

Regards,
BarrieBarrie Davidson

Posted by Juan Pablo G. on January 10, 2002 1:22 PM

Re: Juan, what about...

Thanks for catching that, it worked with the set of data i had, so i assumed it worked. I got this solution that i don't love, i wanted to come up with just ONE formula and drag it down, but...

In B1 put

=IF(COUNTIF($E$1:$E$11,$A$1),VLOOKUP($A$1,$E$1:$F$11,2,0),"")

in B2 (And drag down) put

=IF(COUNTIF($E$1:$E$11,$A$1)>ROW(A2)-1,VLOOKUP($A$1,$F$11:INDIRECT("E"&MATCH($B1,$F$1:$F$11,0)+1),2,0),"")

Juan Pablo G.

Posted by Barrie Davidson on January 10, 2002 1:41 PM

Very cool Juan, I knew you'd figure it out (nt)