## 4 different ways to perform LOOKUP with 2 lookup values

We know that VLOOKUP is very useful. At the same time, we know that VLOOKUP has its limitations. E.g. VLOOKUP only looks from left to right; VLOOKUP only handle one lookup value.  For a simple situation shown below, VLOOKUP doesn’t seem to work (directly).

No worry. There are at least four workarounds!

1. Using a helper column
2. Using CHOOSE to recreate the Table Array for VLOOKUP
3. Using INDEX, MATCH and = Operator
4. Using SUMPRODUCT

### 1. Using a helper column

Most of the time, complicated problem will become easier and manageable when it is broken down into small pieces. Same applies in formula building in Excel.
Take below example:

By CONCATENATING “Month” and “Store” in column A (note: better to put the helper column to the left of the original Table as VLOOKUP looks from left to right.), we create the lookup value required in column A.  As a result, with the helper column A (which could be hidden later), the problem can be solved easily by

`=VLOOKUP(G1&G2,\$A\$2:\$D\$10,4,FALSE)`

Note: we need to combine the Month (G1) and Store (G2) as the lookup value

## Non-helper column approach

Well, for any reason a helper column is not an option, we may do it by array formula. At least two different ways to perform the job:

### 2) Using CHOOSE to recreate the Table Array for VLOOKUP

This is a bit advance. The tricky part is to re-create the Table Array that is suitable for VLOOKUP by using CHOOSE.
CHOOSE({1,2},B2:B10&C2:C10,D2:D10) does two things

1. Combine “Month” and “Store” into a single array as “JanTW”;”FebTW”, etc……
2. Join the two arrays into a “table” of two columns

When we evaluate the result of the above CHOOSE (by selecting the whole CHOOSE formula in the formula bar and then press F9, we get the following result:

`{"JanTW",171;"FebTW",177;"MarTW",179;"JanLP",199;"FebLP",130;"MarLP",103;"JanHC",122;"FebHC",177;"MarHC",200}`

To visualize it in a Table format, it looks like the below:

Now the whole formula makes sense:

```=VLOOKUP(G1&G2,CHOOSE({1,2},B2:B10&C2:C10,D2:D10),2,FALSE)
CTRL SHIFT ENTER (not just ENTER)
```

IMPORTANT: As this is an array formula, we have to input CTRL SHIFT ENTER to tell Excel that we are going to input array formula. You will see the {} in the formula bar if array formula is input successfully.

• Note: For both 1) and 2), the robust way to combine the lookup values should include a delimiter in between, e.g. A1 & “|” & B1 .  Please read Pay attention when concatenate for explanation.

3) Using INDEX, MATCH and = Operator

```=INDEX(D2:D10,MATCH(1,(B2:B10=G1)*(C2:C10=G2),0))
CTRL SHIFT ENTER```

This formula actually looks more intuitive. How?

First let’s understand what the (B2:B10=G1)*(C2:C10=G2) does?

• B2:B10=G1 asks Excel to evaluate if B2=G1, if yes, it returns TRUE; if no, it returns FALSE. Then do the same for B3=G1, B4=G1…… until B10=G1. An array of TRUE/FALSE will be the result.
• C2:C10=G2 basically does the same thing but look at different values.

When we compare the two array of TRUE/FALSE by multiplying them, only TRUE * TRUE gives the result of 1 (TRUE).  A picture tells more. Let’s take a look at the screenshot below:

Bingo, we can identify the row that meets both lookup values.

Now you should be able to use MATCH to identify the position of the matching values. Then use INDEX to return the result required.

• =INDEX(D2:D10,MATCH(1,(B2:B10=G1)*(C2:C10=G2),0)) gives
• =INDEX(D2:D10,MATCH(1,{0;0;0;0;0;1;0;0;0},0) gives
• =INDEX(D2:D10,6) returns
• \$103, the correct result!

### 4) Using SUMPRODUCT

SUMPRODUCT is a powerful function in Excel. I am going to write separate posts about SUMPRODUCT later. Although SUMPRODUCT seems to work in our example, it actually fails if the result we want is text instead of value.

To understand that, we have to understand how SUMPRODUCT behaves. If you want to know more about SUMPRODUCT, stay tuned! 🙂

### So, which one do I use?

Although options 2) and 3) works fine, I prefer option 1) under normal circumstance.  Why?

In daily work life, I believe it is more important to build a spreadsheet that is manageable and readable by most users (including myself)… Not every one in workplace is Excel guru. If every one is, we don’t have to spend so much time to fight with spreadsheets.

As such, let’s obey the KISS principle whenever possible.

This entry was posted in Formula and tagged , , , , . Bookmark the permalink.

### 35 Responses to Perform VLOOKUP with 2 lookup values

1. Jason says:

Hi,
I tried using your “index match =” method on excel 2016 and it doesn’t work. I recreated your spreadsheet, and it still wouldn’t work.

The result is always #N/A.
I used the “show calculation steps” button and it showed me how it took the values and compared them with each other but got false and it never continued afterwards. How do I make sure even if the values don’t match at first, excel would continue down the range?

Like

• MF says:

Hi Jason,
That is an array formula and require Ctrl+Shift+Enter.
Hope it helps.

Like

2. ram says:

i want to combine the formulas below into one

