How to Use MATCH Function in Excel? - ExcelDemy (2024)

In this article, we are going to demonstrate various examples of using the MATCH function in Excel based on different criteria, and what to do when this function doesn’t work.

How to Use MATCH Function in Excel? - ExcelDemy (1)

Introduction to MATCH Function in Excel

The MATCH function in Excel is used to locate the position of a lookup value in a row, column, or table, and returns the relative position of an item in an array that matches a specified value in a specified order.

How to Use MATCH Function in Excel? - ExcelDemy (2)

  • Syntax:


  • Arguments Explanation:
lookup_valueRequiredThe value to match in the array
lookup_arrayRequiredA range of cells or an array reference in which to lookup the value
match_typeOptionalSpecifies how Excel matches lookup_value with values in the lookup_array. 1 = exact or next smallest, 0 = exact match and -1 = exact or next largest
  • Return Value:

Returns the lookup value’s relative position.

  • Available Version:

From Excel 2003 onwards.

How to Use MATCH Function in Excel: 8 Practical Examples

To demonstrate the uses of the Match function, we’ll use the following dataset containing some “Products” with their ‘Price” and ‘Serial Numbers” tofind out the exact or approximate match for our search value.

How to Use MATCH Function in Excel? - ExcelDemy (3)

We used the Microsoft 365 version of Excel here, but the methods should work in any other version from Excel 2003 onwards.

Example 1 – Finding the Position of a Value

1.1 – Exact Match

For an exact match, simply set the matching_criteria argument to 0.


  • In cell C12, enter the following formula.

=MATCH(D11, C5:C9,0)

The lookup_value is cell D11, the lookup_array is C5:C9, and the matching_criteria is 0 for an exact same match. So this MATCH function returns the position in the lookup_array of the exact value in cell D11.

How to Use MATCH Function in Excel? - ExcelDemy (4)

1.2 – Approximate Match

In most cases, rather than an exact match, an approximate match is used for numbers.


  • Insert the below formula in cell D12:


Here, the D5:D9 cell range is the lookup_array. Since an approximate match is our target, we set 1 in the match_type field, which returns the nearest smallest value to the lookup_value. 300 is the nearest value to 335, so our formula returned the position of 3.

How to Use MATCH Function in Excel? - ExcelDemy (5)

1.3 – Specific Text Match

The MATCH function can also take text as its lookup value, as opposed to the cell reference used above. This is useful if you want to find the value or position of particular text in your dataset without knowing the cell reference.


  • Enter the following formula in cell D12:

=MATCH(“Pants”, C5:C9,0)

The formulatakes the lookup_valuePants” and searches for an exact match in the lookup_array C5:C9.

How to Use MATCH Function in Excel? - ExcelDemy (6)

1.4 – Wildcard Match

You can also match text partially and return the position of the partial match in the dataset using a wildcard. For example, to find out the position for the product “Pants“, we can use the wildcard ” Pa*” instead of the full text.


  • Enter the below formula in cell C12:

=INDEX(C5:C9, MATCH(“Pa*”, B5:B9,0))

Here, the MATCH function finds an exact match (matching_criteria = 0) in the lookup_array of B5:B9 for the lookup_value Pa*. Then the INDEX function takes the result of the MATCH function and finds the relation between the C5:C9 array and the Pa* text.

How to Use MATCH Function in Excel? - ExcelDemy (7)

Example 2 – Finding a Value Corresponding to Another Value

We can also find a value corresponding to another value by using the INDEX function along with the MATCH function. The INDEX function returns the value at a given location in a range or array. Then the MATCH function checks for the match.


  • In cell C12 insert the following formula:

=INDEX(C5:C9, MATCH(C11, B5:B9,0))

B5:B9 is the array where we need to find the value. Using the MATCH function, we set the row_number,here 2. Then, from the array B5:B9, the INDEX function returns the value in the position of row 2.

How to Use MATCH Function in Excel? - ExcelDemy (8)

Example 3 – Applying the MATCH Function in Array Formula

We also need the INDEX function to use the MATCH function in an array formula.


  • In cell C14 enter the following formula:

=INDEX(D5:D10, MATCH(1,(C12=B5:B10)*(C13=C5:C10),0))

Here we use 1 as the lookup_value in MATCH. The lookup_array is combined by multiplying the results from checking two criteria within their respective columns.

