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

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

## Get the latest Excel tips and tricks by joining the newsletter!

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

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!

## 175 thoughts on “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!

1. sai krishna says:

Hi Robert,

I am having a sheet with names in one column, and in another sheet with names and numbers. i have used vlookup to get the number from sheet 2 to appropriate value in sheet 1. The difficulty i am facing is in sheet 2 same names are there for different numbers, so vlookup is giving the first match value and leaving the rest. Help me to solve this issue

1. Jim says:

I had the same issue. The way I resolved the issue is to add ” 2″ behind the last name of the second entry for the person(s) with two numbers, and in the results table, you have to have two entries for those people, by adding ” 2″ behind the last name.

If anyone knows a better solution …

1. can you send some photo of your project so i can understand more.

2. Jean says:

I have been going to this webpage for your formula multiple times per month for the past year. You have made my life so much easier. Thank you.

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?

1. marcnz says:

Hi,
the formula will look originally to something like that:
={INDEX(Range,MATCH(1,(criteria1)*(criteria2)),0),Col num in Index range)}
Just put your IFERROR like that:
={IFERROR(INDEX(…,MATCH(…),Col Index range),Value if error)}

Value if error can be either a number or a text (that you would need to put in quotes).

I hope this will help you if you haven’t found the way to do it yet.
I just learned the Index(Match()) with multiple criteria following this great tutorial. concise, clear and well written.

Cheers.

1. anthony says:

fantastic, thanks

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.

1. Kelly says:

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?

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

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

1. TravisC says:

Thijs I’m looking to something similar, can you show me the array formulas you used?

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

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

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

1. Matthew says:

Thank you so very much for posting this. I was able to get both the two and three criteria index/match combination to work pretty consistently (a few that didn’t work, however – posting my example in the forum, thank you for the link).

Again, thank you from all of us. The Index/Match combination is impressively powerful and the fact that you don’t need your table in any certain order is phenomenal.

2. JB says:

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!

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?

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

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

1. David Elms says:

Hi, I wondered if there was a way to do this within the formula using the MAX command somehow?

I am essentially trying to achieve the same thing, but don’t want to have someone to go in and re-sort the data in the sheet all the time as the INDEX/MATCH lookup is being used in a different sheet to the source data. Is it possible?

22. Unal says:

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

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

1. shree says:

=INDEX(G12:G16,MATCH(B7,C12:C16,0))

23. Alberto says:

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

24. Venkatesh says:

25. chris says:

Any ideas why? I didn’t change anything.

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

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

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

1. Juliana says:

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?

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

2. Arif says:

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

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,

1. 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…

1. Wilder says:

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

33. nsdkljvbdivb says:

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.

34. 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,

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

35. Josh says:

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.

36. Phil Mullins says:

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!

37. RAJ says:

Very useful details for cell comparision

38. R Kaur says:

Thanks a lot for posting this.
It really helped me solve a problem that I was struggling with.

39. Lori H says:

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!

40. Melanie says:

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

41. NigelDV says:

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)

42. 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?

43. Jay says:

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

44. amit says:

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)

45. Terry says:

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?

46. Sam says:

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)

1. sachin says:

Use vlookup & match function

47. Laura says:

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!

1. Jay says:

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.

1. Laura says:

Jay… thank you so much! We’ve been concatenating the value in the two fields and then doing VLOOKUP… which isn’t exactly ideal. Now that I could get this to work, I will share with my coworkers!

48. Carol Ann says:

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.

49. Carol Ann says:

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

1. Laura says:

Happens to the best of us!

50. Tommy Riotto says:

I generally don’t reply to discussions, but this tip and the instructions were incredibly well explained and laid out. This has helped me increase report efficiency in other workbooks and KPIs. Thank you!

51. Jeff C says:

I’m looking at the performance impact of a two criteria index match versus using a sumif formula. I’ve been tasked with improving the update speed of a spreadsheet that has about 18,000 rows and 15 columns. (That’s about 360,000 cells that have this formula). The table is more a matrix so I have to have a formula for each row and column to find the value where they meet. Right now we are using sumif’s and it takes about 30 minutes to refresh. Do you think a two criteria index match would have any performance boost in this instance?

