Excel - multi-VLOOKUP?

bazzabear

bazzabear

bazzabear

Soldato
Joined
2 Nov 2013
Posts
4,247
Is there any formula I don't know about that allows you to do the equivalent of a VLOOKUP with multiple answers?

I want to be able to enter a value from column A, and have it return all the related values from column B.
 
You'll need to get creative with array formulae to achieve this:

http://office.microsoft.com/en-gb/e...d-examples-of-array-formulas-HA010342564.aspx

Here is an example solution:

https://dl.dropboxusercontent.com/u/57487477/MultipleVLookup.xlsx


This is the formula (entered by pressing Ctrl+Shift+Enter)

Code: 
=IFERROR(INDEX($A$1:$A$8,SMALL(IF($B$2:$B$8=$B$12,ROW($B$2:$B$8)),$A15)),"")

To break it down, this part does the 'lookup' by checking every cell in b2:b8 against b12 and returning the row of the match if it does:

Code: 
IF($B$2:$B$8=$B$12,ROW($B$2:$B$8))

That will give you a series of row numbers and you need to extract them out. The SMALL function returns the nth smallest value, i.e. you want the lowest row first (first match), second lowest row next (second match), etc:

Code: 
SMALL( <array of row numbers that correspond to matches> ,$A15)

Then the INDEX function pulls out the answer from the range you want:

Code: 
INDEX($A$1:$A$8, <row number of nth match> )

Hope that makes sense.
 
I've found this:

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)

Which looks at a small table from A1 to B7, looks for a value entered in A10 through A1-A7 and returns the related B value (and repeated versions of this formula will reutrn 2nd, 3rd, xth, related values). But I have to admit, I'm struggling to decipher how that language translates to this.
If anyone could split that into chunks and try to explain to me (I've not used array formulae before, but I'm comfortable with things like VLOOKUP and COUNTIFS) I'd be eternally grateful!
 
It's basically the same formula :)

In my formula A15 = 1, i.e. return the 1st match, your example uses ROW(1:1) instead (the row number of row 1 is... 1, so return the 1st match)

And the 2 right at the very right of the formula means column B.

Array formulae are incredibly powerful once you get your head around them, took me a while to do that though!
 
You must log in or register to reply here.
Back
Top Bottom