# How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

**VLOOKUP** 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…

Quick Navigation

## 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…

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.

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(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:

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(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 {}.

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.

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.

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…

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

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!

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?

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.

I’m trying to add the IFERROR to an index/match function and can’t seem to get it right. Can you be specific about how to add this?

Hi Kelly,

You might find my guide on error handling useful… Specifically, the section on IFERROR and Common Error Handling Techniques. You can find it here.

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 I’m looking to something similar, can you show me the array formulas you used?

Hi Thijs,

Depending on what you’re trying to do, you might find How to Combine Data from Multiple Rows into One Cell or Extract a List of Values Filtered by Criteria with Sub-Arrays useful.

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.

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

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

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

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

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!

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!

Thanks! Great Stuff!

How many criteria can I fit in using this method?

Best regards!

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

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.

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

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!

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.

Hi Dustin,

I think you might find this tutorial useful:

http://exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/

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

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

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

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

Really very helpful, thanks lot.

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.

Great that works!

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

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.

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

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?

Sounds like a job for SUMIF() or SUMIFS() to me…

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)

Check to make sure the formula is entered with CTRL+ALT+ENTER and not just ENTER. You’ll see curly braces {} around the formula if it is done correctly.

Hi Andrew, I’m having a similar issue. When I drag the formula into the rest of the column, it changes the array I’m searching through. I want it to search through. In my first cell the formula is {=INDEX(Sheet2!A1:C61,MATCH(1,(Sheet2!A1:A61=DATA!C2)*(Sheet2!B1:B61=DATA!D2),0),3)} but when I drag into the next cell it changes the formula to {=INDEX(Sheet2!A2:C62,MATCH(1,(Sheet2!A2:A62=DATA!C3)*(Sheet2!B2:B62=DATA!D3),0),3)}

How can I keep that fixed?

All you need to do is lock the row references. Sheet2!A1:A61 becomes Sheet2!$A$1:$A$61.

Thanks Andrew, the formula is working fine, there is a small issue, when Match does not find the value its giving me #N/A, will you please tell me how I can use IFERROR,, ISNA or some other function to avoid this issue.

{=INDEX(Order!$A$2:$C$1000,MATCH(1,(Order!$A$2:$A$1000=K$1)*(Order!$B$2:$B$1000=Production!$A3),0),3)}

Hi Arif,

The

IFERROR() formula is pretty straightforward. Just use the formIFERROR(original_formula,formula_if_error). You’ll still need to enter withCTRL+SHIFT+ENTER. For more error handling options, you might like the Definitive Guide to Excel Error Types and Error Handling.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…

Thanks Andrew. Can you please show me an example fot the single string option? Thanks a lot!

Hi Wilder,

Long time coming, but here is a tutorial on how to get Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE that should help!

How’d you get away with the implied if/then? In my own implementation I have to add IF() functions to get the Boolean values.

Oddly, your example works when I download it. The only operational difference between yours and mine is that I’m referencing values in a separate workbook.

Hi,

Thanks for sharing this very useful formula. I am using this to reference data from two separate Tables. I attempted to use the Table name and Table Columns as reference in the Match formula. It looked like this:

{=Match(1,(Inventory[Page ID]=[@Page])*(Inventory[Language]=[@Language]),0)}

However, this did not work. I had to use absolute cell references – e.g., A2:A100. Any idea why this would be? Perhaps I am just referencing incorrectly?

Cheers,

Adam

Hi Adam,

Data Tables in Excel work on internal logic that lets you easily duplicate formulas all the way down the rows. Your reference @Page is referring to a relative page in the row of the table. It is likely not interpreting this correctly for the MATCH, which generally works on absolute row references. There may be a way to use table references in INDEX/MATCH calls, but I have not explored the topic myself. Sounds like you got it to work, though!

Andrew

hi Andrew,

thanks for this great tutorial.

However, it seems that the INDEX/ MATCH combination plus Ctrl+Shift+Enter is too computationally intensive. Excel says it ‘ran out resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.’ This seems to happen when there are already a number of formulas in the spreadsheet. Unfortunately this makes the combi un-usable.

Thanks for this great tutorial – it is exactly what I needed, and is the most well-written and easy to follow post of its kind that I’ve seen. Keep up the good work!

Very useful details for cell comparision

Thanks a lot for posting this.

It really helped me solve a problem that I was struggling with.

This is a great article but I have a slightly different issue. I have two sheets of data that have three of the same columns that I want to compare the row fields. If there is not a match from one sheet to another then I need to generate a report of those differences or at least hightlight the non-matches. Any ideas on how to do that? Thanks!

You may find my article on Sub-Arrays useful: Extract a List of Values Filtered by Criteria with Sub-Arrays

This is the best solution ever! Thank you. Thank you.

Thanks for the great tutorial Andrew. Whenever im faced with such problems i usually just VLOOKUP and simply combine the criteria in a cell and use that as the start of my table array. This method however mean you have to add another column to the mix. =VLOOKUP($B$2&$C$2,$A$5:$I$1159,9,0)

Thanks for this great tutorial. Works almost perfectly for my application. The only issue I have is that one of the criteria I have is case sensitive. Is there any way to make the match formula case sensitive?

Hey Andrew,

Thanks for a great tutorial. You solved my problem with Index and Match. The formula combined with =IFERROR works like a charm. I had 3 set of criteria to be looked before giving the value. Thanks for making this work….

Rgds,

JK

hi,

Just wanted to know how to get nth matched value from the below formula.(Match function returns multiple 1’s and want nth 1)

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

Thanks in advance.

I am still getting #N/A formula. I have checked several times to make sure that it is an array. I am using Excel 2007. Do I need to include the iferror?

hi,

Just wanted to know how to get data matched with code no and month so that the result will be 25000 (1101 & Apr -14)

Hi there;

I’m having a similar issue as Terry; it’s funny, because if I click on the Fx symbol, it shows the result I want but the cell still says #N/A. I’ve clicked on the symbol next to it to analyze it and it says that first part of the MATCH function (in the formula given, it’s the C4:C1159 range) is the problem but I cannot determine why.

Any thoughts, or ideas? I would love to be able to do this without resorting to concatenating the two fields together to create the look-up value!

Thanks in advance!

Hi,

Once, you enter the formula in the cell, dont press enter. instead Press CTRL+SHIFT+ENTER to tell Excel that it is an array formula. It will only work then. Else it will keep giving #N/A.

Hope this was useful.

this is great info, thanks sooo much!! this is exactly what I needed.

I did a test on this and it worked perfectly, but all my data was on 1 sheet.

then I tried to apply it to my real workbook, which has data on 2 sheets, and received the error ‘the formula you typed contains an error’. here is my real formula:

=index(‘Line Detail’!A1:K546,match(1,’Line Detail’!K1:K546=2016)*(‘Line Detail’!B1:B546=’Summary Original’!A3),0),11)

the error seems to come from the ’11’ at the end of the formula. I guess it doesn’t know that the 11 refers to the 11th column in my specified table on my ‘Line Detail’ page. i tried putting ‘Line Detail’ before the 11 and that didn’t work.

any helpful hints? thanks in advance!

Disregard the post I just posted! I left out a “(“. doh! (it works on separate sheets).