1. If speed is what you are looking for, INDEX/MATCH with multiple criteria might not be the best approach… I’ll refer you to the article: Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE

You may need to make an intermediate matrix of all the criteria in a hidden tab to get the full efficiency of the concatenation approach, but it will definitely be faster than standard INDEX/MATCH with two criteria, as it only has to do one compare.

52. larry leightley says:

Need to do a double approximation for a 3 column table and efforts to use > < or -1,0,1 as match types do not seem to work?

gauge width price
0.018 48 \$3.00 {=INDEX(B4:D54,MATCH(1,(B4:B54=A2)*(C4:C54=B2),0),3)}
0.019 49 #N/A
PT Gauge Width \$/cwt
CR 0.015 52.5 \$4.00
CR 0.015 61.5 \$4.00
CR 0.018 36 \$6.00
CR 0.018 42 \$4.50
CR 0.018 48 \$3.00
CR 0.018 52.5 \$3.00
CR 0.018 61.5 \$3.00
CR 0.022 36 \$4.00

53. Catherine Liu says:

How to find the value of row 3 which criteria base on the first and second row?

thank you so much,it’s very useful for the multiple criteria index+match…….

55. TanyaG says:

Wow!!! Awesome, thank you. This has saved me a bunch of time!!

56. Noah says:

Hello —
I would appreciate some help with this formula

I have this table that is used for the look up:
Fiscal Month Start Date End Date
Jun 5/26/2014 6/29/2014
July 6/30/2014 7/27/2014
Aug 7/28/2014 8/24/2014
Sep 8/25/2014 9/28/2014
Oct 9/29/2014 10/26/2014
Nov 10/27/2014 11/23/2014
Dec 11/24/2014 12/28/2014
Jan 12/29/2014 1/25/2015
Feb 1/26/2015 2/22/2015
Mar 2/23/2015 3/29/2015
Apr 3/30/2015 4/26/2015
May 4/27/2015 5/24/2015

For the dates below, I want it to populate the month above if the date is in between the start and end dates above
My results show May for all rows rather than the correct month
Here is the formula that I used:

=INDEX( Check!\$A\$9:\$A\$20, MATCH(1, (A10>=Check!\$B\$9:\$B\$20) * (A10<=Check!\$C\$9:\$C\$20)),0)

Date Result Expected result
6/11/2014 May Jun
7/15/2014 May July
8/18/2014 May Aug

57. Wells says:

very very good article. well explained. thump up

58. Theron says:

I have a master spreadsheet with columns A through U and data on rows 2 through 900. I would like to pull all column/row data based on one criteria ( which would be found in column O and one of 5 options) and display results with the same column headings as the master but in another tab of the workbook. Can you please tell me if this is possible?

Great post. Thank you Andrew.

I thought I’d share what I was working on. I needed to create a table to look up commission rates where the rate could vary by day and by partner, but not for a uniform list. For example, for one partner, the rate may change on Oct, 1, where another one never changes and another one went up for a two month period and then changed to another rate.

In my source data I added in an effective date for each change in rates which was the LAST effective date of the rates. The base effective rate was 10 years in the future.
The in the index formula I looked up for values where the date was >= the date I was searching for.

Worked very well, just the array is heavy on calculations.

Avraham

60. Falcon says:

Thanks for the example. I have a question on just the MATCH formula. As per my understanding, MATCH formula only takes one look-up value, can I specify multiple look-up values where if one of the values is found, I would like it to return the result. Example: I have a row made up of a lot of empty cells, some cells marked as a, some as c, same as d, some as 4, some as 7. I want the formula to return the first relative position in a range where either of 4 or 7 are found while looking up.

61. Danijela says:

many thanks!

62. Annie says:

This just changed my life. Thanks so much 🙂

