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 6. Total comments: 614

  1. Hi,
    I need to look up for a multiple colums with offset for client invoice # po# amount details etc. Please help.
    Regards
    Rashmi

  2. Hello,

    I am trying to reference a sheet that runs horizontally and use INDEX MATCH to fill in another sheet that runs vertical. But is based on multiple criteria.

    IE :

    Customer Measure wk 35 wk 36 wk 37 wk 38
    Erik Retail $9.99 $9.99 $9.99 $9.99
    Erik Cost $4.50 $4.50 $4.50 $4.50
    Erik Sellout FCST 100 100 150 100
    Erik Channel Inventory 250 150 0 100

    I need to look up "Erik", by "sellout FCST" by "wk 37"

    I understand INDEX and with MATCH which was a fantastic tutorial. But i am having trouble adding this small example above.

    Thank you for any help provided.
    E

  3. Hello. I am working on a spreadsheet and trying to figure out the best way. What I need is to find a match from the sheet I am working on which starts on E3. I want to be able to look on the same row on a different sheet that matches the text in E3. It will have a range to match from E3-J3 on the other sheet. However, I do not want it to simply return the value found in that matches, I want it to designate text for each column. Basically if row E3 has the text Cookie in it and it finds a match on the other sheet row 3 in the columns E3-J3 that also contains Cookie, how can I make it return a value based on the column it is found in? Any suggestions would be greatly appreciated.

  4. Hi,

    Which formula do I put in D2 to achieve the following?

    If A2 contains all the text (consecutively but not an exact match) from any cell from range B2 through B50, then display the corresponding text that's currently located in column C in that corresponding row. So if it finds that A2 contains text (consecutively but not an exact match) that exists say in B47 then it should display the text from C47 in column D2.

    Your help would really be appreciated

  5. Hi, My data is A4:J22.
    I have column B which contains the category name, used multiple times.
    I have column F containing a dollar value amount still to be paid.

    In cell K3, I want put the total dollar value (from column F) for specific text "Meal Entertainment" in column B.

    Eg:
    Column B: Column F:
    Meal Entertainment $20.00
    Living Expenses $1,000.00
    Living Expenses $50.00
    Meal Entertainment $100.00

    So, in column K3, I want to end up with an amount of $120.00.

    Thanks in advance.

    1. Thanks. I will give this a try.

      1. THANK YOU!!!! It works perfectly.

        1. Lee-Anne, could you share the solution? It appears the previous comment has been deleted, but I am running into the same problem!

  6. Please help me to match information in order to return the correct code to Column A.....Column B (desc) contains information to be matched to column C (type) and then return Column D (code) to Column A....Column B is a description column and contains different information to describe type, but is all the same. Thank You for your Help!
    Ex.
    B C D
    1 Motor Truck Sc Motor Truck Sc MTS
    2 Truck Sc
    3 MTS

  7. Plsss help me, how can I lookup Apple3 & Orange4 value-

    Apple1 80
    Apple2 100
    Apple3 90
    Orange1 120
    Orange2 220
    Orange3 180
    Orange4 160

  8. Hi... I am working on pulling information from one tab containing several columns of numbers into a separate tab. I used the index function and it works great, but only for one instance of the "search" box. I'm using it to pull information from various events that have occurred at the arena in which I work. How would I adjust the formula to make it pull all of the events that have the same name? For example, Jimmy Buffett has performed multiple times here, but when I type "jimmy buffett" in my search cell, it only pulls data from one show, when he has has four shows here.

  9. Request your help in below issue. Want to match the cell value in column but unable to do that. Please help me.

    Column1 Column2
    2133 GMA
    2133 MCA
    2133 MCA2
    6488 USI
    6488 GMA
    7645 MCA2
    7645 USI
    6488 NOT

    Column4 column5(GMA) Column6(MCA2) Column7(MCA)
    2133
    6488
    7645

    Request your help in creating the formula that can match the value against column4 from column2 and column2.

  10. Sheet 1
    Line# PO Qty Reciver
    2 222000 10 A20535
    6 222000 15 A20587
    9 222000 20 A20687

    Sheet 2
    line# PO Receiver
    2 222000 A20535 ;A20587;A20687 (Answer)

    I need the answer by making formula in Sheet 2 as display above.

    Please help

  11. Here is a link to my spreadsheet on google drive - I was not sure how to post it here https://drive.google.com/file/d/1jfoMPEQQhsAA90tyv43K3p9FqF3LAyFv/view?usp=sharing

    I have three columns of data that are plotted on a graph. You will see on the graph two arrows, and I would like excel to return the x axis values (frame number) for where these arrows point (heel strike). This should be more accurate and time effective than me doing it manually / visually.

    Frame number for heel strike 1 is defined by the GRF data where it first meets or exceeds the threshold displayed in G9. Manually I can see this is frame number 80, but I want excel to return it to me. To find the frame number of heel strike 2 excel will now need to use a velocity threshold. So, based on the previous heel strike velocity value can excel find the next occurrence of this velocity value. Ideally the data point either side of this value should be greater to ensure it is heel strike, and not another point on the graph where the magnitude of velocity is similar. I have tried using the match and index formulas but haven’t got it work. I not sure if there are better functions to use than this.

  12. I have a list of data (numbers). I want to find if any of them are greater than 9 and if so look in all columns to the right of it for values greater than 0 (looking for 2 more). There are 4 outcomes 1) no >9. 2) >9 But no >0. 3) >9 and 1 that is >0. 4) >9 and 2 that are >0. All with more than 2 would return the value if 2 were met.

    What group of functions would I use to make this happen.

    1. John:
      Can you post a small example of your sheet with some sample data?

  13. How can I match two columns, say patient ID and date of visit and then retrieve the test result from a array of multiple visits by the same patient? ie there are results for the same test from multiple visits.

    1. You could try concatenating the vlookup, that might help.

  14. Thanks a lot! It really helped me in completing my task of vlookup from 1 million records sheet.

  15. Hello,

    I have a list of unique account numbers in columns A, B, and C. How can I tell if any of the same account numbers appear in a different column?

  16. I have two sheets, one sheet(A) having unique codes and sheet (B) having same unique codes, category (PT, ST, IT etc..) and date column
    Here sheet (B) have multiple records for same code, same category

    Now, In Sheet (A) I want to get the min date for PT and max date for PT with reference to sheet (B)

  17. Hello

    Need an help regarding the "Index" function

    here I was trying to grab Index with "v-look up", hence I'm using it in getting data from another sheet, I'm seeing some popup stating formula is wrong,

    Please guide me clearly with formula how to use "Index" with "V-lookup"

    Appreciate your help

  18. Using the INDEX MATCH to return data based on the matching of two cells in a different spreadsheet, is there a way to make the MATCH process more than just the first located match? I am using a Google Sheet with a Form to add in a person's intial certificates but also to add when they complete a new certificate. I would like the line for that person on the main tab to update with ALL form entries. Here is my formula:
    =ArrayFormula(IfError(INDEX('Form Responses 1'!$A$2:$AH$1001, MATCH(1, ($A2='Form Responses 1'!$B$2:$B$1001)*($B2='Form Responses 1'!$C$2:$C$1001),0), 6),""))

  19. please help me how can i separat the name of students which are absent in daily roll call from excel attandance sheet

  20. Great tutorial! My question would be if I have 'like' numbers in my array can I still pick up the value if they have different labels attached to them? For example I am applying a score to tickers, then ranking them, top three tickers would be ISRG, GOOGL and AAPL. ISRG has a score of 20, GOOGL and AAPL have a score of 19. Currently my situation and formula (using hlookup) returns in rank 1 ISRG, rank 2 GOOGL, and rank 3 GOOGL. Is there a way to where matching values still return the given ticker, thus showing AAPL instead of two GOOGL?

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

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

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

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

  25. Thanks, I found this really useful.

  26. Fantastic tutorial.

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

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

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

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

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

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

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

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

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

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

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

  38. I like too much. Thanks

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

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

  41. DEAR
    I ENTERED A FORMULA BUT SHOWN IN #

  42. NOW WHAT DO IDO ?

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

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

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

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

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

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

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

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

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