How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

Multiple VLOOKUP LeadVLOOKUP is a great tool for pulling data from tables, but it has a handicap: it can only work with one criteria for matching information. If there are multiple rows in your sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck. Fortunately, Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria. Here’s a quick tutorial to help you learn how…

Example Data

Let’s say, for example, that we want to be able to search through a list of fuel economy data for cars to find the mileage…

Car Data

Normally, we would want to be able to enter the model of a car and get it’s fuel economy as a result. Unfortunately, Many cars, like the Acura ILX, have multiple engine configurations with different mileage ratings. Fortunately, in this case, the car’s displacement can serve to separate them.

Displacement Highlight

This means, however, that we will need to look up the car by both its Model and its Displacement at the same time to find the appropriate Combined Fuel Economy in column H.

Trying to Use VLOOKUP

In a normal VLOOKUP, the syntax is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The lookup_value is the data you are searching with.

The table_array defines the table that you want to look through. The first column must be the column that has the lookup_value in it.

The col_index_num is the number of the column in the table_array that has the data you want to find.

The optional range_lookup specifies whether the list is sorted or not. (TRUE means that VLOOKUP stops looking when it finds something that comes later in the alphabet than the lookup_value. FALSE means it searches the entire list.)

If we were looking for just the Model of car using VLOOKUP in our example data, it would look like this:

VLOOKUP Example

=VLOOKUP(A2,C4:H1159,6,FALSE)

A2 holds the Model of car we want to find.

C4:H1159 is the table we want to search through. Column C is the column with the Model information.

Column 6 is the column that holds the Combined Fuel Economy figure that we want to find.

The result of the VLOOKUP is this:

VLOOKUP Result

It finds the first entry that matches – the 1.5 liter engine with 38 MPG. This is a problem if, for example, you want to find the fuel economy of the 2.4 liter sport version. To do that, we need to use INDEX and MATCH.

Using INDEX and MATCH to Replace VLOOKUP

What we really need is to be able to look up the Model and the Displacement at the same time. MATCH is a function that gives you the location of an item in an array. The syntax for MATCH is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

The lookup_value is what you are searching for.

The lookup_array is the array of values you are trying to find the lookup_value in.

The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically.

The INDEX function takes a location and returns the value that is in the cell. The syntax for INDEX is as follows:

=INDEX(array, row_num, [col_num])

The array is the table of data that contains the cell value you want.

The row_num is the relative row number of the cell you want.

The col_num is the relative column number of the cell you want.

By combining INDEX and MATCH we can produce the same result as VLOOKUP. Using the same search we did for VLOOKUP, the INDEX/MATCH pair looks like this:

INDEX MATCH Example

=INDEX(C4:H1159,MATCH(A2,C4:C1159,0),6)

C4:H1159 is the array that INDEX uses to find the value.

A2 is the cell that the value we want MATCH to find.

C4:C1159 is the lookup_array that MATCH looks through to find the value in A2.

The 0 means that MATCH will look for the exact value instead of an approximate one.

Column 6 is the column in the C4:H1159 array that holds the Combined Fuel Economy values.

The result is identical to the VLOOKUP result. MATCH finds the first Combined Fuel Economy value for the Acura ILX, which means it will give 38 MPG for the 1.5 instead of one of the other engine options. To find a specific Model and engine Displacement combination, we need to modify our INDEX/MATCH formula into an array formula.

Using INDEX and MATCH with Two Criteria

To allow MATCH to search for multiple criteria, we are going to change the way it looks for its result by making it an array formula.An array formula takes an array of values instead of a single one and checks each cell in the array until it finds a result.

Our old MATCH formula looked like this:

=MATCH(A2,C4:C1159,0)

It looked for the value of A2 in the table C4:C1159, and when it found it, it returned the location.

Now we are going to ask it to be creative:

=MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0)

We have asked MATCH to look for a value of 1. Instead of giving it an existing array to look through, we are asking it to build one from scratch. The new array checks all the values in C4:C1159 for one that matches A2 and all the values in D4:D1159 for one that matches B2. Where they both match, the array will have a 1 (a TRUE boolean result). Where they don’t both match, the array will have a 0 (a FALSE boolean result). Therefore, MATCH will return the location where the array matches 1 (when both of our criteria are true).