63. Thank you! This saved me a LOT of time and effort messing around with VBA and writing a macro. It took me a bit to figure out that you had to match “1” (I thought it was looking for the number 1 in a column to begin with), and then I discovered that you cannot use “TRUE” in its place if you want to use this technique. Those two pieces of information are critical to making this work. I’ve now successfully matched my data on three criteria, and it works beautifully.

64. Arun says:

Hi
In a column like c1,c2.c3….. i have paragraphs and in d1,d2.d3… i need my output , from that paragraph i need to filter only the words that matches names like joseph and anna and lawrence, is there any formula for it…..

65. Holly says:

I’m using this formula and entering Ctrl + Shift + Enter and it’s returning the 1st value in the column chosen, not the value that matches both criteria. …can you please help???

=INDEX(WorkingMTD,MATCH(1,(‘NYC Working MTD’!A26:A432=A3)*(‘NYC Working MTD’!D26:D432=\$B\$1),0),5)

66. Sachin says:

Hi,

Can anyone help me in getting the SR No if i enter the date and country. For example if i enter Origin country as Croatia and date as 01 JUL14, i should get the corresponding SR NO as 18.

67. Gary C says:

Hi Andrew I loved the post thanks very much it solved one of the problems I had been banging my head on. Thankyou Thankyou!
I feel that it’s close to solving a second one as well but I can’t seem to make that final logic step. I have given an example of my data set the problem I have a hard time wrapping my head around is that my range for “blue” for example could be anywhere from 1-30 lines.
Here is the issue. I need to return the largest value for each grouping
DATA SET Expected Return
blue 4 Blue 96
blue 16
blue 42
blue 96
Green 12
Green 18 Green 18
Orange 3
Orange 47 Orange 47

68. MANSOOR says:

Wowwww Such a wonderful function. Thanks for explaining and bringing it to us. 🙂

69. SMLCFC says:

Hi, thanks for the fantastic explanation. A couple of questions:

– My formula is returning the value BELOW the one that I am expecting. What am I doing wrong? The criteria in my formula are definitely correct.

– I am returning a value based on 2 criteria. Is this the most efficient way? My spreadsheet is going to contain a lot of formulas so I’m worried about performance. Would it be better to use a sum if array formula? Vlookup?

Many thanks

70. Behalior says:

My god man … it is soooo helpful!

71. Matthew says:

The match function is able to sort through rows of data and indicate back to the index function which row to pull from, is there a way to use the match formula to return column numbers as well? Or is there a separate formula that can return column numbers based off of multiple criteria?

72. Louise says:

Please can anyone advise me with regards to this formula i’m finding to figure out.

Basically, i’m creating a timesheet tracker report. Each week you will be able to identify who’s timesheet has been submitted, unsubmitted and missing. I’ve managed to do a formula based on each of these categories however i’m unable to bring the dates. The report will be run on a weekly basis therefore the dates will change in each column. Can anyone advise?

73. Patricia says:

I need to create a formula in workbook 1 that looks in workbook 2 for a store number, the date, and gives me a total cost. Example. I need in the yellow to look for store 4469 on 2/10/15 and tell me the total cost spent on that date. If correct the cell will total \$700

1. try this..
my own solution hope you like it..

i put extra cells to merge the date and store no. to call it as one..

74. Abhinav JAIN says:

Thank you very much.
It is very innovative and very useful.
Full marks and kudos to you.

75. Sean says:

Hi Andrew –

I’m not sure if this has been covered, but I am trying to use INDEX MATCH to find data that doesn’t necessarily match 1:1 by name using multiple criteria.

Essentially, I have two sheets and I want to compare columns A B C on both sheets where A=A, B=B, etc. The problem is that the text names do not match exactly with a certain amount of variation. The only column that should match 1:1 is column C. So, I want to use either a wildcard or character match for both A to A and B to B with C = C 1:1 and returning D.

I was able to write a formula that uses a wildcard for A:A, but I can’t seem to find a way to use it for B:B and add C:C (as the 1:1) to compare all 3 elements and return C.

