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 2. Total comments: 224

  1. ColA ColB

    Fruits Count
    Apple 10
    Orange 20
    Mango 30
    Apple 20
    Banana 10
    Orange 30
    Apple -30
    Orange -20

    Condition is:
    I need a unique array list of fruits in ColC and do not need the fruits on list whose count is 0. Here, Apple has count 0 as (10+20-30).

    Result may be:

    ColC

    Orange
    Mango
    Banana

    Could you please clarify me below condition. Can we use SUMIF function in Index+Match+Match. I had tried one formula to make output as ColC but did not work. Or, if you can suggest one will be much appreciable.

    {=IFERROR(INDEX(A2:A9,MATCH(0,COUNTIF($C$1:C1,A2:A9)+(SUMIFS(B2:$B$9,A2:$A$9,D2)0),0)),"")}

    Thank you very much in advance.

  2. hi,
    I'm sure this is a very simple but I'm struggling in one situation. Need your kind assistance.

    ColA ColB

    Apple 10
    Orange 20
    Mango 30
    Apple 20
    Banana 10
    Orange 30
    Apple -30
    Orange -20

    Condition is:
    I need a unique array list of fruits in ColC and do not need the fruits whose count is 0. Here, Apple has count 0 as (10+20-30).

    Result may be:

    ColC (Sorted) ColC

    Orange Banana
    Mango Mango
    Banana Orange

    In addition, if this list can be sorted in alphabetical order by formula will be great. I'm using Excel 2016.

    Looking forward to your kind reply.

    Thank you very much in advance.

    1. Hello!
      Your version of Excel has very limited options for solving your problem.
      You can use these instructions to get a list of unique values from column A.
      Then, in column D, you can calculate the amount from column B for each unique item. To do this, use the SUMIF function.
      I hope my advice will help you solve your task.

  3. i need an excel formula that will refer back to a chart to look up data and return the correct data. In my spreadsheet, I enter a width in A1, a Depth in B1, a Thickness (from a drop down list) in C1 and then I want the correct cost to populate in D1 based on a table on another worksheet. Can that be done? The table consist of all of the possible dimensions and their costs.

    Please HELP... anyone!

    Thank you sooooo much!

  4. I have a formula with INDEX and MATCH. I use this to get dates from one sheet to another (to keep it brief). In the column in sheet B are several empty cells. These empty cells in sheet B cause the cells in sheet A to be filled with '00-01-1900'. This makes sense, but I would like to fill these with a small text message: "No date". Instead of printing 'no date' it prints '00-01-1900'. How do I solve this?

    My current formula looks like this:
    =IF(sheet B!G2=" ", "No date", INDEX(sheet B!$G$2:$G$4408, MATCH(sheet A!H2,sheet B!$F$2:$F$4408,0)))

    1. Hello!
      I believe the following formula will help you solve your task:

      =IF(B!G2="", "No date", INDEX(B!$G$2:$G$4408, MATCH(A!H2,B!$F$2:$F$4408,0)))

      I hope it’ll be helpful.

      1. Hey,

        Thank you for your quick reply. It doesn't change anything. But I am using a Dutch version of Excel: therefore I translated my formula to English. The formula I have before me is this: =ALS(Inspecties!G2=" "; "Geen inspectiedatum"; INDEX(Inspecties!$G$2:$G4408; VERGELIJKEN(Hoofdbestand!H2;Inspecties!$F$2:$F$4408;0)))

        I tried to replicate your adjusted formula, but I get the same result. Can you look at my formula again? If you need more information, just say the word.

        Thanks,
        Patrick

          1. Hey,

            Sadly, I get the same outcome :(.
            Can you think of anything else?

            Thanks,
            Patrick

  5. Hi, I am trying to fetch a value using formula but unable to do so as it is returning me #REF
    Formula i used is as below.
    =INDEX(Priority!A5:G16,MATCH(A16,Priority!A:A,0),MATCH(B15,Priority!B4:G4,0))

    Could you please help !!

    1. Hello!
      The #REF error means that your INDEX function wants to show a value that isn't there. For example, in the range A5:G16 there are 12 lines. You want to show the value from the 14th line.
      I hope my advice will help you solve your task.

      1. This is what my sheet is
        P1 P2 P3 P4
        March 4 2 7 9
        December 5 1 8 6

        1. This is what my Sheet is
          P1 P2 P3 P4
          March 4 2 7 9
          December 5 1 8 6

          and this is what i am trying to find
          P3
          March 2

          using formula "=INDEX(A21:E23,MATCH(A17,A21:A23,0),MATCH(B16,B21:E21,0))"

          But it is returning me value of P2 instead of P3. Can you please help why it is returning value of a column before.

  6. Hi, i'm trying to create a formula that allows me to search a column for a reference number which exactly matches the reference number on another worksheet within the same workbook. I need it to return a Yes or No.
    On worksheet 2 In cell H3, I would like it to return a Yes if the ref number in Cell E3 matches any of the ref numbers on Worksheet 1 Column E Range E2:E500.

    1. Hello Lisa!
      If I understand your task correctly, the following formula should work for you:

      =IF(ISNUMBER(MATCH(E3,Sheet1!E2:E500,0)),"Yes","No")

      I hope my advice will help you solve your task.

  7. How do I write the following formula:
    If B16:B46 are all Y, then this cell should be Y. If any of the cells between B16:B46 are N, then this cell should be N.
    Also, how do I write this formula with two factors:
    If B16:B46 AND C16:C46 are all Y, then this cell will be Y. If any of those cells have an N, then this cell should be N.

    1. Hello Mohammed!
      If I understand your task correctly, the following formula should work for you:

      =IF(ISNUMBER(MATCH("N",B16:B46,0)),"N",IF(IFERROR(MATCH(1,IF(B16:B46="Y",0,1),0),"Y")="Y","Y",""))

      I hope this will help

  8. I have a result set as below:
    COL A COL B
    1111987 1224
    1119361 1072
    1119361 1223
    1119361 1224
    1123961 1223
    1123961 1224
    1124136 1072
    1124136 1223

    Since No# - 1124136, isn't having a combo of 1224, I wish to have that as a result.
    Please assist.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =INDEX(A1:A8,MATCH(0,COUNTIFS(A1:A8,"="&A1:A8,B1:B8,"=1224"),0))

      I hope this will help

  9. I have name of students in column B and grade scored in column BT in sheet 1. I want to pull the name of students who scored A to sheet 2, B to sheet 3, C to sheet 4 etc

  10. I am looking to match based on two unique numbers that have the same result. The lookup tables or array results are in a different
    Example.

    Column A = US product ID #
    Column B = CA product ID #
    Column C = the product name of both Column A and Column B of that Same row, so can have either a corresponding US or CA Product ID

    Example: US1234 or CA123 = pencils

    For the entry, they will only enter in product ID number below, and I want to dynamically return the product name without knowing if it will be a US ID or a CA iD
    Product ID Product Name

    Thanks

    1. Hello Joseph!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(INDEX(C1:C5,MAX(IFERROR(MATCH("*"&$E$1&"*",A1:A5,0),-1), IFERROR(MATCH("*"&$E$1&"*",B1:B5,0),-1))),"ERROR")

      where E1 -- Product ID

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  11. I am using the formula to copy values from one sheet to other, from a table with 8 columns. The value has to come from the 8th column. The formula is working fine. Kindly inform if any variable can be put instead of writing 8.
    Thanks.
    Sheets("EXE_1").Select
    Range("O4").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR((INDEX(MIN_WIP!R4C17:R20C25, MATCH(EXE_1!RC[-13],MIN_WIP!R4C17:R20C17,0),8)),""0"")"

    1. Hello Manas!
      It is possible to insert an expression, which will return a number, instead of "8" in your formula. However, to give you a full and accurate answer, additional information on the result you would like to get is required. Moreover, I would like to see your formula in standard view, not R1C1.

  12. It seems a Windows update has now broken this formula and it produces a #SPILL error message. Can you produce a workaround for this?

    1. Hi Graham,

      I've retested the formulas in our sample worksheet. All seem to be working normally. Can you please specify exactly which one returns an error?

  13. Hi, Svetlana. Thank you for a great tutorial. Unfortunately, I'm quite new at Excel, and have trouble applying INDEX/MATCH in my sheet. I hope you can help. Here goes: I have one summary tab (X) where each column's data would be ported over to different tabs (Y) in the sheet. Each column of X will be numbered at their corresponding topmost cell from 1 to 10, and each Y tab will be labelled from 1 to 10 in a specific cell. Would it be possible to match/lookup X 1 to Y 1 (X 2 to Y 2, and so on) so that the range of data in column X 1 ports over to a column in Y 2? (I hope what I wrote makes sense).

    1. Sorry. For the last sentence, I meant to port the range of data in column X 1 onto Y 1.

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

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

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

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

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

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

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

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

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

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

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

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

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

  27. Wow, amazing articles!

    How did you learn this all?

    1. Thank you, Jenny! Just read a lot, practice a lot, and work with colleagues who know a lot more :)

  28. Hi, can someone help me, I have spent 3 hours trying to find what formula I need.

    I have 3 columns, 550 rows of data. Column A contains 25 options, Column B contains a 22 options for each of the options in column A, column C contains a number.

    I have done 2 dropdown lists for Column A & B. what i need is a formula to look at what i have selected in the cells that have the list and bring in the number from column C.

    Please help, Thanks

    1. Hi Collin,

      You should use the following array formula:
      {=INDEX(C1:C550, MATCH(G2&H2, A1:A550&B1:B550, 0), 1)}
      The dropdown lists are in G2, H2.
      To enter this formula press CTRL+SHIFT+ENTER.

  29. Hi Svetlana,

    I need your help. I am in Recruiting.

    In recruiting a potential candidate would pass through different stages (sourced-> not suitable/reject/voice message-> submitted-> interviewed -> offered-> hired).

    The current ATS displays a single candidate at multiple stages (which gives me inaccurate report)
    What I need is to be able to pull proper report with total unique sourced candidates, not suitable candidates, voice messages etc.
    I need your help, if it is possible to have excel create a hierarchy system, where excel can pick one option (Interviewed), in case 2 or more options available (sourced, interviewed, left voice message etc).
    And this needs to be multiple criteria, as I would be lining them against different job IDs.

    Thanks
    Subash

  30. Good day Svetlana

    I have data in several columns, the most important are 6 columns

    1.Each set of data has unique number in column 1 (unique number start from 1 to …)
    2.Each unique number has a set of rows with identical description in column 2 (Comm. Payable x 2 rows, Funding Charges – this can be multiple rows, Trade Payable or Trade Receivable)
    3.Each unique number amount of items in column 3 (amount in only 1 row, same row Trade Payable or Trade Receivable from column 2)
    4.Column 4 and 5 are buying price and selling price respectively – on same row with Trade Payable or Trade Receivable from column 2
    5.Column 6 has profit & loss. Each description in column 2 rows has a value in this column

    # Description Items Buying Selling P/L
    86 Comm. Payable 0 0 -5
    86 Trade Payable 709 0.485 0.439 -32.61
    85 Comm. Payable 0 0 -5
    85 Trade Payable 1740 0.345 0.315 -52.2
    85 Funding Charges 0 0 -0.06
    86 Funding Charges 0 0 -0.04
    85 Funding Charges 0 0 -0.06
    86 Funding Charges 0 0 -0.04
    86 Funding Charges 0 0 -0.03
    86 Funding Charges 0 0 -0.05
    86 Comm. Payable 0 0 -5
    85 Comm. Payable 0 0 -5

    I want to change the descriptions in column 2 rows to be column headers, in a way that each unique number will now have only one row with summation of descriptions

    # Buying Selling Items Funding Charges Comm. Payable P/L
    85 0.485 0.439 709 -0.12 -10 -52.2
    86 0.345 0.315 1740 -0.16 -10 -32.61

    Thank you
    Ron

    1. Hi Ron,

      Please try to do the following:
      1. Add the additional column G in your table with the following formula:
      =IF(COUNTIF(A2:A13, "="&A2) = 1, A2, "")
      Now you have only unique values for column A in column G.
      2. Add the additional columns with the following formulas:
      =SUMIF(A2:A13, G2, C2:C13) - to sum the Items column
      =SUMIF(A2:A13, G2, D2:D13) - to sum the Buying column
      =SUMIF(A2:A13, G2, E2:E13) - to sum the Selling column
      =SUMIF(A2:A13, G2, F2:C13) - to sum the P/L column
      3. Remove the blank rows to get the results.

  31. Hi all,
    i need to sort the B no. column datas with respect to the A no. column datas, (both columns are text format)

    1. Hi Jinson,

      Please try to do the following:
      1. Select the range with your data - A1:B100 for example.
      2. Select the Data ribbon tab and click the Sort button.
      3. Select the Column A in the Column.
      4. Click OK.

  32. I want formula for following condition.

    If age >22 then Basic*60% else if age =16 and less than 22 then basic*50% and if age=11 then basic*40% and if age=6 then basic*30% and age<=6 then basic*20%

    Regards
    Rajaraam

    1. Hi Rajaram,

      You should use the following formula:
      =IF(A1>22, B1*1.6, IF(A1>=16, B1*1.5, IF(A1>=11, B1*1.4, IF(A1>6, B1*1.3, B1*1.2))))
      The age values are in Column A, the base values are in Column B.

  33. i don't know if this is relevant, but i have 5 cells that all have a "=IF" function in so it will look at a cell and return "TRUE" or "False" value.
    what i want to do is, have it look at all 5 cells that were returned and number for the "TRUE" cell
    cell values are
    A2 = False
    B2 = False
    C2 = True
    D2 = False
    E2 = False

    then i want it to look at all 5 cells and return a number for the "TRUE" cell so the result must be "3" for this example
    the numbers that will be assigned to the cell will be

    A2 = 1
    B2 = 2
    C2 = 3
    D2 = 4
    E2 = 5

    how would i do this?

  34. Is there a way to autofill the array INDEX/Match Formula? I've created the formula in row 4 and need to drag it down and autofill though 2000 rows. Thank you in advance!

  35. Using this array,
    =LOOKUP(2,1/(Z$18966:Z$21192=G16930),AA$18966:AA$21192)

    is it possible to return the value?

    Thanks

  36. Dear Svetlana. Thank you very much for you explanations.
    I have a qyuestion to you, taking the same data table in your explanation.
    I would like to now how to handle INDEX/MATCH in the following case:

    Below each product and in the same line of the customer, I would like to have the $ sum

    Products Lemons Apples Sweets Pies Biscuits
    Customer ___________________________________________
    Dan Brown ___________________________________________
    Jeremy Hill ___________________________________________
    Romert Acey ___________________________________________
    Tom Boone ___________________________________________

    Thanks

    Carlos

    1. Hello, I'm sorry, I manage to see my mistake in writing the formula, and make it work.

      Thanks anyway

      Carlos

  37. Hi

    I have two columns in a file. Date (Column A) and Amount (Column B)
    Date Amount
    8/22/2016 600
    8/23/2016 600
    8/24/2016 200
    8/25/2016 800
    8/26/2016 600
    8/27/2016 600
    8/28/2016 600
    What I want to achieve is described below.

    In another sheet, I have certain dates. Not all the dates from above, but specific dates as below. And what I want to achieve through a formula is what is under column Sum.
    Dates Sum
    8/23/2016 =600+600
    8/25/2016 =200+800
    8/28/2016 =600+600+600

    I'll explain the formula I want taking 8/28/2016 as an example.
    I have calculated the sum until 8/25/2016. So I want to look at the data
    from the next row after 8/25/206 until the date specified here, that is 8/28/2016 and then sum it up.

    If I take 8/25/2016 as an example, then I have calculated the sum until 8/23/206. Hence I would have to pick up the numbers from the row below 8/23 (from the data) which is 8/24 until 8/25 and then sum that up.

    Can someone please help me with this?

    1. Sorry, I figured it out!! :-)

      =SUM(INDIRECT("Attendance!E" &Match(A22,Attendance!$A$1:$A$842,1)& ":E" &Match(A23,Attendance!$A$1:$A$842,1)))

      Cell reference here doesn't really match the example data I have given above, but you will get the concept

      1. Correction, you need to add a + 1
        =SUM(INDIRECT("Attendance!E" &(Match(A22,Attendance!$A$1:$A$842,1)+1)& ":E" &Match(A23,Attendance!$A$1:$A$842,1)))

  38. Hi I am trying to find a value in Column A and I know the value in Colum B.

    For example, I have placed a value from Column B, cell B10 in a parallel sheet. next to that I want to place the value of column A, cell A10. can I please have any advise on this ?

    1. Please do not respond to this , I have reached the solution !

      Thanks :)

  39. Hi, got a simple question. In your example of "Look up with multiple criteria using INDEX MATCH," the customer/product/qty example, how would you write a INDEX MATCH formula to average the Biscuits quantities?

    1. Hi Jason,

      I believe it's easier to do with the AVERAGEIF formula like this:

      =AVERAGEIF(B2:B10, "biscuits", C2:C10)

      Where B2:B10 are products and C2:C10 are quantities.

  40. Dear mam/sir i reading in vlookup formula but i did not clear so kindly that by example

  41. 4 4.25 4.5 4.75 5
    1.10 1.11 1.13 1.14 1.15
    1.08 1.09 1.10 1.11 1.12
    1.06 1.07 1.08 1.08 1.09
    1.04 1.06
    1.02 1.03
    1.00 1.01
    -22 212 233.2 235.9 238.5 241.2 243.8
    -21 206 226.6 229.2 231.8 234.3 236.9
    -20 200 220.0 222.5 225.0 227.5 230.0
    -19 194 213.4 215.8 218.3 220.7 223.1
    -18 188 206.8 209.2 211.5 213.9 216.2
    -17 182 200.2 202.5 204.8 207.0 209.3
    -16 176 193.6 195.8 198.0 200.2 202.4
    -15 170 187.0 189.1 191.3 193.4 195.5
    -14 164 180.4 182.5 184.5 186.6 188.6

    Hi,please see above which is driving me crazy. I have a number I want to match with row 1 (eg 4) and I have a value corresponding to the main table (eg 192.2) and I want it to give me the left hand column value nearest to (192.2) which in this instance would be -16. Is this possible please?

    1. Sorry, the row 1 item (4) is in cell C1 and the nearest number to 192.2 is 193.6 in cell c14 and I would like it to return -16 from cell A14.

      Hope that makes more sense.

  42. Hi,

    I need to add prefix with as no of zero as required to the amount field till the lenth becomes 11 digit.

    For example, if amount field is 1000, then it prefix with 7 zeros as '00000001000' and if amount field is 10000, then it prefix with 6 zeros.

    1. Hi Shankar,

      Kindly right click on cells and select format cells. Now select format special and manually type 11 zeros (00000000000) in type box. Hope this will solve your requirement.

  43. Hi I'm still unclear on where "1" came from in the chapter of "Look up with multiple criteria using INDEX MATCH"

    Is there any hidden formula to return "1" value ?

    1. Hi Jacob,

      I guess you are asking about this part of the formula:

      MATCH(1, (A2='Lookup table'!$A$2:$A$13),0) * (B2='Lookup table'!$B$2:$B$13)...

      There is no hidden formula, 1 is "hardcoded" in the Match formula as the lookup value. The formula checks two columns in the main table, column A ("Customer name") and columns B ("Products"), for matches in the lookup table. If the match is found, the above equation returns "1", otherwise "0". And that is why we use "1" as the lookup value, we just want the MATCH function to return an item's position only when both criteria are true.

      1. sorry for bothering you. as i'm beginner,i understand your intention why you put hardcoded "1". Without any formula like "if", excel current sytx enables user could compare and return either "1" or "0" ?

        1. Hi Jacob,

          Generally, yes. More precisely, the functions return the logical values of TRUE that equates to 1, and FALSE that equates to 0.

          In fact, I started to write a more detailed explanation for you, and ended up re-writing that part of my blog post :) Please check out the revised Look up with multiple criteria example. Hopefully the current explanation makes more sense. And thank you for your question!

          1. Thanks for your kindness :)

  44. Greetings,

    I'm stuck with an odd ball for days. The Cell that contains the Value I want exceeds 255 characters/symbols (not the value I'm looking for).

    Formula I used trying to capture the row containing '9F0702':
    MATCH("*9F0702*", A2:A10, 0)

    Lets assume it's contained within A5

    Data in A5 (321 characters):
    7081975A0851843600000810395F24031908315F25031410015F280207845F3401028C279F02069F03069F1A0295055F2A029A039C019F37049F35019F45029F4C089F34039F21039F7C148D0C910A8A0295059F37049F4C088E14000000000000000042014403410342031E031F039F0702FF009F080200029F0D05BC50BC08009F0E0500000000009F0F05BC70BC98009F4A01829F420207849000

    I Get: N/A

    How can I get my formula to work?
    The tutorial explains what to do when the value you are looking for exceeds 255 but not when the Cell Value itself exceeds it.

    Please please help me out.

    1. Hello,

      You can try the following workaround:
      =MATCH("*9F0702*",LEFT(A2:A10,255),0)

  45. Hello,
    I have a 93k line data set, in which I've utilized the annoying conditional formatting tool for duplicate lines. I am attempting to group all duplicates and remove conditional formatting, however, it is no easy feat. With such a large data set it causes excel to crash during filtering.

    I have attempted using lookups, match, index/match functions to no avail. Each duplicate line is not an exact duplicate because of the format (i.e. OPI or opi). Some are capitalized and may have additional letters and/or symbols.

    Is there a formula that can be used to locate and group the duplicates without being exact matches from so much data?

  46. Hello,

    I came here to look for advice on a task in Excel that could be very simple, but I am not an Excel expert. I have two columns that contain over 1,000 values in each. 90% of the values are the same in each column. I simply to look for the values that are different in each of the columns. I tried the Vlookup and I have not had any luck. Is this something the Index Match could do?

    Thanks in advance for your help.

  47. Hi,
    I want formula for its like a inventory style, I have received same item different date and different price than I want issue same price what I was received
    Example:
    Received:
    Date Description UOM Qty Rate
    01/12/2015 Boat Nail 4" Kgs 200 10

    Issue Or Sale
    Date Description UOM Qty Rate
    01/12/2015 Boat Nail 4" Kgs 20 10

    Issue Sheet Date,Description Qty, rate Placed placed automatically compare to received sheet Date,Description and Qty

  48. Hi, I'm trying to find a formula that would help me solve the following problem:

    Code Country LE#
    123 US 4026
    123 UK 4026
    123 US 3026
    435 CN 1419
    435 CN 1398

    I need to find a formula that can return lowest LE# so if I look up 123 & US it should return with the lowest LE# which is 3026

    Thanks.

    1. Hello, David,

      Please try this array formula:
      {=MIN(IF((A2:A6=123) * (B2:B6="US"), C2:C6))}

      Make sure you press CTRL + SHIFT + ENTER after entering it.

      1. Thank you!

  49. Hi,

    Is it possible to do the index match function with an isblank formula?

    The index match formula works great, but returns a '0' when the cell is blank, I would rather it returned a blank cell.

    Many thanks
    Emma

    1. Hello, Emma,

      Here's a sample formula that may work for your task:
      =IF(ISBLANK(INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))), "", INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0)))

  50. I am new to the Vlookup function, I feel I have a good grasp of it but one of my basic lookups is not working correctly. Ifyou look at test sheet, cell c24. The value is not returning correctly, there seems to be some math occurning and I cant figure out where it is. Any assistance?

    C24 - =IFERROR(VLOOKUP(A24,Sheet1!A2:B23,2),0)

    1. I was trying to post the xls but don't see an attachment option.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)