MATCH Function in Excel

=MATCH(lookup_value, lookup_array, [match_type])
↑Click any parameter above to learn what it means — it'll highlight in the live example below.
  • lookup_value: (What to look for) The exact text, number, or cell reference you are trying to find.  (See Common Criteria Patterns in MATCH below to learn how to use wildcards.) Required.
  • lookup_array: (Where to look for it) The single column or single row where Excel will look for your item. Required.
  • match_type: (Exact or Approximate) How to match. The rule Excel uses to find your item: Type a 0 to find an exact match. Type a 1 to find the largest value that is less than your item. Type a -1 to find the smallest value that is greater than your item. If you leave this completely blank, Excel defaults to 1 . Optional.

What is the MATCH function in Excel?

The Excel MATCH function searches for a specific item in a list and tells you exactly what position it is in. It gives you a number (like row 3 or column 5) instead of the actual data.

Imagine looking at a top 100 music chart. You do not want to hear the song; you just want to know its position number on the trending list. The MATCH function gives you that exact number.

Lookup & ReferenceIntermediateWorks in Excel All versions
!Golden Rules

MATCH's 4 golden rules

Reading these is a must. Skipping them is the #1 reason MATCH formulas break.

The lookup_array must be a single column (like A1:A10) or a single row (like A1:D1).
If you try to select a large box of cells (like A1:D10), the formula will fail.
If your search item appears three times in the list, the formula will only give you the position number of the very first match.
It ignores all duplicates.
The formula does not tell you the exact row number of the spreadsheet. It tells you the position inside your lookup_array.
For example, if your list starts at cell A5, and the item is in cell A5, the MATCH function gives you a 1, not a 5.
If you leave the match_type parameter blank, Excel defaults to an approximate match (1). If your data is not sorted properly, this will return completely incorrect position numbers.
Always type a 0 at the end of your formula to force an exact match.