This is my formula: =INDEX(SHEET1!\$D\$2:\$D\$406, MATCH(“*”&LEFT(SHEET2!A2,5)&”*”, IF(SHEET1!\$B\$2:\$B\$406=SHEET2!B2,SHEET1!\$A\$2:\$A\$406),0))

76. Erin says:

I have an excel with items listed in column a… across the top are order numbers and below each order number it has quantities. I want to be able to pull all PO’s and quantities under each for each item.. However, the item may have more than one po. How can I pull each item, with multiple po’s and quantities on one line?

77. Geirur says:

I tried to copy paste your example, and Ctrl/shift+Enter . But as you can see in my picture I get this error in formula. Maybe I got the wrong Excel, using, Excel Pro 2010 ver. 14.0.6106.5005.

78. Rick says:

I need to have a list of the top five items in my list which I have created but I have two fields with the same number. I need to look for the header for that value but, the fields that have the same value the index matched the headers the same. All I want is to go to the next matching value and match the header. Can anyone help?
=INDEX(\$B\$1:\$B\$150,MATCH(L13,\$C\$1:\$C\$150,0))

79. Melissa says:

Please help. Currently this process has taken over a week and I am hoping someone can simplify this. I have tried INDEX/MATCH and cant figure it out.

I have a listing of employees who each have a current phone bill amount.
EID
Amount of Phone Bill

I have a spreadsheet that provides me a listing of the Employees
EID
Job Name
Job Hours
% of Total Hours

I need to merge these sheets together so I have 1 working sheet but my issue is an employee can charge several jobs and I can only get the 1st job to appear. Will excel know to add additional lines based on the number of jobs an employee charges?

Any help would be greatly appreciated.

80. MissPiggy says:

I tried this formula so many times with unsuccessful result.

Billion Thanks

81. CJ says:

Hi, Thanks for your guidance. This method solved my problem.

TQVM

82. Rama says:

whoaaa!!it works!!
many thanks for this great tutorial.finally understand how to use the index n match function.Keep up the good work!!

83. Marcel says:

I’m trying to use INDEX and MATCH to find a value based on multiple criteria. I’ve got it working for regular tables.
Except I want to find the value in a pivot table. Do you know if this is possible? And if so, what my array selection should be?

84. Duane says:

I got a SUMPRODUCT to work in one workbook A, but the same expression is not working in another workbook B and I can’t see why.

In both workbooks, the issue is I need to lookup a piece of information based on one identifier AND “the most recent date” for that identifier.

So, in workbook A, I’m looking up a tax rate based on province and the latest date in a table of rates sorted by province and date and the following formula works perfectly (because tax rates change periodically but past dates should continue to be calculated against the applicable rate at the time of the transaction):

