Multiple-Column-VLOOKUP

VLOOKUP With 2 inputs and 2 lookup Columns

How To VLOOKUP with 2 Inputs and 2 LOOKUP Columns

Every now and then you have to get a bit creative with multiple Excel formulas to overcome particular issues.

This time we needed to take 2 input values, look each one of them up in 2 separate columns and bring back the result from a third column.

There were a few array formulas that we played with (one being the famous INDEX MATCH). If the result we needed to bring back was numeric, a simple SUMIFS formula would have worked. However, the result we needed was a text value.

Here’s what we finished with…

{=VLOOKUP($A5&B5,CHOOSE({1,2},$D$5:$D$9&$E$5:$E$9,$F$5:$F$9),2,0)}

To Explain

First thing to notice is the curly braces wrapping the formula. You get this when you hold Ctrl + Shift before hitting Enter. And this is the way to enter an Array Formula into Excel.

Multiple-Column-VLOOKUP

The VLOOKUP joins the values in cells A5 and B5 as the lookup_value (in this case YesWe're.
The table_array contains a CHOOSE formula that creates a virtual table concatenating D5:D9 with E5:E9 as column 1 and then F5:F9 as column 2. Using {1,2} as the index_num parameter means that you want to return both the first and the second values in the CHOOSE formula.

This is what the virtual lookup table will look like…

Multiple-Column-VLOOKUP

Now it should starts to make sense

Our lookup value was YesWe're the table it looks in is the virtual table and when it finds our lookup value it brings back the value in column 2. Which is Great. Great!

Absolutely mind blowing

No ratings yet.

Please rate this