(B2)-> VLOOKUP(A2,BASS,2,FALSE)
(b3)->VLOOKUP(A2,BASS,3,FALSE)
B4-> IF(AND(B2=”S”,B3=”M”,”YES”,”NO”)

Like

3. Anshul Sood says:

Thanks for the wonderful explanation. it works great!!

Like

• MF says:

Like

4. Aeh says:

Just wanted to say thank you!

Like

• MF says:

You are welcome!

Like

Like

6. ExcelVbaLab says:

Hi

Really liked idea of using choose, but sumproduct would fail if there is multiple matching items..

Cheers!!
ExcelVbaLab.Com

Like

• MF says:

If there is multiple matched records, none of the above would work as they will return the first matched record, like VLOOKUP does.
So we need to be careful and really study the data first.
Cheers,

Like

• Igor says:

Hi Fung,
Like you said below – “VLOOKUP will return the first matched value found” or above, in case there are multiple matched records what formula should we use? Maybe Index+Match will work better?

Like

• MF says:

Hi Igor,
If you need to lookup multiple matched records, VLOOKUP is not the function you are looking for. Indeed, there is no simple function to achieve that. We need a combination of functions and array formulation.
As a simple alternative, you may consider using an extra helper column to identify the incidence of occurrence for an item. The formula would be =COUNTIF(\$C\$2:C2,C2) when we refer to example 1 in this post.
With that helper column, we can then concatenate the number with the lookup item and deploy the method we described example 1.
You raised a very good question and I will put it on my list and write a post in the future.
Cheers,

Like

• Igor says:

Thanks, will wait for your study on this topic.
P.S. I checked Index+Match doesn’t work. Same as Vlookup those two returning only first matched record. In my case I can’t use something like SUMIF, as I’m looking for employee’s rate, which can’t be combined. I differently need something different if array has more than one unique value.

Like

• Igor says:

Also want to add – if Using a helper column we can use this formula:
=SUMIF(A2:D10,G1&G2,D2:D10). Without helper column we can use this formula: =SUMIFS(D2:D10,B2:B10,G1,C2:C10,G2)

Like

• MF says:

just to remind, it works only when
1) there are no duplicates;
2) the result you expected is number, not text.

Like

7. andrewsipahutar says:

Hi MF!
I can’t understand about CHOOSE function in array formula.. Why do we have to use choose? Would u explain it in simple way? Thank you very much 🙂

Liked by 1 person

• MF says:

Hi Andrewsipahuta,
You gave me a challenging question: to answer it in a simple way… 😛

I will try my best:
First you need to understand what CHOOSE does.
The syntax:
CHOOSE(index_num,value1,value2,…)
Depends on the index number, it returns the corresponding value from a list of values you feed into the function. For example:
=CHOOSE(1,”A”,”X”) returns “A”
=CHOOSE(2,”A”,”X”) returns “X”
=CHOOSE({1,2},”A”,”X”) returns an array of “A”,”X”
To go a bit further,
=CHOOSE({1,2},{“A”;”B”;”C”},{“X”;”Y”;”Z”}) returns an array of {“A”,”X”;”B”,”Y”;”C”,”Z”}

So basically it “re-construct” a table_array with two columns:
“A”,”X”;
“B”,”Y”;
“C”,”Z”

Now going back to our example in the post, “Month” and “Store” are concatenated into a single array. And we need CHOOSE to “re-construct” the Table_array required for VLOOKUP.

Hope you can understand it better now.
Cheers,

Liked by 1 person

• Hi MF!
I understand now.. So, we proposing “a new array” with Choose({1,2},…. Rather than “choosed” from (“A” & “X”), we selected both columns with that function then vlookup the value from the “new array”.

Thank you for your explanation, it’s been clear in a simple way 😀

Liked by 1 person

• MF says:

You are welcome! Glad it helps 😀

Like

8. Pingback: Hlookup with two conditions?

hi. tks for your info,, 😀

Like

• MF says:

You are welcome! 😄

Like

10. Saurabh says:

Is it possible to get sales only by using vlookup function Eg: G3 =vlookup(G2,D2:D10,3,FALSE) =vlookup(G1,C2:C10,2,FALSE)

Like

• MF says:

Hi Saurabh,
Your formula is actually comparing the results returned by two VLOOKUPs. You will likely get a result of “FALSE”.
If you need to lookup two values, the above examples should do the job.
On the other hand, remind you that if you are looking up single value only, you should check if there are duplicates in your Table VLOOKUP will return the first matched value found. You may refer to my post regarding vlookup for more information.

Like

11. Amy says:

Hi I’m trying to do this but using different worksheets and a dropdown option. I tried doing the 3rd option but still having trouble. Would you care to suggest what can be done please?

Here’s the link to my inquiry on reddit:

Thanks so much!

Like

• MF says:

Hi Amy,
IF you end up with #NAME, that means your Named Range or Functions used in your formula do not exist, most likely being mis-spelt. Please check.
Cheers,

Like

12. ashish says:

If you are interested in learning How to Use Vlookup Function without using Helper Column

http://www.exceltip.com/tips/how-to-use-vlookup-function-without-using-helper-column-in-microsoft-excel.html

Like

13. hayat khan says:

Plz upload an example file for all above formulae

Like

• MF says:

Hi Hayat,
Thanks for your suggestion. The truth is… I don’t know how to upload sample file here. 😛
On the other hand, I suggest you input some simple data and follow the steps shown above. In this way, although it may take longer time and more effort, you will learn better as you go through the whole process on your own.
Cheers,

Like

14. Pingback: Note lookup with 2 conditions

15. Pingback: Two criteria for a lookup table