Find a Position in Excel with MATCH and XMATCH Formulas

To find where a value sits within a list, use MATCH or XMATCH. If the value to find is in E2 and the list is in A2:A10, enter =MATCH(E2,A2:A10,0) to return its position within that range; use =XMATCH(E2,A2:A10) in Microsoft 365 for an exact-match alternative. These functions return a position, not automatically the worksheet row number. If you mean locating a value visually, press Ctrl+F on Windows and search for it.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows.
A range containing the values to search, such as A2:A10, and the value you want to find. You can type the value directly in a formula or put it in a separate cell such as E2.
For XMATCH, Excel for Microsoft 365 or Excel 2021. MATCH is available in older Excel versions too.

The examples assume a vertical list, but the same functions work with a horizontal range. Keep the search range to one row or one column so the result is easy to interpret. A position of 1 means the first cell in the range, not necessarily row 1 of the worksheet.

Step-by-Step

Identify the list and lookup value. Note the exact range to search, including the first and last cells. For example, suppose A2:A10 contains product names and E2 contains the product name you want to locate. The example range has nine cells: its first position is A2, its second is A3, and so on. Avoid including a heading unless you intend to search it too.
Choose a cell for the answer. Select an empty cell outside the list, such as F2. Keeping the result separate from the data makes it easier to read and prevents a formula from overwriting a value you need. If you plan to copy the formula down for several lookup values, choose a result column beside those values.
Enter MATCH for a position within the range. With the lookup value in E2 and the list in A2:A10, type this formula in F2: =MATCH(E2,A2:A10,0). The final argument, 0, requests an exact match. Press Enter. If the value in E2 is found at A5, the result is 4 because A5 is the fourth cell in A2:A10. The result is not 5, even though the matching cell is on worksheet row 5.
Or use XMATCH in a supported version. With the same layout—lookup value in E2 and list in A2:A10—enter =XMATCH(E2,A2:A10). XMATCH uses exact matching by default, so this returns 4 when the matching item is A5. It is often a convenient choice in Microsoft 365 because its default is exact, while MATCH needs the explicit 0 argument for exact matching. XMATCH is available in Microsoft 365 and Excel 2021.
Check what the returned number represents. MATCH and XMATCH report the item’s ordinal position relative to the range’s first cell. If the range is A2:A10 and the result is 4, the item is at A5. If the range starts at A1, a result of 4 points to A4. For a horizontal range such as B3:G3, a result of 4 points to the fourth cell in that range, E3.
Calculate the worksheet row number if that is what you need. With the lookup value in E2 and the list in A2:A10, enter =MATCH(E2,A2:A10,0)+ROW(A2)-1. If the match is in A5, the formula returns 5, the actual worksheet row. The ROW(A2)-1 adjustment accounts for the range starting on row 2. This formula gives a row number for a vertical list; it is not a cell address.
Find the cell itself using Excel’s search tool. If you want to jump to a matching cell rather than calculate its position, press Ctrl+F on Windows, type the value, and select Find Next. In the Find dialog, choose Options if you need to set where Excel searches, such as within the current sheet or the whole workbook, or whether it searches formulas or displayed values. Select Find All to list every match; the Location column identifies each cell. On Mac, use Command+F to open Find.
Test the result against the list. For a small range, count from the top cell to the match and confirm the number agrees with the formula. If you calculated a worksheet row, use the row headings to check it. A quick manual check helps catch an accidentally included heading, a shifted range, or a lookup value that differs by a space.
Copy the formula carefully when looking up multiple values. If each lookup value is in E2, E3, and E4, a formula in F2 can be filled down so the lookup reference changes to E3 and E4. To keep the list fixed while copying, select the range reference in the formula bar and press F4 on Windows to add dollar signs, producing =MATCH(E2,$A$2:$A$10,0). On many Mac keyboards, use Command+T to cycle reference styles; keyboard behavior can depend on macOS and function-key settings. You can also type the dollar signs yourself.

Use the function result that answers the question you actually have. A relative position helps with list logic or a later lookup; a worksheet row number helps identify where a record is; Find is quickest when you only want to navigate to the cell. These are related tasks, but their results are not interchangeable.

Common Mistakes

