Comments on: How to use MATCH function in Excel - formula examples

There exist many different Excel lookup functions for finding a certain value in a range of cells, and MATCH is one of them. Basically, it identifies a relative position of an item in a range of cells, however, the Excel MATCH function can do much more than its pure essence. Continue reading →

Comments page 2. Total comments: 90

  1. i need to look up for a date (exact match) in a column and the value available in the next row in that column.
    please suggest the formla

  2. I am trying to write a formula that finds the last matching data in a cell and calculate the time between the two. ex column A is dates, column B are things (A,B,C & D), column C is the amount of days between the last time "D" was entered.

    4/1/2021 C
    4/3/2021 A
    4/5/2021 D
    4/6/2021 D 1 days
    4/12/2021 C 11 days
    4/20/2021 D 14 days

    Thanks

    1. Hello!
      If I understand your task correctly, write the formula in cell C2

      =IFERROR(A2-LOOKUP(2, 1/($B$1:$B1=B2), $A$1:$A1),"")

      After that you can copy this formula down along the column.
      Please check out this article to learn how to get a value associated with the last entry in a row.

  3. Match function did help half the purpose as I needed the 'position' it last appeared.

    However, I tried and it can only be applicable to one column of appearance.
    With multiple column of appearances to how to get the last position

    For Example:

    A B C D E F
    1 1 2 3 4 5 6
    2 2 3 7 8 9 10
    3 1 3 4 8 9 10

    I used =Match (A1, A2:A3, 0) ................ yes it will return 2, which is OK.
    But I tried ...
    =Match (B1, A2:F3, 0) .............................. It will give me an error instead of 1, which is the answer.

    As the match function seems to comb vertical and single column. Can it look up both ways (vertically and horizontally across multiple columns) and still return the last row position?

    Thank you.

    1. Hello!
      To find the line number in which the last required value is located (cell I1), you can use the formula:

      =MAX(IF($A$2:$G$7=$I$1,ROW($A$2:$A$7),0))

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

      1. Your answer maybe referring to the wrong question

        1. Hi,
          Mistake. Instead of I8, read I1. The formula returns the last row number that contains the value from I1.
          If this is not what you need, explain the problem in more detail.
          Formula =Match(A1, A2:A3, 0) return error, not 2.
          =Match(B1, A2:F3, 0) return 2, not error.
          Maybe instead of Match(B1, A2:F3, 0)
          try
          =MAX(IF($A$2:$F$3=$B$1,ROW($A$2:$A$3),0))

          1. Hi Alexander,

            Thanks for the reply. The first column of numbers is actually the excel mandatory numbers

            A B C D E F
            1 1 2 3 4 5 6
            2 2 3 7 8 9 10
            3 1 3 4 8 9 10

            I used =Match (A1, A2:A3, 0) ................ yes it will return 2, which is OK.
            But I tried ...
            =Match (B1, A2:F3, 0) .............................. It will give me an error instead of 1, which is the answer. As it is the last position it last occur. 1 row down.

            I tried your formula, but still got an error. Have you tried it in excel?

            Thanks!!!!!

              1. How do i apply it? It returns an error.

            2. =IFNA((MATCH(A1,A2:A3,0)),0)&IFNA((MATCH(A1,B2:B3,0)),)&IFNA((MATCH(A1,C2:C3,0)),)&IFNA((MATCH(A1,D2:D3,0)),)&IFNA((MATCH(A1,E2:E3,0)),)&IFNA((MATCH(A1,F2:F3,0)),)

              Found the formula that i want. I wonder if there is a shorter version.

              Thanks Alexander.

              1. but the formula is wrong... is the table extends out with repetitive numbers. sigh

      2. I dont understand. In the above set data shown there are not G column, and what is cell I8?

  4. Hi
    I am trying to find the first time a specific amount or number is found (equal or higher than the value I am looking for) above or below in the same column.

    Match works for looking down in the column, but not up - unless I am missing something here.

    Any ideas?

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

      =INDEX($A$2:$A$8,MATCH(MIN(IF((($A$2:$A$8>=A2)*(ROW($A$2:$A$8)<>ROW(A2)))>0,$A$2:$A$8-A2)),IF((($A$2:$A$8>=A2)*(ROW($A$2:$A$8)<>ROW(A2)))>0,$A$2:$A$8-A2),0))

      I hope my advice will help you solve your task.

  5. Hi folks! Great article. I need help please -
    Scenario - I have 12 columns dated Jan to Dec with cumulative totals under each column. E.g.
    Jan - 5000
    Feb - 10000
    March - 20,000
    April - 45,000
    May - 90,000
    and so on.

    I have another column that provides me with a total number. E.g.
    Total Funding Amount = 50,000
    I need to create another column, called month of funding breach that can add the cumulative total from the months above by matching it to the total funding amount, and return a string i.e the month. E.g. Since total funding amount is 50,000, funding will run out in May. I need this month to be populated in the new column 'Funding Breach Month.'

    Can anyone assist please?

    1. Hello Geetha!
      Do you want to determine the month in which the running total amount is greater than the total amount of funding?
      If the total funding amount are calculated on row 30, then use the formula

      =VLOOKUP((MATCH(50000,A30:L30,1)+1), {1,"January";2,"February";3,"March";4,"April";5,"May";6,"June";7,"July"; 8,"August";9,"September";10,"October";11,"November";12,"December"},2,0)

      Hope this is what you need.

  6. I I am trying to match the first 4 digits in the column to the first 4 digits in another column and if there's a match I want the value associated with the column. My code keep saying #NA: =INDEX(Sheet1!$B$2:$B$100, MATCH(LEFT($F2,3), Sheet1!$B$2:$B$100,1))

    1. Hello Melissa!
      Your formula is looking for the first 3 (not 4) characters from cell F3 in column B. Moreover, in column B, the formula is not looking for the first 3 characters, but looking for text of 3 characters. The search failed, so an N/A error appears. I do not know what data you are using, so I can not say the cause of the error.
      Maybe this formula will work

      =INDEX($B$2:$B$100, MATCH(LEFT($F2,3), LEFT($B$2:$B$100,3),0))

  7. Great explanation! Thank you.

  8. Hi
    Is there a way to list all matches for one search criteria? For example, if on sheet 1 I have Column A - date, Col B - number, Col C - name.

    Is there a way on Sheet 2, to have a search criteria where the name is entered in D1 (for example), then a formula next to it that lists all the sheet 1 (Column B) numbers that are found each time the name in sheet 2 D1 is matched in sheet 1 Col C?

  9. I have a table that has values being returned by formulas. When I tried MATCH to find the location of a number, it looks for the value in the formulas and can't find anything. Is there a way to tell MATCH to look up in the values, not the formulas? Thanks.

  10. I need to search a data base of guest names who stay at the Hotel in 2018 compared to 2019. how do I search so Excel alerts me to the same name or mobile number appearing twice.
    Thank you

  11. I have a large workbook with multiple tabs that all feed each other. Everything that is being fed into other sheets is currently done by linking to individual cells, which means that sorting does not work well. I'm looking for a solution that would allow me to use a unique identifier for each line item, feed info between sheets, and allow the individual sheets to all be sorted without affecting the data presented. Is this possible with INDEX/MATCH?

  12. I am trying to find a better formula to look for a value using index and match. could you please help me to figure out the problem in the below formula. I am not getting the result. the result is "#N/A"
    1. {=INDEX(BQ:BQ,MATCH(1,(VLOOKUP(LEFT(BF2,4),BL:BL,1,FALSE))*(BO:BO=D3),0))}
    2. {=INDEX(BQ:BQ,MATCH(1,BL:BL=OR(BD2,BE2,BF2))*(BO:BO=D4),0)}
    3. {=INDEX(BQ:BQ,MATCH(1,BL:BL=BH2&"*")*(BO:BO=D5),0)}
    whereas below formula with single cell reference works fine..
    4. {=INDEX(BQ:BQ,MATCH(1,(BL:BL=BC2)*(BO:BO=D6),0))}
    Please help.

  13. I am trying to run an index that will look for a specific name in four different columns and if that name appears in any of those columns (or all of them), give me the info that is in the first column.

    To do this, the match doesn't seem to work. Is there an or component that can used?

  14. I don't seem be able to use match when the values in the row being searched for a match are in TIME format. I'm trying to find the element that equals "11:38 PM", which is there, but my match formula returns #N/A, I think for a reason related to this. =MATCH("11:38 PM",A2:A12,0). That range does contain the time value. It must be something about indicating the value being sought.

  15. if the same values are there in the both the values then i have to give condition like as "name" so please explain how to do it?

    thanks
    ramesh

  16. I have a slightly different problem which doesn't seem to be solved by Match because Match will only return the first value. I have a number of surveys done by a number of people on a series of days. Some people will have done the survey more than twice. How can I determine the date difference between each survey and the preceding one, please? When I apply match, index and datedif, only the difference between the latest and the first survey dates is returned. Any suggestions?

    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.

  17. I have a problem where I need to extract a list of dates between 2 given dates from a vertical array of dates in ascending order. Is this possible using the index match functionality without resorting to VB. I'd rather avoid have blank entries at the top of the generated list too.

    1. Hello, Chris,

      supposing that you have the dates in A1:A24, the first given date - in B1 and the second - in B2, you can try the formula similar to the one below, but please keep in mind that it's an array formula and it should be entered by pressing Ctrl+Shift+Enter on your keyboard:

      {=IF(SUM(($A$1:$A$24*(A1>$B$1))*($A$1:$A$24*(A1<$B$2)))=0,"",A1)}

      I kindly ask you to alter it according to the way your data is stored.

      If you still have some problems or the formula doesn't work, please send us the workbook with your data to support@ablebits.com . Don't forget to link this comment in your email.
      We'll look into your task and try to help.

  18. Unless I am misunderstanding, I think the first example is wrongly explained. Match finds Laura in position 4 because she is the fourth item in the range being searched, not because that is her score ranking as stated. As it happens, she is fifth highest score, not fourth, after Rachael(1st with 287), Christian (2nd with 280), Brian (3rd with 274), Neal (4th - 240). But that's just chance as Match is not looking at the scores, only the names (the A2-A8 range). There is no "offset" to tell it to look at the scores.
    In effect, unless you wanted to know where a student's name was positioned in a random list of names, I don't believe this example tells you anything else by itself?

    1. Hi Paul,

      That is a good catch! Yes, Match returns Laura's position in the range being searched. And because the scores are sorted from largest to smallest, it is also her position among other students.

      For some reason, in my original screenshot, the highest score (Rachael's) appeared at the bottom of the list. Not sure how that could happen, maybe I added it after sorting the Score column. Anyway, I've resorted the column and updated the screenshot. Now, the Match formula returns the 5th position, exactly as it should. I've also added this explanation to the post to make the example clearer.

      Thank you again for your feedback!

  19. 7/13/2017 1301311 ESI NDT 0936 PIT 2A - NMY 2A SARNO, L. #VALUE! 07/13/2017 A001301311 NTX 9035

  20. by colum 1264012 - A0001264012 how to match - 0

  21. by colum 1264012 - A0001264012

  22. Wow!!

  23. Hello,
    I understand all of this very well, when lookup_array is a 1-dimensional array.
    But, what if lookup_array is a 2-dimensional array?
    Will MATCH return a couple of numbers, representing row and columns?
    Thank you,
    stefano

  24. Thankful! to you.
    Very useful and perfect.

  25. Useful, very detailed explanation, thanks a lot!

  26. Really impressive; I'm really curious about the huge world of excel formulas. I hope I'll learn some of them. You are doing a great job with writing blogs like this.. everyone who wants to learn excel (especially excel formulas) will be thankful for this.

  27. Brilliant work- Am sharing this link to everyone.

  28. Your are good. Your presentation is so awesome. Thanks a lot.

  29. i have table with daily entry
    c column to add 1 when A1 is 14 or less, in same time if A1 more than 14
    d column give 1
    the problem is A1 is increasing every day and i don't want column C to give me zero when A1 more than 14
    thank you for help.

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