Perform VLOOKUP with 2 lookup values

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).

Excel Tips - lookup 2 values 1
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

Let’s start with the basic.

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:
Excel Tips - lookup 2 values 2
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


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:
Excel Tips - lookup 2 values 3

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:


To visualize it in a Table format, it looks like the below:
Excel Tips - lookup 2 values 5

Now the whole formula makes sense:


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


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:
Excel Tips - lookup 2 values 6
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.

Excel Tips - lookup 2 values 3

  • =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!


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.
Excel Tips - lookup 2 values 4
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.

Other topics about vlookup:

  1. The basic of vlookup
  2. Tips in constructing vlookup
  3. vlookup with Match 
  4. vlookup options – True or False?
  5. Advanced vlookup – Text vs. Number
  6. Advanced vlookup – Wildcard Characters “?” and “*”
  7. Alternative to vlookup – Index and Match
  8. Three different ways to do case-sensitive lookup

Excel Expert Course


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

35 Responses to Perform VLOOKUP with 2 lookup values

  1. Jason says:

    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?

    Could you please help?


  2. ram says:

    i want to combine the formulas below into one

    B4-> IF(AND(B2=”S”,B3=”M”,”YES”,”NO”)


  3. Anshul Sood says:

    Thanks for the wonderful explanation. it works great!!


  4. Aeh says:

    Just wanted to say thank you!


  5. Thank you very much for your tips, sure i will follow your tips.
    For more information:


  6. Pingback: Multiple Lookup based on FirstName LastName to return Phone number

  7. ExcelVbaLab says:


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



    • MF says:

      Glad you like it.
      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.


      • 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?


        • 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.


          • 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.


    • 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)


  8. 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:
      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:

      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.

      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

  9. Pingback: Hlookup with two conditions?

  10. nadia says:

    hi. tks for your info,, 😀


  11. 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)


    • 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.


  12. 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!


    • 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.


  13. ashish says:

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


  14. hayat khan says:

    Plz upload an example file for all above formulae


    • 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.


  15. Pingback: Note lookup with 2 conditions

  16. Pingback: Two criteria for a lookup table

  17. Pingback: 3 Way Lookup By splitting the Cell value

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s