The (C12=B5:B10) and (C13=C5:C10) criteria provide an array of TRUE or FALSE. By multiplying the arrays, another array of TRUE and FALSE is formed. TRUE can be represented as 1, so we are looking for the position of the TRUE value inside the array.

Since this is an array formula, press CTRL + SHIFT + ENTER to execute it if you’re not a Microsoft 365 subscriber (where just pressing Enter will suffice).

How to Use MATCH Function in Excel? - ExcelDemy (9)

Example 4 – Utilizing Case-Sensitive MATCH Formula

For case-sensitive text, use the EXACT function and then the MATCH function to match the criteria. The structure of the formula is slightly different from that of the MATCH function formulas used in the above examples.


  • Enter the following formula in cell D12:


The EXACT(C5:C9, D11) syntax returns an exact match in the lookup_array C5:C9, and the logical argument TRUE represents the existing value from the EXACT function.

How to Use MATCH Function in Excel? - ExcelDemy (10)

But when we use a lowercase letter in the lookup_value then it returns #N/A. So this formula works accurately, because an exact, case-sensitive match is required.

How to Use MATCH Function in Excel? - ExcelDemy (11)

Example 5 – Comparing Two Columns Using ISNA and MATCH Functions

Suppose we have a dataset of 2 lists, and we want to compare the 2nd list with the 1st one and return the values that don’t appear in the first list. We can do this using the ISNA and MATCH functions, along with the IF function to display the logical result in text format.

How to Use MATCH Function in Excel? - ExcelDemy (12)


  • In cell D5 enter the following formula:

=IF(ISNA(MATCH(C5, B5:B12,0)), “Not in List 1″,””)

Here, the MATCH function in Excel returns TRUE for an exact match and FALSE otherwise. Then the ISNA function flips the results received from the MATCH function. Finally, the IF function returns the logical output as text.

How to Use MATCH Function in Excel? - ExcelDemy (13)

Example 6 – Applying the MATCH Function Between Two Columns

Suppose we have a list of products that match a previous column, and we want to put the value of “Price” that is an exact match in our new column. To do this, we need to use the INDEX and MATCH functions together.


  • In cell F5 enter the formula below:

=INDEX($C$5:$C$12, MATCH(E5,$B$5:$B$12,0))

This formula compares the text between columns B and E and returns the matching value.

How to Use MATCH Function in Excel? - ExcelDemy (14)

Example 7 – Use the MATCH Function with VLOOKUP in Excel

When using the VLOOKUP function to look up a value from a dataset, deleting or inserting any column from that dataset will break the function.

In that case, use the MATCH function with VLOOKUP to do the task. To look up the Sales value of a product, we can use the following formula:


How to Use MATCH Function in Excel? - ExcelDemy (15)

In the formula, we use the cell range B4:D9 as table_array. Now, if we delete the Quantity column, the formula will change the table_array to B4:C9 and show the result.

How to Use MATCH Function in Excel? - ExcelDemy (16)

Example 8 – Apply HLOOKUP with the MATCH Function to Lookup Values in a Horizontal Dataset

Similarly, use the HLOOKUP function to look up values in a horizontal dataset. Use the following formula to do that:


How to Use MATCH Function in Excel? - ExcelDemy (17)

Read More: Excel MATCH Function Not Working

Frequently Asked Questions

1 – Is the MATCH function better than the VLOOKUP function?

No, the VLOOKUP function is better as it returns a value rather than the position of a value like the MATCH function. However, by combining these two functions you can look for a value from a dataset, and deleting any row or column will not change the result.

2 – What does the MATCH function return if no match is found?

The error value #N/A.

3 – Can the MATCH function work with both rows and columns?

Yes. The lookup_array can be a single row or a single column.

4 – Can the MATCH function figure out the difference between uppercase and lowercase values?


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Uses of MATCH Function.xlsx

<< Go Back toExcel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
How to Use MATCH Function in Excel? - ExcelDemy (2024)


How do you use match () in Excel? ›

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

Why won't match function work in Excel? ›

If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because: The cell has unexpected characters or hidden spaces. The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.

How do you easily match data in Excel? ›

We need to use the MATCH function. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3.

What is the exact match function in Excel? ›

Compares two text strings and returns the logical value TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive. Use EXACT to test text being entered into a document.

How to compare two lists in Excel for matches? ›

