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)