SUMPRODUCT(([ProvinceList]=[province]*([DateList]<=[date]),([CorrespondingRatefromRateList]))

In workbook B, I need a frame number based on unit and the most recent frame installation and the same formula as above, applied to this, does not work:

SUMPRODUCT(([UnitList]=[unit])*([DateList]<=[date]),([CorrespondingComponentIDfromComponentList]))

I'd just prefer to use SUMPRODUCT rather than array formulas where possible, and I also want to understand why the identical construction of a formula works in one situation but not in a similar situation in another workbook.

Any help is much appreciated.

Cheers.

85. Andrew Ragland says:

Great tutorial, useful technique. Problem: I need a logical OR for the two criteria instead of an AND. Thus, if(criteria1 OR criteria2) then value in return column. Given a technician’s name or ID number, return the department they’re in. The Excel OR function returns a boolean, and I’m trying to work through from that to a return value based on the department column, but not having much luck. I get the feeling that I’m overcomplicating the solution. Advice?

86. Jakub says:

Hi
It’s great tutorial. I’d like to ask for some help.
I would like to use multiple criteria but one should be 100% match and second should be just partial match. So for example Last name must match, post code must match but first name must match on first letter from check cell and first letter on check cells. (or first two letters)
I’ve created that:
=INDEX(A2:A43,MATCH(“*”&LEFT(H5,2)&”*”,B2:B45,0))
but I can’t transfer it into multiple criteria:
=INDEX(A2:C43,MATCH(1,(C2:C41=G1)*(B2:B45=”*”&LEFT(H5,2)&”*”),0),1)
something is wrong
Then I’ve tried this one and it seems to be OK but I’m not sure if it works right.
=INDEX(A2:C43,MATCH(1,(C2:C41=G1)*(B2:B45=”*”&LEFT(H1,2)&”*”),0),1)

Thank you for any suggestion

87. Thomas says:

Hi, worked great but I have one problem. In my “lookup array” I have more than one set of the same “lookup values”. Which means the formula is going to find the first set going down the list. Completely ignoring the other sets of the same “lookup values.” Is there a way around this, without having to add any additional criteria to my lookup array???

88. melindamo says:

Hi Andrew,
I have a problem with my formula, but I can’t find where. The result is either #Value! (if source and result are in the same sheet):
=INDEX(\$A\$5:\$G\$10,MATCH(1,(\$A\$5:\$A\$10=G2)*(\$D\$5:\$D\$10=H1),0),6)

or #N/A (if source is in a different sheet than the result).
=INDEX(Models!\$A\$5:\$G\$10,MATCH(1,(Models!\$A\$5:\$A\$10=B7)*(Models!\$D\$5:\$D\$10=W3),0),6)

My range is a pivot table, which didn’t cause a problem with vlookup before.
I am working in an xlsx file.
Ctrl+Shift+Enter didn’t help either.

Attached is a png of my source table.
Melinda

89. Scott says:

This is great info. Can you help me resolve an error? This is my formula:

=INDEX(Sheet1!1:1048576,MATCH(1,(Sheet1!E:E=A2)*(Sheet1!B:B=B2),0),8)

This formula is used in one sheet to go to another sheet (Sheet1), find a row where the data in Sheet 1’s column E matches A2 and the data in Sheet1’s column B matches B2, then fills the cell the data from the 8th column of the matching row from Sheet1. However, I am getting #N/A instead.

90. BT says:

Thanks a lot!

91. Michael says:

Solved my problem perfectly. Thanks a lot!

92. A hearty Thank You! for your explanation that transitioned me mentally from VLOOKUP to the equivalent INDEX,MATCH to the multi-criteria INDEX,MATCH. I’m trying to simplify chart building to convey the cost of annual program releases segregated by their included project subreleases. The previous author was happy with pointing to cells in a “chart creation table” to data in a source table, but turning the source table into a range and selecting within the “chart creation table” based on data in two cells in a row plus a variable column for the year gives me a “universal” function that I can copy into whatever cells need to be populated.

Building complex charts in Excel is a feat in itself, what with the dummy columns to put space betwen series to make them easier to visualize, so your explanation here lets me simplify at least the population of the cells in the chart that the table looks at.

Alan

93. Beth says:

This post was so helpful! #mindblown

94. Tommy says:

I am trying to find an average of items sold on Monday, Tuesday, Wednesday, etc.
So basically A5 in attached sheet would find all Mondays (from columns labeled “Monday Total”) and find all values in Row 5 for only of Monday columns, and return an average in A5.

In B5, there would be an average of all numbers from “Tuesday Total” column, an so on.

I am trying to track sold items. There is daily tracking (from Column J and on..) and then there is an average items sold for all Mondays, Tuesdays, Wednesdays, etc. so it would give me better idea of how many items are being sold on an average on Monday, Tuesday, Wednesday, etc.

I hope my description is not too confusing.
Appreciate the help in advance! Thank you!

95. Justin says:

Hi
I know this must be basic but I am stuck can you help please
I am trying to get my formula to look at different columns but one column has **PC**12345678 in it.
I have my look up data which are numbers then I have a shelf number and then I have the number I have scanned in to the spread sheet.
My problem is I can’t get the vlookup to see passed the ( **pc**) and only look at the number can you help.

Justin

96. Wendy Wong says:

Hi Andrew,
Thank you for your informative article on Index and Match.
This is the second article I am reading on Index and Match but I was able to follow and generate the correct answer this time.

Wendy

97. Steven says:

Andrew, I am awed by how well you describe this complicated scenario in Excel. Thank you for sharing.

98. Rupert Dick says:

Very interesting post – however I found this to be quite a slow method than I expected.

In the past I have used this method

=IFERROR(SUMIFS(‘May15′!\$c2:\$c31001,’May15′!\$A2:\$A31001,\$A7,’May15’!\$F2:\$F31001,T\$2),0)

In this example I sum a value where column A is a changing value and column F is a fixed value (in this case a date)

=IFERROR(SUMIFS(‘May15′!\$c2:\$c31001,’May15′!\$A2:\$A31001,”East”,’May15’!\$F2:\$F31001,”01/05/2015″),0)

This is over 50% faster than

=IFERROR(INDEX(‘May15’!\$A2:\$E31001,MATCH(1,(‘May15’!\$A2:\$a31001=A7)*(‘May15’!\$f2:\$F31001)=X\$2),0),3),0)