If you want to compare columns with any two or more cells with the same values in the same row, then you might use the following formulas: =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "")

What is the formula for matching cells in Excel? ›

The formula to find matches in any two cells in the same row is =IF(OR(A2=B2, B2=C2, A2=C2), “Match”, “”).

What is the alternative to match function in Excel? ›

A modernized alternative to MATCH, the XMATCH function was introduced concurrently with XLOOKUP and is only available in Excel 365 and Excel 2021. Here are the key improvements: Unlike MATCH, XMATCH defaults to an exact match, which aligns with most use cases.

What is the error no match in Excel? ›

A No Match error usually means that the MATCH portion is unable to find a cell on your source sheet that exactly matches the text you have in that top cell. Based on your formula, it looks like you may have your ranges swapped around.

How do you compare two columns for matches in Excel? ›

  1. Select the first column of data.
  2. Press the Ctrl key and select the second column of data.
  3. Go to Home > Conditional formatting > New Rule > Highlight only Unique or Duplicate Values.
  4. Select Duplicate and under Format, select any colour under the Fill tab.
  5. Click on OK/Apply.
Feb 13, 2013

How do I match data in Excel with multiple criteria? ›

To perform an INDEX MATCH with multiple criteria in Excel, simply use an ampersand (&) to place multiple references in your lookup value and lookup array inputs in the MATCH formula.

How do I compare two data sets in Excel for matches? ›

Use the formula “=IF(A1=B1, “Match”,”Not a match”)" to test if the cell in A1 is the same as B1, replacing the references to match your own data. Press the “Enter” key or select another cell to apply the formula. Identify whether your cell reads “Match” or “Not a match”, depending on the data in cells A1 and B1.

How to check data in Excel if match? ›

Check if cell matches any cell in range

In Excel 2019 and lower, this should be entered as an array formula by pressing the Ctrl + Shift + Enter shortcut. If you are using Excel 2019 or lower, remember to press Ctrl + Shift + Enter to get the second OR formula to deliver the correct results.

What is the match rule in Excel? ›

The MATCH function in Excel searches for a specified value in a range of cells, and returns the relative position of that value. Lookup_value (required) - the value you want to find. It can be a numeric, text or logical value as well as a cell reference. Lookup_array (required) - the range of cells to search in.

How do you use the match function? ›

The two formulas you can use to write the function are MATCH and XMATCH, which you can type as:=MATCH(lookup_value, lookup_array, [match_type])=XMATCH(lookup_value, lookup_array, [match_type])Where: "MATCH" is the function you want Excel to perform.

Does Excel match work on text? ›

The Match function in Excel is dedicatedly designed to search for a given term, part, or text in a range of cells.

How do you use the Choose and Match function in Excel? ›

How to use the CHOOSE function in Excel
  1. Insert the CHOOSE function. Select the cell where you want the returned value to appear. ...
  2. Type your list of values. The first argument after "=CHOOSE(" is the index value, but it's often easier to first type the values you want the function to return. ...
  3. Enter the index value.
Jan 26, 2023

How do you match the first 5 characters in Excel? ›

Using the LEFT Function

Apply the formula =LEFT(text, num_chars) where text is the cell reference containing the string, and num_chars is set to 5. For example, =LEFT(A2, 5) will return the first 5 characters of the text in cell A2.

How to extract certain text from a cell in Excel? ›

Here are four methods you can use to extract a substring in Excel:
  1. Use the LEFT, RIGHT and MID functions. You can use the LEFT, RIGHT and MID functions to extract specific text from a cell. ...
  2. Use the TRIM function. ...
  3. Use the MID and FIND functions. ...
  4. Use Flash Fill.
Feb 3, 2023


Top Articles
Latest Posts
Article information

Author: Greg Kuvalis

Last Updated:

Views: 6015

Rating: 4.4 / 5 (75 voted)

Reviews: 90% of readers found this page helpful

Author information

Name: Greg Kuvalis

Birthday: 1996-12-20

Address: 53157 Trantow Inlet, Townemouth, FL 92564-0267

Phone: +68218650356656

Job: IT Representative

Hobby: Knitting, Amateur radio, Skiing, Running, Mountain biking, Slacklining, Electronics

Introduction: My name is Greg Kuvalis, I am a witty, spotless, beautiful, charming, delightful, thankful, beautiful person who loves writing and wants to share my knowledge and understanding with you.