---
title: "MATCH function in Excel: syntax and examples | ExcelSavvy"
description: "How to use MATCH in Excel and Google Sheets: find the position of a value, and combine it with INDEX."
canonical_url: "https://excelsavvy.com/functions/match"
meta:
  "og:description": "How to use MATCH in Excel and Google Sheets: find the position of a value, and combine it with INDEX."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~TUFUQ0ggZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBNQVRDSCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogZmluZCB0aGUgcG9zaXRpb24gb2YgYSB2YWx1ZSwgYW5kIGNvbWJpbmUgaXQgd2l0aCBJTkRFWC4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TUFUQ0gobG9va3VwX3ZhbHVlLCBsb29rdXBfYXJyYXksIFttYXRjaF90eXBlXSk%2Cp_Ii9mdW5jdGlvbnMvbWF0Y2gi%2Cs_ilPzlc7-ENQBp8jQ.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "MATCH function in Excel: syntax and examples | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~TUFUQ0ggZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBNQVRDSCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogZmluZCB0aGUgcG9zaXRpb24gb2YgYSB2YWx1ZSwgYW5kIGNvbWJpbmUgaXQgd2l0aCBJTkRFWC4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TUFUQ0gobG9va3VwX3ZhbHVlLCBsb29rdXBfYXJyYXksIFttYXRjaF90eXBlXSk%2Cp_Ii9mdW5jdGlvbnMvbWF0Y2gi%2Cs_ilPzlc7-ENQBp8jQ.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **The MATCH function**

Returns the position of a value in a row or column. Use 0 as match\_type for an exact match.

=