but gives the same result.

This does surprise me because I have always found INDEX & MATCH to be a lot faster.

It might be down to how ‘May15’ is ordered so I swapped the two expressions around

=IFERROR(INDEX(‘May15’!\$A2:\$E31001,MATCH(1,(‘May15’!\$f2:\$F31001)=X\$2)*(‘May15’!\$A2:\$a31001=A7),0),3),0)

so that the date was first – this column is in date order

This made the INDEX & MATCH version 40% faster – so a result.

But it does show that the order of the lookup table is important, if this is not predictable then you may want to use a different method

99. Greg says:

Thank you so much. I’ve seen similar formulas used for various tasks I’ve needed but I haven’t been able to find a post where someone explains what values are being returned. That’s the information I needed in order to know how to modify the formula to fit a variety of needs. This is the first time I’ve fully understood the Index and Match functions so I can use them on my own in the future.

100. Monica says:

the array formula was fantastic and very helpful. However, I was hoping to set another filter to this array.

I have a file with 600,000 rows of data. I was able to use your formula to pull back the data I needed based on 3 criteria points. from this point I want to pull back only the top 4 or 5 options. It currently pulls back 30 – 40 rows from the 600K rows. I was trying string in the large function but I can’t figure out the proper formula.

Any suggestions?

101. Karen Griffiths says:

Great tutorial, I put it to what I needed – and it worked! Thanks

102. Binita says:

That was super cool ,thanks !

103. matt says:

I’ve been looking for a simple way to do this. Great explanation, thanks.

104. Sachin says:

Fantastic!!!! Took me a while to understand and implement this (DO NOT FORGET to hit Ctrl+Shift+Enter at the end of your formula PLEASE, AS INSTRUCTED HERE!!!) but eventually worked like a charm. I had two tables in excel that I had to join by Purchase Order Prefix, Purchase order Number, Page Nbr, and Line Nbr to link up the cost per unit with nbr of units and multiply the two together. This INDEX(….MATCH …)) worked perfectly!!! 🙂 THANK YOU!!!

105. David says:

This is orgasmic!

106. Chickypooh says:

Great Tutorial!!

Question – The index match formula works but before any data is entered into my chart it automatically states table 1 seat 1 in the output field? I would like it to be blank like the ticket number and table captain field?

Thank you.

107. Loai says:

Hi

Thanks a lot for the help, really makes life easier,
wondering can we do something similar with HLOOKUP

1. Hi Loai! INDEX/MATCH can be used to imitate HLOOKUP or VLOOKUP equally. Just look at the syntax of the INDEX statement to adjust your queries accordingly…

1. Loai says:

Thanks a Lot Andrew, working great

108. Sarunas says:

Hello
I need some help
I want to extract data, if it meets only one criteria from first column (C) or second column (E), into one row, but, if it meets two criteria from two different columns – into two different rows.
Thank You

109. Candace Marquardt says:

Thank you SOOOOOOOOOOOOOOO much! I work in a manufacturing environment and this tiny little trick is going to save so much time and money and error proofing and whatnot. Thank you so much!!!!!!!!!!!!!!!!!

110. Sire Robin says:

So what exactly is the difference between this method and sumifs?

111. Anna M. says:

Hi Andrew,

thanks for this article, it’s very useful and explained with extreme clarity!

I have one more question for you, though, as I have to do something more: I need also to sum the values found with this double comparison method.

To better explain: I have values in one column, corresponding to countries and purchase orders. I need to sum and report in a different tab all what was invoiced per each country, based on the purchase order.

With the INDEX + MATCH formula I manage to get only the first entry of the column where the double criteria PO + Country corresponds. I believe I should add a SUMIF formula before this one to have it working as I need it, however I’d need some help to build it because any of my attempts works.

Any suggestion?

112. Fish says:

Hi there, I’m using this formula for more than 50,000 lines.
Is there any way that I don’t have press (Ctrl+Shift+Enter) for each lines ?
Or there is a shortcut that I’m not aware of ?

1. Once you have used CTRL+SHIFT+ENTER for the first row, you can drag the formula down and it will maintain it’s “array formula” status.

113. John says:

Thanks Andrew, you described this topic in easy to understand ways that I understood with ease. I will guarantee that come Monday morning I’ll be puting to use what you just taught me, within minutes of arriving at the office. I’m very grateful you posted this.

114. Jannette says:

Hi,

I want to extract all the information from one sheet to another sheet where a field criteria is met. at present if I use a vlookup it only takes the first and ignores the rest. How do I get around this.

115. Cheryll D Bell says:

I owe you my total gratitude for explaining this! I’ve been using VLookup for ages but I’ve been at a loss at how to use more than one criteria. This is the solution to my problem! thanks so much!

116. Peter van Well says:

So Cool! CTRL, SHIFT, ENTER made the difference! Failed to realize that was the key to my problem. Now it works! Thank you for sharing!

117. Taufan says:

Really helpful, save so much time! Thank you very much

118. Victor says:

Thanks Andrew, this is really helpful! Now your website is my go to place!
Also the explanation is clear once you actually start reading:)

119. Kenny says:

I’m trying to use this formula to return multiple values. So it works for the first entry it finds when the two criteria are met, but once I drag down it just keeps returning the same results. What do I modify to have to keep searching through and pulling all cells that meet the criteria?

120. Alex says:

Thank you so much!.. this was amazing and worked perfectly. Saved the day!

121. Leo says:

Hi, thank you very much for the detail and clear instruction.

I would like to use this great method to lookup values from criteria of both numbers, but excel return #N/A. I have tried to change the ‘category’ in ‘format cell’ but still obtaining the same output.
It works perfectly if one of the criteria is not number though.
What have I missed out?

122. Ans says:

Hi

I have a little question.
I made a little selection, like this:
{=IFERROR(INDEX(Calendar!B\$7:B\$1005;SMALL(IF((Calendar!\$A\$7:\$A\$1005)=\$N\$1;ROW(Calendar!B\$7:B\$1005)-ROW(Calendar!B\$7)+1);ROW(1:1)));””)}

It works perfectly!

But … I have another question.
Is it possible that when I striketrough my source cell in the tab “Calendar” that it’s also strikedtrough in the destination cell?

Kind regards,

Ans.

123. Rafia says:

It didn’t work for me

I have property references against multiple job numbers and each job number has multiple values.

so, I want to match a job number & the value I already have against the table explained above. how would this work?

124. Erick says:

Thank you, this was extremely helpful!

125. Hello Andrew,

I just wanted to share my appreciation for this tutorial, and the example spreadsheet. I spent hours on other website trying to figure out how to do this, but this example finally helped me to achieve what I wanted to do.