Learn with Example: MATCH

  1. Step 1: Understand the Goal We need the formula to output a simple row number for Invoice #102 first, and then automatically find the row numbers for #104 and #100.
  2. Step 2: Start the Formula To find the row position, click the empty cell next to Invoice #102 in the Overdue Accounts Report table. Type: =MATCH(
  3. Step 3: Select the lookup_value For your lookup_value, select the cell containing the invoice number you want to find (A11). Then, type a comma. Type: =MATCH(A11, lookup_value The specific item you want to find.
  4. Step 4: Select the lookup_array For your lookup_array, select the column of Invoice IDs in the Invoice Database table (A3:A7). Then, type a comma. Type: =MATCH(A11, A3:A7, lookup_array The single column or row where Excel should search.
  5. Step 5: Set the match_type For your match_type, type a zero to force Excel to find an exact match for your invoice. Close the parentheses. Type: =MATCH(A11, A3:A7, 0) match_type 0 means exact match.
  6. Step 6: Check the First Result Press Enter. You will now see the exact row position number for Invoice #102! Result 3
  7. Step 7: Lock the Search Area If we copy this formula down right now, our lookup_array (A3:A7) will accidentally slide downward. To lock it in place, click inside A3:A7 in your formula bar and press F4 on your keyboard to add dollar signs. Change This =MATCH(A11, A3:A7, 0) To This =MATCH(A11, $A$3:$A$7, 0) Hint: Do NOT lock the lookup value! We want it to slide down to the next rows when we drag the formula.
  8. Step 8: Copy Down and Check the Final Result Double-click the small green square in the bottom right corner of your cell to instantly find the row position for the rest of your overdue invoices! Result 3 5 1

You have a massive list of unpaid invoices. Your boss gives you a specific list of 3 overdue accounts. How do you instantly find exactly which rows they are sitting in?

B11
ƒx
=MATCH(lookup_value,lookup_array,[match_type])
ABCD
1Invoice Database
2Invoice IDCompanyDue DateAmount
3#100Acme Corp08/01/2026$500
4#101TechFlow08/05/2026$1,000
5#102GlobalSync07/28/2026$250
6#103Beta Industries08/10/2026$750
7#104OmniSolutions07/15/2026$100
8
9Overdue Accounts Report
10Target InvoiceRow Position
11#102
12#104
13#100
Ready
Step-by-step guide1 / 8
Step 1: Understand the Goal

We need the formula to output a simple row number for Invoice #102 first, and then automatically find the row numbers for #104 and #100.

When to use MATCH

Here are the most common situations where you should use the MATCH formula instead of manually counting rows:

  • Finding the exact row number of an item: When you have a long list of names and need to know exactly which row contains a specific name. The formula will give you the exact row number.
  • Checking if an item is missing from a list: When you want to check if a specific invoice number is on a list. If the formula gives you a position number, the invoice is there. If it gives you an error, the invoice is missing.

The MATCH function is commonly used with the INDEX function. When you combine them, they find the actual data value you are looking for. This combination is the best alternative to the VLOOKUP function.

When not to use MATCH

Because this formula is designed to only give you a position number, you should use a different method for these specific situations:

  • When you need the actual data: If you want the formula to give you the person's phone number or email address, do not use MATCH. Instead, use the XLOOKUP or VLOOKUP function.
  • When you have a two-dimensional grid: If you need to search across multiple rows and multiple columns at the exact same time. The MATCH function can only search a single straight line (one column or one row). Instead, you must use two separate MATCH functions (one for the row and one for the column).

MATCH vs XMATCH

If you need to find the position of an item, you must choose between the older MATCH formula and the newer XMATCH formula.

FeatureMATCH FunctionXMATCH Function
Best used forFinding a position number in older versions of ExcelFinding a position number in newer versions of Excel
Defaults to an exact match?No (you must type a 0)Yes
Can search from bottom to top?NoYes
Wildcards (* or ?)Always turned onTurned off by default (safer)
Excel versionAll versionsExcel 2021 and newer
Ease of useIntermediateBeginner

Bottom line: Use the MATCH function if you are sharing your file with people who use older versions of Excel. Use the XMATCH function if you have a newer version of Excel because it is faster and easier to write.

Common Criteria Patterns in MATCH

You can use wildcards in the lookup_value if you only know part of the word you are looking for. This only works if you set the match_type to 0.

Criteria Pattern NameFormula ExampleExplanation
Begins with specific text=MATCH("App*", A1:A10, 0)Finds the position of the first word that starts with "App" (like Apple or Application).
Ends with specific text=MATCH("*son", A1:A10, 0)Finds the position of the first word that ends with "son" (like Johnson or Jackson).
Contains specific text=MATCH("*phone*", A1:A10, 0)Finds the position of the first word containing "phone" anywhere inside it.

For a complete list of patterns (including dates, numbers, and cell references), see our full Excel Criteria and Wildcards reference page.

Common Mistakes When Using MATCH

Getting the #N/A error because your lookup_value does not exactly match
This usually happens because the item in your lookup_value has hidden spaces, spelling mistakes, or is a number formatted as text. For example, if you search for the number 100, but the 100 in the lookup_array is stored as text, the formula will fail. You must ensure both cells exactly match.
Getting the #N/A error because the lookup_array is a grid
This usually happens because you selected multiple columns (like A1:C100). You must change your lookup_array to be a single column or a single row for the formula to work.
Getting the wrong position number because you forgot the match_type
This usually happens when you leave the match_type blank. When it is blank, Excel assumes the list is sorted alphabetically and makes a guess (an approximate match). You must always type a 0 at the end of the formula to force Excel to find an exact match.
Getting the wrong result because your text contains an asterisk (*)
Because the MATCH function automatically treats asterisks as wildcards, searching for a literal asterisk (like the company name "Star*bucks") will give you the wrong position number. To safely search for text that contains symbols, use the newer XMATCH function instead.
For a complete list of formula errors (like #VALUE! or #NAME?) and how to fix them, see our Common Excel Errors Troubleshooting guide.

Frequently Asked Questions about MATCH

Quick answers about MATCH

Why is it returning a 1 instead of row 14?

The function gives you the position relative to the cells you selected. If your lookup_array is A14:A20, the very first cell is A14. So if your item is in A14, the result is 1.

Can I use this function to search from right to left?

Yes. Because the MATCH function only gives you a number, it does not care if the data is on the left or the right. It simply counts down the column or across the row you select.

How do I find the second or third match?

The standard MATCH function cannot do this; it always stops at the first match it finds. You will need to use the FILTER function to find multiple results.

Do I have to use quotation marks around my search word?

Yes. If you type a word directly into the lookup_value, you must wrap it in quotation marks (like "Apple"). If you click on a cell reference (like B1), you do not use quotation marks.

Is the MATCH function case-sensitive?

No. It ignores capitalization. For example, searching for "apple" will perfectly match "APPLE".

MATCH Practice Workbook

Download ready-to-use Excel files and practice MATCH hands-on

Complete Practice File
MATCH Practice Workbook
.xlsx · 75 KB
This file contains:
  • All 5 worked examples (finance, sales, HR, ops, data), each with a master table and a report table to fill in
  • Row-position, column-position, wildcard, and approximate-match (weight tiers) scenarios
  • Blank result cells to write each MATCH yourself, plus the locked ranges set up for copy-down
  • An answer key to check every position number
Download Complete File
Reviewed by Grapte teamLast updated September 2026Tested in Microsoft Excel 365