Comments on: INDEX & MATCH in Excel - better alternative to VLOOKUP

This tutorial demonstrates the key strengths of Excel's INDEX / MATCH function that make it superior to VLOOKUP. You will find a number of formula examples that will help you easily cope with many complex tasks when VLOOKUP fails. Continue reading →

Comments page 7. Total comments: 614

  1. I am looking for excel formula for following scenario:
    Ex: Column A,C & E indicates color and Col B ,D & F Indicates price of the color. In Column G , We need to find minimum price for red color.

    ColumnA Col.B Col.C Col.D Col.E Col.F Col.G
    Row1 Yellow 15 Red 58 Red 32 Min price of red

    Please share which formula will be suitable to overcome on above scenario.

    1. Transpose the rows than color over price than highlight the new table than add an pivot table.

  2. Hi,
    Sir,
    Your explanation mathod is superb......
    Any simple person could also be understand....
    Are you a lecturer in any institute.....

  3. They aren't. They are basically random unique numbers.

  4. I have column A with a unique list of hundreds of names

    I have column C with a not unique list of thousands of names that includes multiple instances of names from column A, and 100 times as many names that are not in Column A

    I have column D with the same amount of rows as column C, all unique numbers.

    I need to take every name from column A, find all of the same name in column C and then give me a list of all the phone numbers it finds from column D.

    And I need all those numbers to be in one big column.

    1. Matthew:
      How are the names in C connected to the numbers in D?

  5. Thanks, I found this really useful.

  6. Fantastic tutorial.

  7. Hi Lu,

    A simpler solution to your question would be to sort your data by patient and date of visit. A conditional formatting can be used to indicate where a pain score is higher than the previous visit.

  8. Thank you for this wonderful tutorial. It only took me ten minutes to get the formula working for me. I like the way you write! Almost feels like you are a teacher in a classroom explaining it on a board. You made me feel like a student again, and lucky for me, one who just passed :-)
    Oh and one more thing, bye bye vlookup ...

  9. I think this is where my question would go... I have a dataset with a list of patients, their visit dates and the pain score they reported on that date. I need to flag all of the patients whose score increased on the subsequent visit (i.e., the treatment is not working). Tried with a pivot and lookup table to get the max score but can't get the dates compared. Assist please.

  10. Hi...I have 2 columns of data... Let say Column A as list of Capital as in the example above and column B is a remark, filled with text -"take" in Row 3 and 5 only for example. I want to use Index to identify if you find take - then show capital in the 1st row, in the 2nd row etc in column D for example as I want to have the result be positioned. How can I do it with index and it should be matched with which other formula. Can you help?

  11. Thank you! Thank you! I've spent 3 days working on a spreadsheet, copy and pasting values to only mess it all up. Your Index and Match function explanations enabled me to do the same thing 3 minutes. You are a genius!
    Congratulations on a tutorial well done!

  12. Hi,

    I'm trying to find something similar to this but I'm having trouble.

    I have a list of addresses in Sheet 1, the whole address is in one cell. So for example A2, A3, A4 all contain a different address. Then in B2, B3, B4 etc I want to display a area code, "3" or "8" for example based on the postcode in the cell in column A.

    I have a list of postcodes in Sheet 2 with the area code adjacent to them.

    I've tried with a VLOOKUP and a CELL but I cant fathom it out... if anyone can help I would be grateful......

    1. Hi J.E., if I understand you correctly, you might be better off using VLOOKUP here. So on Sheet1, cell B1, if you wrote something like:

      =VLOOKUP(RIGHT(A1,7),Sheet2!$A$1:$B$50,2,0)

      where the address in cell A1 contains a 6-character postcode with a space in the middle, you should return the area code.

      Hope that helps!
      Min

      1. Hi iam Trying to reverse VLOOKUP but its not possible right instead of VLOOKUP How to use index function
        Lookup values
        Master list( Some date )

  13. Is there a way to do a reverse index match? I have a calendar set up for appointments times by client name based on a weekly calendar grid. My software will give me a list of clients by date and time and I would like to populate the calendar without having to do it manually. Is there anyway to do this? Vlookup will do it by day, but not by time.

  14. Hello, I am interested in the posibility I see in your Merge Duplicates wizard. However, it appears this may only be designed for finding duplicates within a column. Is there a tool which can merge duplicates within a ROW, and return the column headers as the output? Specifically, I would like to find all the zero cells in a row, ignore the non-zero cells, and compile a list of column headers for the zeros in each row.

  15. hi ablebits team, please help with a formula that will compare multiple rows with multiple columns and match data of the same pattern.

    table 1 table 2
    a b c d a b c d e
    6 13 35 60 5 6 35 39 60

    i will really appreciate your help.

    thanks.

  16. Goodmorning Ms. Lana,

    I am from the philippines. and i am very glad reading your articles. I have learned many things.
    Do you have a free course regarding excel formula?
    Please reply.

    Thank you and mabuhay!

    1. Hi Renato,

      Thank you for your kind words! Regrettably, we do not have any courses on Excel. Our blog articles is all that we have :)

  17. Hi, Could you please help me with the below criteria,

    Eg,
    A In
    B Out
    C out
    D In
    E In
    F Out

    Now I want to get the result of above criteria separately Like In’s separate and Out seperate with the help of formula.

  18. I like too much. Thanks

  19. I have an excel sheet with 5 columns. And 40000 rows of data.
    1st column has Id no
    2nd Date
    3rd Name of institution
    4th Subject
    5th Course
    The solution i require is
    The formula should search for identical Ids, then search for identical date within that id, identical institution within that date, idential subject within that institution and concancenate the courses in one cell for that institution.
    Can this be done by an array or does it need to write a macro.
    I dont know much about arrays and macros. Please help find solution for this.

  20. I'm trying to return a list of individuals scheduled to work nights on a specific date.

    Sheet 2
    From Sheet 1, the names in column 1 associated with an array of shift values (i.e. N, N1CB, N2CB, N1, N2, etc) found in the column associated with the date entered in cell A1 on Sheet 2.

    Sheet 1
    Column A: Names
    Row 1: Date (starting 1/1 - 12/31)
    B2:NJ54: Variety of values showing shift worked (D, N, D1, N2, etc)

  21. DEAR
    I ENTERED A FORMULA BUT SHOWN IN #

  22. NOW WHAT DO IDO ?

  23. i input the FORMULA OF A6,=INDEX('RM DETAILS'!$J$5:$J$4101,MATCH(1,INDEX(('RM DETAILS'!$F$5:$F$4101='LAST PRICE'!A7)*('RM DETAILS'!$I$5:$I$4101=MAX(IF('RM DETAILS'!$F$5:$F$4101='LAST PRICE'!A7,'RM DETAILS'!$I$5:$I$4101))),0),0)) BUT OUT IS SHOWN #N/A
    NOW WHAT DO I DO ?

  24. This is my first time on any forum...
    I'm trying to create a formula :
    "Spreadsheet 1" is where I'm trying to get my information.
    "Spreadsheet 2" Cell C2, the formula should read, "Refer to Cell A1 "A", look in Spreadsheet 1, and put the values for MON in cell C2, do the same for cell C3 and so forth.

    Spreadsheet 1 contains: Spreadsheet 2:
    A B C A B C
    1 A MON 10 1 A
    2 A TUE 11 2 MON ______
    3 A WED 12 3 TUE ______
    4 B MON 20 4 WED ______
    5 B TUE 21 5 B
    6 B WED 22 6 MON ______
    7 C MON 31 7 TUE ______
    8 C TUE 32 8 WED ______
    9 C WED 33 9 C

  25. I tried copying the table but got the above result.
    But the crux is the first amount is the result from the same sheet and the second amount is result from another sheet.

  26. Hi,

    I did find the Index/Match multiple criteria formula very good however I have an issue in using this formula in different sheets.

    Table 1 = Lookup
    Table 2 = To be populated table

    I have used the formula to check two criteria - a. Employee number and b. Department name.
    The first row in the Lookup table in the relevant filed contains zero and so when I apply the formula the entire field is populated with zero [as in the first row of the Lookup table. I guess that this is because of the fixed reference rule applying.
    Can I have the formula without Fixed reference so that the like data reference will be picked correctly?

    Given below is the sample data table:

    Formula used - {=INDEX(Lookup,MATCH(1,([@[Emp no]]=Lookup[Emp no])*([@Dept]=Lookup[Department]),0),25)}

    Emp no Dept Amount Amount from another
    from same sheet sheet.Repeats first
    value
    1 Governance 38000 38000
    1 Customer Service 27000 38000
    2 IT 40000 38000
    2 Environment Services 35000 38000

    Thanks for the assistance.

  27. Hello

    I am looking for a formula which will find a value first [(say Stock Name) from a table where there are many stocks with day to day turnover values}and then sum or average the corresponding values [day to day turnover for a month]for that particular stock.

    Say for example:
    Stock Name 5/12/17 6/12/17 7//12/17 8/12/17 9/12/17
    ABBANK 20 22 25 15 12
    Brac Bank 35 38 40 80 70

    In another sheet we want to fetch the sum and average values for Brac Bank..
    Please suggest...

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  28. I don't usually do this but I have to give you praise for this wonderful example. Thank you so much!

  29. There are 3 shifts, Every shift needs change duty after a Week (Sunday) of the month through 24:00 hrs. as given below detail.

    Date: Friday,01/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Saturday,02/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Sunday,03/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Monday,04/12/2017 (Duty Shift would be Changed)
    Shift:A 16:00 TO 24:00 hrs (Evening-duty)
    Shift:B 00:00 TO 08:00 hrs (Night-duty)
    Shift:C 08:00 TO 16:00 hrs (Morning-duty)

    Please help me that how to set formula in excel that will show me Shifts: A, B or C will perform their duties on given date in 24:00 hrs.

    May kindly please be helped me in this case. in advance I shall be very thankful for him/her.

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  30. Hello,

    I'm trying to pull information from one sheet to another.
    Basically I have one sheet with the following information

    Distributor_Company Product_Name Price

    Now I want to create a sheet for each Distributor Company that looks at the first sheet and pulls all the Product_name it carries and price

  31. I have created the formula and its working only for each currency tab but i'm unable to create the formula in consolidated file could you please help/advise me for the above.

  32. Curr. Amt in loc.cur. LCurr Eff.ex.rate Formulae Currency Actual Rate Rate +10% Rate -10%
    AUD -21,928.99 SEK 6.4573 IF(AND(L2>$T$2,L2$T$2,L3<$S$2),"YES", "No") AUD/SEK AUD 6.45 7.09 5.80
    EUR -8,905.29 SEK 9.7415 YES CHF/SEK CHF 8.42 9.26 7.57
    EUR -5,668.19 SEK 9.7415 YES CNY/SEK CNY 1.27 1.40 1.14
    EUR -21,310.71 SEK 9.7535 YES DKK/SEK DKK 1.31 1.44 1.18
    GBP -3,030.09 SEK 11.16096 No GBP/SEK GBP 11.03 12.13 9.93
    GBP -11,536.34 SEK 11.11894 No JPY/SEK JPY 0.07 0.08 0.07
    NZD -21,607.94 SEK 5.78294 No NOK/SEK NOK 1.03 1.13 0.93
    NZD -3,559.39 SEK 5.78292 No NZD/SEK NZD 5.81 6.39 5.23
    USD -14,824.97 SEK 8.39942 No USD/SEK USD 8.42 9.26 7.58
    USD -14,824.97 SEK 8.39942 No CZK/SEK CZK 0.38 0.42 0.34

    I have created the formula but for each currency tab but i'm unable to create the formula in consolidated file could you please help/advise me for the above.

    Thanks,
    Chandru

    1. Hello, Chandru,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  33. Dear Ms. Svetlana

    I need FIRST CHARACTER TO BE NUMERIC(SINGLE DIGIT 1 TO 9) FOLLOWED BY A DASH SYMBOL AND ONLY TEXT CHARACTER FOLLOWED BY IT, AND TOTAL LENGTH TO BE 6, Kindly can you tell me how to use it in data Validation tool in excel 2016

    Product Code Product name
    1-ABZX XXXXXX
    9-BTGH XXXXXX
    8-RCTB
    6-XYXT
    0-ABCD

  34. Hello- This formula doesn't seem to be working for me. Can you tell me what I'm doing wrong.
    I have the following problem:
    Sheet 1 contains the following data:

    M N
    Period Room
    2 356
    2 246
    3 310
    6 361
    4 315
    5 313
    7 3
    1 GYM-C
    7 155
    2 201
    5 204
    7 266
    4 3
    6 259
    1 110
    1 364
    3 201
    5 3
    7 110
    7 266
    4 204
    6 1
    2 201
    3 201
    6 155
    1 240
    5 360
    2 201

    Sheet 5 contains the following data:

    A B C
    Room Wave Period
    1 A 5
    3 A 5
    110 B 5
    112 B 5
    114 B 5
    125 A 5
    151 D 5
    154 D 5
    155 D 5
    158 D 5
    160 D 5
    161 D 5
    201 B 5
    202 B 5
    203 B 5
    204 B 5
    205 B 5
    210 B 5
    213 B 5
    217 B 5
    240 C 5
    246 C 5
    254 C 5
    255 C 5
    258 C 5
    259 C 5
    264 C 5
    265 C 5
    266 C 5
    270 C 5
    304 A 5
    310 A 5
    313 A 5
    315 A 5
    351 D 5
    352 D 5
    355 D 5
    356 D 5
    357 A 5
    359 D 5
    360 D 5
    361 A 5
    362 A 5
    366 A 5
    212A B 5
    212B B 5
    Aqua Out A 5
    Gym-B D 5
    HCC A 5
    RCA C 5

    I need a formula that will match columns M & N of Sheet1 with Columns A & C of sheet 5 and return Column B of Sheet 5 in Sheet 1 "IF" Period=5.

  35. Svetlana,
    Thank you so much for your very logical and understandable explanation of the Index and Match function. I had dabbled with it in the past but never really grasped it. Your tutorial made it very clear! I was having problems on a spreadsheet using VLOOKUP where is would not allow me to have more than 2800 rows. I was able to switch to Index/Match and it works perfectly. Thank You!!!!

  36. Hello Svetlana,

    Thanks much for all the instructions on index/match functions.

    I am trying to index an array and find the value in Column B if the value in Column A match a certain text string ignoring all spaces and any characters other than letters or numbers. For example, the text string I am trying to match is K15 but the column A may have K15 shown as K15, K 15, K-15, K_15, or K _ 15 etc. How can I make the index/match functions consider K15, K 15, K-15, K_15 and K _ 15 as the same text string when it does the matching search?

    Thanks much and really hope I can get some help with this question.

    Drew

  37. Dear Svetlana Cheusheva,

    I am a demand planner.

    I have a 3 types of milk but I want raise demand on average consumption on milks, which formula will be apply on this condition.

    Regards,
    Mehmood

  38. Hi - I am wondering for this formula,

    =MATCH($C$78,$A$1:$L$1,0)
    This allows me to return the column # there is a match of the text in C78 in Row 1, from column A to L.

    Is it possible to extend this to an area i.e. multiple rows, vs single row? I want to do 20 rows i.e. $A$1 to $L$20 for example.

    If I want it to return a value I want to translate to for each of the column, how do I specify? Say if it's in column A, it should populate with "Apple", column B with "Orange" etc.

    Thanks a lot in advance!

    1. Hi, Mandy,

      since MATCH works with either column or row, you need to use VBA code or a macro to solve your task.
      But I'm afraid we won't be able to help you with that. Please ask around Mr. Excel forum for it.

      I wish I could help you better.

  39. =IFERROR(INDEX($K$1:$AA$1,MATCH("Z",K3:AA3)),"YTS")
    In the above Index-Match function, I wanted to Index a value from K1:AA1 by Matching and finding the final text value from K3:AA3. But, I want to exclude cells T3:V3. If I use the following, I get result only upto K3:S3. Please suggest a solution.
    =IFERROR(INDEX($K$1:$S$1,MATCH("Z",K3:S3)),INDEX($W$1:$AA$1,MATCH("z",W3:AA3),"YTS"))

  40. Thank you so much for this article.

  41. Is there a way to populate a result in a cell matching two columns, looking for a value in column A and populating the corresponding value from column B? For instance I have column A listing 1-90. In Column B I have a dollar amount. I want to have that dollar amount as the automatic result when the same number from column A is typed in another cell. I hope this makes sense! :-)

  42. Hi,

    I have one doubt in Index match function with multiple criteria. If we have more than one identical values in the main sheet, it will pull the first found value from the lookup table. Is there any solution?

    I have one sheet having Invoice number, amount, Supplier name, Currency. And in the main sheet for different Invoice numbers, same combination of Supplier name, currency and amount is there.

  43. Hello, I am new to Excel and needing to write some formula or vlookup to select the min and the max values of an array like this one:

    1 9/8/2017 20:20 x y 16,015.00 16,017.00
    1 9/8/2017 20:20 x y 16,055.00 16,057.00
    1 9/8/2017 20:20 x y 16,095.00 16,097.00
    1 9/8/2017 20:20 x y 16,135.00 16,137.00
    2 9/9/2017 15:55 x y 15,815.00 15,817.00
    2 9/9/2017 15:55 x y 15,855.00 15,857.00
    2 9/9/2017 15:55 x y 15,895.00 15,897.00
    2 9/9/2017 15:55 x y 15,935.00 15,937.00
    2 9/9/2017 15:55 x y 15,975.00 15,977.00
    3 9/10/2017 1:02 x y 15,615.00 15,617.00
    3 9/10/2017 1:02 x y 15,655.00 15,657.00
    3 9/10/2017 1:02 x y 15,695.00 15,697.00
    3 9/10/2017 1:02 x y 15,735.00 15,737.00
    3 9/10/2017 1:02 x y 15,775.00 15,777.00
    4 9/13/2017 1:26 x y 15,415.00 15,417.00
    4 9/13/2017 1:26 x y 15,455.00 15,457.00
    4 9/13/2017 1:26 x y 15,495.00 15,497.00
    4 9/13/2017 1:26 x y 15,535.00 15,537.00
    4 9/13/2017 1:26 x y 15,575.00 15,577.00

    Would need to extract the 16,015 and the 16,137 for the cells with a 1 on the first column then do repeat the same for column with value 2, 3, 4...
    The values needed to be returned for column with a value of 2 would be 15,815 and 15,977, column with a value of 3 would need to return 15,615 and 15,777......

    Thank you very much in advance

    Ev

  44. I’m having some difficulty figuring out how to utilize Excel’s reference functions to do horizontal and vertical lookups, where the task is to search for a particular value in a specified row, and return the text value in the header of that value’s column. It seems like a combination of Index & Match would be desired, but I can't figure out a workable syntax that doesn't produce errors.

    For example
    Column A contains record ID’s. (e.g. 101-200)
    Columns B-F contain options (e.g. apples, oranges, pears, etc.)
    Cell values contain a 1 for first choice, 2 for second choice, 3 for third choice, blanks for unranked options.

    An example of the desired outcome is to lookup a particular id (which would be identified by cell reference or formula, not a fixed row #), find the value ranked 1 in that row (values will not be ordered), and return the name of the option associated with that rank. For example, lookup record 25, find rank 1, return oranges if oranges is the header of the column containing rank 1 for record 25.

    Any help would be greatly appreciated. Thank You.

  45. Hi there,

    I have a question that I am hoping somebody here might be able to provide some insight. So thanks in advance.

    I have a table of values (%'s) as outlined below:
    | Decile 3 | Decile 4 | Decile 5
    Metric X 25.72 - 28.72 28.73 - 30.38 30.39 - 31.52
    Metric Y 18.96 - 24.69 24.70 - 35.23 35.24 - 45.87

    Also, I have data(%'s) as follows:
    Metric X: 100 / 400 = 25.00. Now, based on this score, this results in the 2nd decile, as referenced above. Assuming that I "freeze the denominator", how much would the numerator (100) have to increase in order to reach decile 5? It would be easy to do some simple algebra to calculate that number.

    However, I am trying to come up with an automated way to calculate the number to increase the numerator to achieve a given decile.

    *Please disregard "Metric Y" above, but note that I have many metrics with different decile ranges in this table.

    I appreciate all input.

    Thanks!

    1. **Correction, a 25.00 would result in Decile 1 that is not shown. That is not key, but I wanted to explain since I just caught that mistake.

  46. Hi,

    Really need your help.
    Trying to create an excel spreadsheet that will help with costing for a construction project.

    Column A: Area of task
    Column B: Task
    Column C: Cost code

    I want to match the area and task and for the cell to output the cost code according to the lookup table. I tried to use the formula you have provided above but keep getting "#Value" - what am I doing wrong?

    The values I want to match are part of a drop-down list. Is this my problem?

    1. I think I figured out the problem - needed to press Crtl + Shift + Enter. How come we need to do that for this formula?

  47. Is it possible to find answers that DON'T match? I have a list of people who have completed trainings. I can see which trainings they have completed, but I would like to see which ones they have not. I currently have the list of everyone who has completed certain courses, and I have a list of all required courses. I'd like to have a list of each name and the courses still required to be taken. Any suggestions would be helpful!

  48. Hi there,

    need help plz, am a beginner and trying to create a small template where I will be calculating price of goods plus GST applied. In description column I will write details of goods, now is there a way where I can apply a filter saying lets say description column is B. GST is applied on everything except food items. I have five items to list i.e.tea, coffee, sugar, creamer and Sweetener. so what formula can I try which will say if these 5 items GST will be 0 else it will be E*5%.

    DESC QUANTITY UNIT PRICE SUB TOTAL GST
    PILLOW 4 13 52 2.6
    SUGAR 3 10 30 1.5

    1. Hi,

      I'm sorry, I'm afraid your task is not entirely clear for me. But from what I understand, I believe you may want to check out the following articles that may help you build the formulas:
      COUNTIFS - to count the items.
      IF - to return 0 or multiply by 5% depending on the COUNTIFS result.

      I really hope you'll find those tutorials helpful.

  49. Hi Svetlana, very interesting article. I was looking for how to do it but looking up for a value in a whole 2-dimensional range (matrix) instead of an array. The problem is that MATCH function only works with 1-dimension arrays. Any ideas?

  50. I have an array with column headings I am sorting on. Once I find the right column I need to copy all cells below to another location, where the original formula resides. Tried using INDEX and Match but come up with some crazy results. Can I use Index and Match to do this?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)