Leaving out MATCH’s exact-match argument. MATCH defaults to approximate matching when its third argument is omitted. That can produce a misleading result, especially if the list is not sorted as required for approximate matching. For an ordinary exact search, use 0 as the third argument, as in =MATCH(E2,A2:A10,0).
Confusing a range position with a worksheet row. A result of 4 means fourth within the selected range. It does not always mean row 4. Account for the range’s starting row if you need the worksheet row, or use Find to navigate directly.
Searching a range that does not include the value. Check the start and end references. A list that extends to A11 will not be fully searched by a formula that ends at A10. Also make sure the search range is one-dimensional rather than an unintended block of rows and columns.
Expecting every duplicate to be listed by one formula. MATCH and the default XMATCH search return the position of the first match. If the same value appears more than once, that result does not indicate all its locations. Use Find All to see multiple matches, or narrow the search range if you want a particular occurrence.
Overlooking spaces or different data types. Text that looks identical may contain a leading or trailing space. A number stored as text may also behave differently from a numeric value. Check the source cells and, if appropriate, clean or standardize the data before searching.

Tips

Put lookup values in their own cells instead of embedding them in formulas. Then you can change the search term without editing the formula, and you can copy the setup for other searches.
Use an absolute list reference when filling a formula down. The dollar signs in $A$2:$A$10 hold the search range steady, while E2 remains relative and changes for each lookup row.
For a very long list, convert the data into an Excel table using Insert > Table. Structured references can make formulas easier to understand and can expand as rows are added. Check that the lookup column contains the records you intend to search.
If you need to search from the bottom upward and there are duplicates, XMATCH can reverse the search. With the lookup value in E2 and the list in A2:A10, =XMATCH(E2,A2:A10,0,-1) searches from the last item toward the first and returns the position of the last matching occurrence.
Keep the range and the lookup value consistent. If one contains numbers and the other contains text that looks like a number, consider correcting the underlying data rather than repeatedly changing formulas.

Troubleshooting

The formula returns #N/A. Excel did not find an exact match in the specified range. Check spelling, spaces, punctuation, and whether the lookup value is actually inside the range. Confirm that the formula uses the intended sheet and cells. If you copied the formula, inspect the references to make sure the search range did not move.

The result is a number, but it points to the wrong row. The formula probably returned a range-relative position, not a worksheet row. For A2:A10, add the starting-row adjustment using the row-number formula in the steps, or use Find to jump to the matching cell. Verify that the range begins where you think it does.

MATCH returns an unexpected position. Confirm that its third argument is 0 for an exact match. Without 0 or 1, MATCH uses approximate matching and expects sorted data. Also inspect duplicates: an exact search returns the first occurrence. If you need the last occurrence and have XMATCH, use the reverse-search example in Tips.

XMATCH shows a function-name error. Your Excel version may not include XMATCH. It is available in Microsoft 365 and Excel 2021; use MATCH with 0 for an exact search in older versions. If you are in Excel for the web, XMATCH is available with a supported Microsoft 365 account, though the ribbon and keyboard behavior may differ from Windows desktop.

Ctrl+F does not find the value you see. Open Find > Options and check whether Excel is searching the current sheet or the entire workbook, and whether the search target is formulas or values. Also check the selected range: Excel may restrict the search to selected cells. Clear the selection or set the search scope as needed, then search again. On Mac, open Find with Command+F.

Frequently Asked Questions

Does MATCH return the cell address?
MATCH returns a number showing the value’s position within the range. For example, 4 means the fourth cell of the chosen range. It does not return a full address such as A5. Use Find to jump to a cell, or translate the relative position into a worksheet row when working with a vertical range.

What is the difference between MATCH and XMATCH?
Both can return a value’s position. MATCH is compatible with older Excel versions and requires 0 as its third argument for an exact match. XMATCH, available in Microsoft 365 and Excel 2021, defaults to exact matching and offers options such as searching from last to first.

How do I find the second occurrence of a value?
The standard MATCH formula returns the first exact occurrence. XMATCH can search from the bottom to return the last occurrence, but that does not specifically mean the second occurrence when there are many duplicates. To inspect all occurrences, use Ctrl+F and Find All on Windows, or Command+F and Find All on Mac.

Can I find a position in a row instead of a column?
Yes. Use a single horizontal range, such as B3:G3, as the lookup range. The result is the position from left to right within that range. A result of 4 means the fourth cell, E3, not worksheet column 4.

Can I search for a partial text match?
Yes, MATCH supports wildcard characters when its match type is 0. With the search text in E2 and the list in A2:A10, =MATCH("*"&E2&"*",A2:A10,0) finds the first cell containing that text. The asterisks represent any number of characters. This returns the first matching position, so check for duplicates if several entries contain the same text.

Why does Find show several locations?
The searched value appears more than once in the scope you selected. Find All displays the matches and their locations. Check whether Excel is searching the active sheet or the whole workbook, then choose the specific result you want to visit.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.