If this process doesn’t make sense to you, that’s okay. Just plug the new MATCH function into your INDEX/MATCH formula:

=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)

When you enter the formula, don’t just press ENTER. Press CTRL+SHIFT+ENTER to tell Excel that it is an array formula. You can tell you’ve done it right because the entered formula will be surrounded in curly braces {}.

Curly Braces

With that, your formula will be able to find the Combined Fuel Economy based on both the Model and the Displacement. You can use this technique for any number of criteria with INDEX and MATCH. Just add additional terms to the multiplication equation.

Multiple Criteria VLOOKUP with INDEX and MATCH Example Download

You can follow along with this tutorial using the original source data and explore an example of the solution in the embedded file below. To download your own copy, click on the green Excel icon in the lower right corner.

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Jump start your analysis with a one-on-one consultation or join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

45 Responses to How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

  1. Jason says:

    Thanks for the great post on the use of the combined INDEX & MATCH functions. It will certainly be useful for me in the future. Unfortunately, it falls just short of solving a problem I’m working on now. Can you suggest how this approach might be modified (or another approach entirely) for use with two criteria, one of which is not exact? Specifically, I want to search an array where one value is an exact match, and the second value falls within a range (specified by two columns – an upper and a lower bound). When both of these criteria are met, I want to output the value in a column adjacent to the values that were searched. For example, in the attached image, the cells highlighted yellow are what I’d like to generate a formula to produce (here I determined them by hand). I need to search column A for a match for column F, and then find cases where column G falls between the values in columns B & C. Then I want to output the value contained in column D. Please let me know if you’d like any clarification, and thanks for any help you can provide.

  2. Chuck Trese says:

    Only need to recognize that you can <, as well as =.
    For example, in cell H2 enter this formula
    =INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7<$G2),0),4)

    remember to enter using Ctrl-Shift-Enter.
    Then you can copy formula into cells H3 through H13

    EDITOR’S NOTE: Chuck, I corrected the formula you provided to the way you intended before approving it…

  3. Chuck Trese says:

    sorry, text editor won’t allow greater-than and less-than symbols.
    You just need to recognize that you can use ‘greater-than’ and ‘less-than’, not just ‘equals’ in your comparison checks. (Unfortunately, the equation in the previous post is a mess because it does not show the greater-than or less-than symbols, but instead removed the middle of the formula – the part that mattered the most.)

  4. Hi Jason,

    Chuck’s suggestion to use a greater-than or less-than to set upper and lower bounds is right on the money. His formula checks to make sure Column B is less than the Column G item. To be more precise, you could check the upper bound too, like so:

    =INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7< $G2)*($C$1:$C$7>$G2,0),4)

    Don’t forget to enter as an array formula with CTRL+SHIFT+ENTER!

  5. Randy says:

    Thanks for the fantastic post! I have one question. Sometimes when I am using a two criteria formula with index and match just as you described. I want to return a “0″ or blank if the result is #N/A.

    How do I change the formula to return 0 or something else when both criteria are not met?

  6. Hi Randy,

    Glad you like the tutorial! If you want to catch errors like #N/A, you need to wrap the INDEX(MATCH()) in an IFERROR() function. This lets you specify the behavior when you have an error. For more details, check out this guide on Excel error handling.

  7. Thijs says:

    Great post, and almost fixes my problem, I’m just wondering how you can use this if you have multiple similar instances of the searched criteria (say two people with the same first and last name) and therefore multiple outcomes exist. Is there a way to get excel to look up the 1st, 2nd, 3rd etc. of each combined instance so I can put them in a row. I’ve attached an example of what I’m trying to do. And thanks for any help you can provide

    • Thijs says:

      Hey Andrew,

      Thanks for the reply, I actually figured it out on my own, I just used Vlookup but simply combined the two search criterea and the two rows where to find it using an & function That way I could simply ask it to find outcome 1 to 15 and get my results.

  8. Devin says:

    Thanks, helped alot. Although I have a table of around 7000 and this method takes alot of time. What would be a faster way to do this. I think it is slow because it has to create an array for each cell. It does about 5.8 every sec. = 20 mins of waiting. Just in case it is relevant, no other calculations are on the sheet, and I am using 3 critiera.

    • Hi Devin,

      Because the INDEX/MATCH has to compare each criteria against the entire table, lots of rows and lots of criteria makes the number of necessary calculations grow exponentially. Try to think of ways to pre-calculate some of the criteria to reduce the total number in the INDEX/MATCH or search a sub-set of the data where possible. Also, if you are working with the table often, it may be worth it to shut off automatic re-calculation and only re-run the 20 minute session when you are finished making changes.

      Good luck!
      Andrew

  9. chris says:

    I’m looking for a formula that look a 3 criteria, item, size and month..I wrote the formula many different ways but can’t seem to get it right can you pls advise. thanks ahead for your help

    • Hi Chris,

      The basic format for a 3-criteria INDEX/MATCH would be as follows:
      =INDEX(TableRange,MATCH(1,(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2)*(CriteriaRange3=Criteria3),0),ReturnColumnNumber)

      If you have more specific difficulties, I’d suggest you submit a question at one of the forums available on my Web Resources page.

      Good luck!
      Andrew

  10. Rachel says:

    Hi Andrew,

    I have the same issue as Chris does (matching 3 criteria), plus I need to match a few columns of data, and I need to pull it from one tab into another tab. Is that possible?

    Rachel

  11. Rachel says:

    Thanks Andrew! If this works, it will save myself and my team hours of pressured manual excel labor.

  12. Ken says:

    Thanks for taking the time to write this fantastic explanation for using INDEX and MATCH for multiple criteria. I’ve actually been using the MATCH part for a year or so, but I had no idea what the 1 aspect of it was about. Yours is the first article that explained the logic behind it. I never comment on blog posts, but I had to for this – it was excellent!

    Much appreciated!

  13. Jonas says:

    Thanks for this great tutorial. I finally understand how to use the Index + Match function and was able to use it in a complex spreadsheet for work. In the end, I had to transpose the data from a vertically structured table to a horizontally structured one. Is there a way to use Index + Match for data that is structured from top to bottom? Either way, a great tutorial and well written blog. Keep up the good work!

  14. James says:

    Thanks! Great Stuff!

    How many criteria can I fit in using this method?

    Best regards!

  15. Hi Jonas –
    You can use INDEX/MATCH vertically or horizontally… You just need to use the MATCH statement in either the row number or column number field. Syntax for INDEX is as follows: =INDEX(array, row_num, [col_num])

  16. Hi James –
    As many criteria as you want… I warn, however, that each additional criteria adds computation time. If you do more than two, things start slowing down exponentially. It may be easier to concatenate multiple criteria into one to save on computation.

  17. Brandon says:

    Thank you so much for the step by step instructions. I can’t tell you how much time this saved me!

  18. Takis says:

    Thank you Andrew! Very nice guide and extremely useful!
    “If this process doesn’t make sense to you, that’s okay. Just plug the new MATCH function into your INDEX/MATCH formula:”,
    hahahahaha! really enjoyed when I read it!

  19. Dustin says:

    This almost does exactly what i’m looking to do. I just want to know is it possible to generate more results. so If i wanted to generate a list from my table of everyone with a certain qualification.

  20. Katie says:

    Thank you – this is a big help. I was able to do the formula with 3 variables. My only issue is I’d like to copy this formula down an entire column of 100K+ line items. I am unable to just click the bottom right corner because I get a #REF! error. The formula copies with “#REF!” where the match range is supposed to be. The only way I can get it to work is hit the CTRL+SHIFT+ENTER in each cell, but I cannot do this 100K+ times. Any simple way to copy this formula down an entire column?

    • Hi Katie,

      Congratulations on getting the formula up and running! Array formulas (the ones you enter with CTRL+SHIFT+ENTER) are a bit picky about how you copy them. You can select the cell with the entered formula and drag down with the bottom right corner to copy the formula down. You can also copy and paste the formula into other cells, but you must exclude the original cell from the paste selection or Excel will give an error.

      As an aside, I will caution you that a 3-criteria INDEX/MATCH applied across 100k cells will likely take a very long time to compute (on the order of hours). Be sure to save your work before copying the formula down and plan to do so when you are able to leave Excel alone to process for several hours.

      Good luck!
      Andrew

  21. alberto Jaume says:

    Hi Andrew,

    This is a very nice article/tutorial! I have found it can be used in differnet circumstances, however I was wondering whether you can provide further explanation in regards to a particualr situation. I am trying to produce a report that checks for a name in table (that is repeated multiple times) and selects the the most recent entry (each entry has a different date in a different column). I ma breaking my head trying to use your approach but not sure how it could make it work. Could you cast some light on this?

    Many thanks!

    • Hi Alberto,

      Thanks for the feedback! This method should always return the first match it finds, so if you pre-sort your list so that newer entries appear at the top, the lookup should work the way you want.

      Good luck!
      A

  22. Unal says:

    Hi,
    I think there is an easy way to do it (but you need a new column in your data sheet)

    Please check below.

    i merged the cells that I want to use in criteria and put them in column C together (=A6&” “&B6)
    Then used this formula to match it. =VLOOKUP(G6&” “&H6,$C$6:$D$11,2,FALSE)

    That’s it. You can even create as much criteria as you want.

    Regards

  23. Alberto says:

    This How to saved me about 5 days woth of work… its is awesome. Thanks a bunch.

  24. Venkatesh says:

    Really very helpful, thanks lot.

  25. chris says:

    When I download your example in Excel 2007, it doesn’t return an answer in cell C2 but rather #VALUE!

    Any ideas why? I didn’t change anything.

    • The formula in C2 of the example worksheet is an array formula. These were available as early as Excel 2003. If there is a #VALUE! error in cell C2, the formula has probably been re-entered by pressing only ENTER instead of CTRL+SHIFT+ENTER. If you can’t see the {} curly brackets around the =INDEX() command in the formula box, you need to click in the formula box and re-enter the array formula using CTRL+SHIFT+ENTER.

  26. chris says:

    Great that works!

  27. Zane says:

    Reply to: Unal comment below. Thank you very much. This formula has saved my life. This is genius thanks again!!!!

    Hi,
    I think there is an easy way to do it (but you need a new column in your data sheet)

    Please check below.

    i merged the cells that I want to use in criteria and put them in column C together (=A6&” “&B6)
    Then used this formula to match it. =VLOOKUP(G6&” “&H6,$C$6:$D$11,2,FALSE)

    That’s it. You can even create as much criteria as you want.

    Regards

  28. Dwight says:

    This is very cool. Thanks for sharing. I had good luck with ctrl R and ctrl D to copy down and right. I didn’t know about IFERROR. I routinely use =IF(ISERROR(formula),””,formula) to get blanks instead of #N/A or #VALUE. Cheers.

  29. Vince says:

    Thanks very much for sharing your knowledge. This worked perfectly for me!

  30. Sbu Nkosi says:

    I have a different (and maybe easier) challenge. I’m trying to do the sum of all the values that meet a criteria i specify. For example, I have a 2-column table/array that consists of months and their values. I have several rows containing Jan 2015 and want to add up all values for Jan 2015. How do i do this?

  31. Sean says:

    Hello,

    I am having a problem getting the formula to work I keep getting a result of N/A even though I know there is a match.

    Here is my formula

    =INDEX(Sheet1!$B$6:AD$32,MATCH(1,(Sheet1!$B$6:$AD$32=Sheet2!$A5)*(Sheet1!$C$6:$AD$32=Sheet2!H$2),0),Sheet3!$D$3)

  32. Wilder says:

    Hi Andrew,
    Thanks for this post. Please, letting me to know if it’s possible to avoid the array option. It works for one cell (one record) at the time. I wanna use a formula for many records and drag the formula to complete the search. Much obliged,

    • You should still be able to drag down the formula after you enter it as an array. Another option is to concatenate all your criteria into a single string to avoid the “multiple criteria” INDEX/MATCH all together…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Heads up! You are attempting to upload an invalid image. If saved, this image will not display with your comment.