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 on: How to use MATCH function in Excel - formula examples
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 58
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?
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.
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))
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))
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?
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!
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
Hello, Stefano,
no, MATCH will not return the numbers for both row and column. But depending on your task, you can try another way - use MATCH with INDEX.