How To Use Excel Match Function With Examples

The excel MATCH function is used to find a value in a cell range, it will return the position number of the search value in the cell range. After that, we can use the excel INDEX function and the position number to get the matched cell data. This article will tell you how to use the excel INDEX MATCH function together with examples.

1. How To Use Excel Match Function.

1.1 Excel Match Function Syntax.

  1. The excel MATCH function has the below syntax.
    =MATCH(lookup_value, lookup_array, [match_type])
  2. The first argument lookup_value is the keyword data (number or text) that will be searched in the second argument lookup_array.
  3. The second argument lookup_array is an array or a cell range that will be searched in.
  4. The third argument match_type is a number value.
  5. When the number is 1 it means to look up the first maximum number in the lookup_array that is less than or equal to the lookup_value, this match type needs to sort the lookup_array in ascending order.
  6. When the number is 0 it means to look up the first value in the lookup_array that is equal to the lookup_value exactly, this match type does not need to sort the lookup_array elements order.
  7. When the number is –1 it means to look up the first minimum number in the lookup_array that is greater than or equal to the lookup_value, this match type needs to sort the lookup_array in descending order.
  8. And the MATCH function will return the position number of the first matched array element or cell in the lookup_array.

1.2 Excel Match Function Examples.

  1. Below is the excel match function example data cells.
    A B C
    1 Sale Item Unit Price($)  
    2 Apple Watch 1800 =MATCH(850, B2:B7, 1)
    3 iPad 1500 =MATCH(850, B2:B7, -1)
    4 iPhone 1100 =MATCH(800, B2:B7, 0)
    5 Mac Mini 1000
    6 Macbook Air 900
    7 Macbook Pro 800
  2. Because we need to sort the above rows, so we need to convert the above cells to an excel table.
  3. You can read the article Excel Convert Table To Range Example And Vice Versa to learn how to convert cell range A1:B7 to an excel table, below is the table after converted.
1.2.1 Excel Match Function Match Type Less Than Example.
  1. Click the Down arrow on the right side of the cell Unit Price($) and sort the column B in ascending order by selecting the Sort Smallest to Largest item.
  2. Input the formula =MATCH(850, B2:B7, 1) in cell C2 and press the enter key, then it will display the number 1 in cell C2.
  3. This is because only the first cell ( contains number 800 )’s value is the biggest number which is less than the lookup_value (850).
1.2.2 Excel Match Function Match Type Greater Than Example.
  1. Click the Down arrow on the right side of the cell Unit Price($) and sort the column B in descending order by selecting the Sort Largest to Smallest item.
  2. Input the formula =MATCH(850, B2:B7, -1) in cell C3 and press the enter key, then it will display the number 5 in cell C3.
  3. This is because the fifth position cell ( contains number 900 )’s value is the smallest number which is greater than the lookup_value (850).
  4. And you can see the cell C2‘s value has been changed to #N/A, this is because cell C2 contains the formula =MATCH(850, B2:B7, 1) which set the match_type value to 1 in the formula.
  5. And when you set the match_type value to 1, it needs to sort the lookup_array (cell range B2:B7) in ascending order.
1.2.3 Excel Match Function Match Type Exact Match Example.
  1. Input the formula =MATCH(850, B2:B7, 0) in cell C4 and press the enter key, then it will display the error text #N/A in cell C4.
  2. This is because you set the match_type argument to 0 which means the exact match and the cell range B2:B7 do not contain a number 850.
  3. Change the formula to =MATCH(800, B2:B7, 0) and press the enter key, then it will display the number 6 ( the cell position in my example).
  4. The exact match type do not need to sort the lookup_array.
  5. You can use the wildcard in the exact match lookup_value, the character * will match multiple characters and the character ? will match single character.
  6. The formula =MATCH(“iPa?”, A2:A7, 0) will return the number 2 ( which cell value is iPad ).
  7. The formula =MATCH(“*mac*”, A2:A7, 0) will return the number 4 ( which cell value is Mac Mini ).
  8. Because the cell value Mac Mini is the first cell that contains the lookup_value *mac*.
  9. And the MATCH function is case insensitive.

2. How To Use Excel Index Match Function To Get The Matched Cell Value.

  1. After you use the excel MATCH function to get the lookup_value position in the lookup_array, you can use the excel INDEX function to get the searched cell value.
  2. Below is the example data table.
    A B C
    1 Sale Item Unit Price($)  
    2 Apple Watch 1800
    3 iPad 1500
    4 iPhone 1100
    5 Mac Mini 1000
    6 Macbook Air 900
    7 Macbook Pro 800
  3. If we want to search the keyword mac in Sale Item column and return the first matched cell value, we can use the formula =INDEX(A2:A7, MATCH(“*mac*”, A2:A7, 0)).
  4. The MATCH(“*mac*”, A2:A7, 0) function will return the first matched cell’s position in the cell range.
  5. The INDEX function will return the cell value in cell range using the above cell position value.

Subscribe to learn more excel tricks.

We don’t spam!

close

Subscribe to learn more excel tricks.

We don’t spam!

0 0 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x