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

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

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

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

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

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

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

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