MATCH(

## **How to use MATCH**

MATCH answers the question "which position?" Give it a value and a range, and it returns where the value sits in that range, counting from 1. You do not get the value back, only its place, like learning that a name is fifth on the guest list.

On its own, a position is good for checks. `**=ISNUMBER(MATCH(A2,F2:F20,0))**` tells you whether an invoice number appears on another list at all. Its main job is to feed INDEX: MATCH finds the row, INDEX fetches the value from that row, and together they look up anything in any direction, including to the left, as in `**=INDEX(A2:A20,MATCH(F2,C2:C20,0))**`.

Nearly always, finish with 0 for an exact match. The default of 1 expects data sorted from small to large and returns the last value that is less than or equal to yours, which fits price tiers but not customer IDs. MATCH reads only a single row or column. With 0, text lookups accept wildcards, so `**=MATCH("Ber*",A2:A20,0)**` finds the first name that starts with Ber. The newer XMATCH is exact by default.

## **Syntax**

```
MATCH(lookup_value, lookup_array, [match_type])
```

<dl><dt>**lookup_value **</dt>
<dd>The value to find.</dd>
<dt>**lookup_array **</dt>
<dd>A single row or column to search.</dd>
<dt>**match_type **(optional)</dt>
<dd>0 exact match; 1 (default) largest value less than or equal, in ascending data; -1 smallest value greater than or equal, in descending data.</dd></dl>

## **Examples**

D1`=MATCH(<sup>1</sup>"Pear",<sup>2</sup>A2:A4,<sup>3</sup>0)`

|  | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | Fruit | Price |  | **2** |
| 2 | Apple | 4 |  | |
| 3 | Pear | 6 |  | |
| 4 | Plum | 5 |  | |

**In plain words:** Find the position of <sup>1</sup>Pear in <sup>2</sup>the fruit list in A2:A4, <sup>3</sup>only on an exact match.

D1=MATCH(5,B2:B4,0)

|  | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | Fruit | Price |  | **3** |
| 2 | Apple | 4 |  | |
| 3 | Pear | 6 |  | |
| 4 | Plum | 5 |  | |

## **Common MATCH mistakes**

- MATCH returns a wrong position or #N/A in an unsorted list

  The last argument is missing, so the default of 1 applies, and that needs ascending data. Add 0 at the end: `**=MATCH(F2,A2:A20,0)**`.
- MATCH returns #N/A even though the value is in the list

  The values only look the same. A trailing space or a number stored as text is the usual cause. Clean the value with `**=MATCH(TRIM(F2),A2:A20,0)**`, or use `**VALUE(F2)**` when the list holds real numbers.
- INDEX and MATCH return the row next to the one I wanted

  The two ranges do not start in the same row. If the INDEX range starts in row 2, the MATCH range must too: `**=INDEX(C2:C20,MATCH(F2,A2:A20,0))**`. Including the header row in only one of them shifts the position by one.
- MATCH returns #N/A when I select a whole table

  MATCH reads only one row or one column. Point it at the single column that holds the keys, such as `**A2:A20**`, not `**A2:C20**`.

## **Practice MATCH**

- [**1****Find a position with MATCH**→](https://excelsavvy.com/practice/index-match/match-function)
- [**2****Look up a price with INDEX and MATCH**→](https://excelsavvy.com/practice/index-match/index-match)
- [**3****Look to the left with INDEX and MATCH**→](https://excelsavvy.com/practice/index-match/index-match-left-lookup)
- [**4****A two-way lookup with INDEX and two MATCHes**→](https://excelsavvy.com/practice/index-match/index-match-match-two-way)

## **Read the guide**

- [**VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use**](https://excelsavvy.com/learn/vlookup-vs-xlookup-vs-index-match)
- [**Excel inventory data: free practice file with tasks and answers**](https://excelsavvy.com/learn/excel-inventory-data)

## **Questions**

<details>

<summary>

### What is the difference between 0, 1 and -1 in MATCH?

</summary>

0 finds an exact match and works on any order. 1, the default, finds the largest value that is less than or equal to yours and needs ascending data. -1 finds the smallest value that is greater than or equal to yours and needs descending data.

</details>

<details>

<summary>

### How do I use INDEX and MATCH together?

</summary>

MATCH finds the row number and INDEX returns the value from that row. =INDEX(C2:C20,MATCH(F2,A2:A20,0)) looks up F2 in column A and returns the matching value from column C.

</details>

<details>

<summary>

### How do I check if a value is in a list with MATCH?

</summary>

Wrap MATCH in ISNUMBER, as in =ISNUMBER(MATCH(A2,F2:F20,0)). It returns TRUE when the value is on the list and FALSE when it is not.

</details>

<details>

<summary>

### What does MATCH return when nothing is found?

</summary>

It returns #N/A. To show something else, wrap it in IFNA, as in =IFNA(MATCH(A2,F2:F20,0),0).

</details>

## **Related functions**

- [**INDEX**](https://excelsavvy.com/functions/index)
- [**XMATCH**](https://excelsavvy.com/functions/xmatch)
- [**XLOOKUP**](https://excelsavvy.com/functions/xlookup)

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/functions/match#webpage","dateModified":"2026-11-16","description":"How to use MATCH in Excel and Google Sheets: find the position of a value, and combine it with INDEX.","name":"MATCH function in Excel: syntax and examples","url":"https://excelsavvy.com/functions/match","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/functions/match#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/functions/match#/schema/question/1"},{"@id":"https://excelsavvy.com/functions/match#/schema/question/2"},{"@id":"https://excelsavvy.com/functions/match#/schema/question/3"},{"@id":"https://excelsavvy.com/functions/match#/schema/question/4"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/functions/match"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/functions/match#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://excelsavvy.com/functions","position":2},{"@type":"ListItem","name":"MATCH function in Excel: syntax and examples","position":3}]},{"@id":"https://excelsavvy.com/functions/match#article","dateModified":"2026-11-16","description":"How to use MATCH in Excel and Google Sheets: find the position of a value, and combine it with INDEX.","headline":"MATCH function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~TUFUQ0ggZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBNQVRDSCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogZmluZCB0aGUgcG9zaXRpb24gb2YgYSB2YWx1ZSwgYW5kIGNvbWJpbmUgaXQgd2l0aCBJTkRFWC4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TUFUQ0gobG9va3VwX3ZhbHVlLCBsb29rdXBfYXJyYXksIFttYXRjaF90eXBlXSk%2Cp_Ii9mdW5jdGlvbnMvbWF0Y2gi%2Cs_ilPzlc7-ENQBp8jQ.png","@type":["Article","TechArticle"],"author":{"@id":"https://excelsavvy.com/#identity"},"image":{"@id":"https://excelsavvy.com/#/schema/image/1"},"isPartOf":{"@id":"https://excelsavvy.com/functions/match#webpage"},"mainEntityOfPage":{"@id":"https://excelsavvy.com/functions/match#webpage"},"publisher":{"@id":"https://excelsavvy.com/#identity"}},{"@id":"https://excelsavvy.com/functions/match#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"What is the difference between 0, 1 and -1 in MATCH?","acceptedAnswer":{"@type":"Answer","text":"0 finds an exact match and works on any order. 1, the default, finds the largest value that is less than or equal to yours and needs ascending data. -1 finds the smallest value that is greater than or equal to yours and needs descending data."}},{"@id":"https://excelsavvy.com/functions/match#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I use INDEX and MATCH together?","acceptedAnswer":{"@type":"Answer","text":"MATCH finds the row number and INDEX returns the value from that row. =INDEX(C2:C20,MATCH(F2,A2:A20,0)) looks up F2 in column A and returns the matching value from column C."}},{"@id":"https://excelsavvy.com/functions/match#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How do I check if a value is in a list with MATCH?","acceptedAnswer":{"@type":"Answer","text":"Wrap MATCH in ISNUMBER, as in =ISNUMBER(MATCH(A2,F2:F20,0)). It returns TRUE when the value is on the list and FALSE when it is not."}},{"@id":"https://excelsavvy.com/functions/match#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"What does MATCH return when nothing is found?","acceptedAnswer":{"@type":"Answer","text":"It returns #N/A. To show something else, wrap it in IFNA, as in =IFNA(MATCH(A2,F2:F20,0),0)."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"},{"@id":"https://excelsavvy.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~TUFUQ0ggZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBNQVRDSCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogZmluZCB0aGUgcG9zaXRpb24gb2YgYSB2YWx1ZSwgYW5kIGNvbWJpbmUgaXQgd2l0aCBJTkRFWC4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TUFUQ0gobG9va3VwX3ZhbHVlLCBsb29rdXBfYXJyYXksIFttYXRjaF90eXBlXSk%2Cp_Ii9mdW5jdGlvbnMvbWF0Y2gi%2Cs_ilPzlc7-ENQBp8jQ.png","inLanguage":"en-US","url":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~TUFUQ0ggZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBNQVRDSCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogZmluZCB0aGUgcG9zaXRpb24gb2YgYSB2YWx1ZSwgYW5kIGNvbWJpbmUgaXQgd2l0aCBJTkRFWC4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~TUFUQ0gobG9va3VwX3ZhbHVlLCBsb29rdXBfYXJyYXksIFttYXRjaF90eXBlXSk%2Cp_Ii9mdW5jdGlvbnMvbWF0Y2gi%2Cs_ilPzlc7-ENQBp8jQ.png"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
