MATCH Formula in Excel

 

What Does It Do ?

This function looks for an item in a list and shows its position.

It can be used with text and numbers.

It can look for an exact match or an approximate match.

Syntax

=MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)

The TypeOfMatch either 0, 1 or -1.

Using 0 will look for an exact match. If no match is found the #NA error will be shown.

Using 1 will look for an exact match, or the next lowest number if no exact match exists.

If there is no match or next lowest number the error #NA is shown.

The list of values being examined must be sorted for this to work correctly.

Using -1 will look for an exact match, or the next highest number if no exact match exists.

If there is no exact match or next highest number the error #NA is shown.

The list must be sorted for this to work properly.

Examples 1

Using the 0 option suitable for an exact match.

The Ascending list gives the exact match.

The Descending list gives the exact match.

The Wrong Value list cannot find an exact match, so the #NA is shown.

Example 2

Using the 1 option suitable for a ascending list to find an exact or next lowest match.

The Ascending list gives the exact match.

The Descending list gives the #NA error.

The Wrong Value list finds the next lowest number..

Example 3

Using the -1 option suitable for a descending list to find an exact or next highest match.

The Ascending list gives the #NA error.

The Descending list gives the exact match.

The Wrong Value list finds the next highest number.