You often need to locate the position of a specific value within a list or table in Excel. Searching manually is slow and error-prone for large datasets. The MATCH function provides a precise, formula-based method to find the row number where your lookup value first appears. This article explains how to use MATCH to get the row position for any value you need to find.
Key Takeaways: Using the Excel MATCH Function
- MATCH(lookup_value, lookup_array, [match_type]): This is the core function syntax that returns the relative position of an item in a range.
- Exact match with 0: Setting the match_type argument to 0 finds the first occurrence of an exact lookup value.
- INDEX and MATCH combination: Use the row number from MATCH as the row argument in INDEX to retrieve a value from another column.
What the Excel MATCH Function Does
The MATCH function searches for a specified item within a one-dimensional range or array. It returns the relative position of that item as a number. For example, if your lookup value is in the 5th cell of the range you searched, MATCH returns 5. This position number is most useful when combined with other functions like INDEX to retrieve related data.
The function has three arguments. The first is the lookup_value, which is the text, number, or logical value you want to find. The second is the lookup_array, which is the single row or column of cells you want to search. The third, optional argument is match_type, which controls the search logic. Using 0 for an exact match is the most common method for finding a specific value’s row.
Understanding the Match Type Argument
The match_type argument is critical for correct results. A value of 1 finds the largest value less than or equal to the lookup_value, but the lookup_array must be sorted in ascending order. A value of -1 finds the smallest value greater than or equal to the lookup_value, requiring the array to be sorted in descending order. For finding the exact row where a specific value appears, you must use 0. This setting does not require the data to be sorted and will return #N/A if the value is not found.
Steps to Find a Row Number with MATCH
Follow these steps to use the MATCH function for an exact lookup and return a row number.
- Identify your lookup value and search range
Decide on the specific value you need to find. Select the single column of data where Excel should search for this value. The MATCH function will only look within this one range. - Enter the MATCH function formula
Click on the cell where you want the row number to appear. Type an equals sign to start the formula. Then type MATCH( to begin the function. Your formula bar will now show the start of the function. - Input the function arguments
For the first argument, click on the cell containing your lookup value or type the value directly in quotes, like “ProductA”. Type a comma. For the second argument, select the single-column range where you want to search. Type another comma. For the final argument, type 0 to specify an exact match. Close the formula with a parenthesis. - Complete and evaluate the formula
Press the Enter key. Excel will calculate the formula. The cell will now display a number. This number is the relative position of your lookup value within the range you specified. If the value is in the 8th cell of your selected range, the result will be 8. - Adjust for the worksheet row number
The result from MATCH is the position within your selected lookup_array, not the worksheet row. To get the actual worksheet row number, add the starting row of your range minus one. If your search range is A10:A100 and MATCH returns 5, the value is in row 14 on the sheet (10 + 5 – 1).
Common Mistakes and Limitations to Avoid
MATCH Returns #N/A Error
The #N/A error means Excel cannot find your lookup_value in the specified range. First, verify you used match_type 0 for an exact search. Check for extra spaces in either the lookup value or the cells in your range. Use the TRIM function to remove them. Ensure the data types match; a text string like “123” is different from the number 123.
Incorrect Result with Wrong Match Type
If you omit the match_type argument, Excel defaults to 1. This assumes your data is sorted in ascending order and may return an incorrect position if it is not. Always explicitly use 0 as the third argument when you need an exact match, especially with unsorted data.
Searching in a Multi-Column Range
MATCH is designed to search only a single row or a single column. If you select a multi-column range like A1:C10 for the lookup_array, MATCH will only search the first column of that range (A1:A10). To search across a row, your lookup_array must be a single row, like B2:F2.
MATCH Function Arguments Comparison
| Item | Exact Match (0) | Approximate Match (1) |
|---|---|---|
| Match Type Argument | 0 | 1 or omitted |
| Data Sorting Requirement | Not required | Must be sorted ascending |
| Search Logic | Finds first exact value | Finds largest value ≤ lookup_value |
| Result if Value Not Found | #N/A error | Position of nearest smaller value |
| Common Use Case | Finding a specific ID or name | Looking up tax rates or score grades |
You can now use the MATCH function to quickly find the row position of any value in your Excel data. Combine this result with the INDEX function to pull related information from another column on the same row. For a more advanced application, nest MATCH inside the row argument of a VLOOKUP to create a two-way lookup. Remember that MATCH is not case-sensitive; to perform a case-sensitive search, you will need to use the EXACT function within an array formula.