*This tutorial explains how to use MATCH function in Excel with formula examples. It also shows how to improve your lookup formulas by a making dynamic formula with VLOOKUP and MATCH.*

In Microsoft Excel, there are many different lookup/reference functions that can help you find a certain value in a range of cells, and MATCH is one of them. Basically, it identifies a relative position of an item in a range of cells. However, the MATCH function can do much more than its pure essence.

## Excel MATCH function - syntax and uses

The MATCH function in Excel searches for a specified value in a range of cells, and returns the relative position of that value.

The syntax for the MATCH function is as follows:

**Lookup_value** (required) - the value you want to find. It can be a numeric, text or logical value as well as a cell reference.

**Lookup_array** (required) - the range of cells to search in.

**Match_type **(optional) - defines the match type. It can be one of these values: 1, 0, -1. The match_type argument set to 0 returns only the exact match, while the other two types allow for approximate match.

- 1 or omitted (default) - approximate match (closest smaller). Finds the largest value in the lookup array that is less than or equal to the lookup value. Requires sorting the lookup array in ascending order, from smallest to largest or from A to Z.
- 0 - exact match. Finds the first value in the array that is exactly equal to the lookup value. No sorting is required.
- -1 - approximate match (closest larger). Finds the smallest value that is greater than or equal to the lookup value. The lookup array should be sorted in descending order, from largest to smallest or from Z to A.

To better understand the MATCH function, let's make a simple formula based on this data: students names in column A and their exam scores in column B, sorted from largest to smallest. To find out where a specific student (say, *Laura*) stands among others, use this simple formula:

`=MATCH("Laura", A2:A8, 0)`

Optionally, you can put the lookup value in some cell (E1 in this example) and reference that cell in your Excel Match formula:

`=MATCH(E1, A2:A8, 0)`

As you see in the screenshot above, the student names are entered in an arbitrary order, and therefore we set the *match_type* argument to 0 (exact match), because only this match type does not require sorting values in the lookup array. Technically, the Match formula returns the relative position of Laura in the range. But because the scores are sorted from largest to smallest, it also tells us that Laura has the 5^{th} best score among all students.

Tip. In Excel 365 and Excel 2021, you can use the XMATCH function, which is a modern and more powerful successor of MATCH.

### 4 things you should know about MATCH function

As you have just seen, using MATCH in Excel is easy. However, as is the case with nearly any other function, there are a few specificities that you should be aware of:

- The MATCH function returns the
**relative position**of the lookup value in the array, not the value itself. - MATCH is
**case-insensitive**, meaning it does not distinguish between lowercase and uppercase characters when dealing with text values. - If the lookup array contains several occurrences of the lookup value, the position of the first value is returned.
- If the lookup value is not found in the lookup array, the #N/A error is returned.

## How to use MATCH in Excel - formula examples

Now that you know the basic uses of the Excel MATCH function, let's discuss a few more formula examples that go beyond the basics.

### Partial match with wildcards

Like many other functions, MATCH understands the following wildcard characters:

- Question mark (?) - replaces any single character
- Asterisk (*) - replaces any sequence of characters

Note. Wildcards can only be used in Match formulas with *match_type* set to 0.

A Match formula with wildcards comes useful in situations when you want to match not the entire text string, but only some characters or some part of the string. To illustrate the point, consider the following example.

Supposing you have a list of regional resellers and their sales figures for the past month. You want to find a relative position of a certain reseller in the list (sorted by the Sales amounts in descending order) but you cannot remember his name exactly, though you do remember a few first characters.

Assuming the reseller names are in the range A2:A11, and you are searching for the name that begins with "car", the formula goes as follows:

`=MATCH("car*", A2:A11,0)`

To make our Match formula more versatile, you can type the lookup value in some cell (E1 in this example), and concatenate that cell with the wildcard character, like this:

`=MATCH(E1&"*", A2:A11,0)`

As shown in the screenshot below, the formula returns 2, which is the position of "Carter":

To replace just one character in the lookup value, use the "?" wildcard operator, like this:

`=MATCH("ba?er", A2:A11,0)`

The above formula will match the name "*Baker*" and rerun its relative position, which is 5.

### Case-sensitive MATCH formula

As mentioned in the beginning of this tutorial, the MATCH function doesn't distinguish uppercase and lowercase characters. To make a case-sensitive Match formula, use MATCH in combination with the EXACT function that compares cells exactly, including the character case.

Here's the generic case-sensitive formula to match data:

*lookup array*,

*lookup value*), 0)

The formula works with the following logic:

- The EXACT function compares the lookup value with each element of the lookup array. If the compared cells are exactly equal, the function returns TRUE, FALSE otherwise.
- And then, the MATCH function compares TRUE (which is its
*lookup_value*) with each value in the array returned by EXACT, and returns the position of the first match.

Please bear in mind that it's an array formula that requires pressing Ctrl + Shift + Enter to be completed correctly.

Assuming your lookup value is in cell E1 and the lookup array is A2:A9, the formula is as follows:

`=MATCH(TRUE, EXACT(A2:A9,E1),0)`

The following screenshot shows the case-sensitive Match formula in Excel:

### Compare 2 columns for matches and differences (ISNA MATCH)

Checking two lists for matches and differences is one of the most common tasks in Excel, and it can be done in a variety of ways. An ISNA/MATCH formula is one of them:

*1st value in List1*,

*List2*, 0)), "Not in List 1", "")

For any value of List 2 that is not present in List 1, the formula returns "*Not in List 1*". And here's how:

- The MATCH function searches for a value from List 1 within List 2. If a value is found, it returns its relative position, #N/A error otherwise.
- The ISNA function in Excel does only one thing - checks for #N/A errors (meaning "not available"). If a given value is an #N/A error, the function returns TRUE, FALSE otherwise. In our case, TRUE means that a value from List 1 is not found within List 2 (i.e. an #N/A error is returned by MATCH).
- Because it may be very confusing for your users to see TRUE for values that do not appear in List 1, you wrap the IF function around ISNA to display "
*Not in List 1*" instead, or whatever text you want.

For example, to compare values in column B against values in column A, the formula takes the following shape (where B2 is the topmost cell):

`=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")`

As you remember, the MATCH function in Excel is case-insensitive by itself. To get it to distinguish the character case, embed the EXACT function in the *lookup_array* argument, and remember to press Ctrl + Shift + Enter to complete this **array formula**:

`=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")`

The following screenshot shows both formulas in action:

To learn other ways to compare two lists in Excel, please see the following tutorial: How to compare 2 columns in Excel.

## Excel VLOOKUP and MATCH

This example assumes you already have some basic knowledge of Excel VLOOKUP function. And if you do, chances are that you've run into its numerous limitations (the detailed overview of which can be found in Why Excel VLOOKUP is not working) and are looking for a more robust alternative.

One of the most annoying drawbacks of VLOOKUP is that it stops working after inserting or deleting a column within a lookup table. This happens because VLOOKUP pulls a matching value based on the number of the return column that you specify (index number). Because the **index number** is "hard-coded" in the formula, Excel is unable to adjust it when a new column(s) is added to or deleted from the table.

The Excel MATCH function deals with a **relative position** of a lookup value, which makes it a perfect fit for the *col_index_num* argument of VLOOKUP. In other words, instead of specifying the return column as a static number, you use MATCH to get the current position of that column.

To make things easier to understand, let's use the table with students' exam scores again (similar to the one we used at the beginning of this tutorial), but this time we will be retrieving the real score and not its relative position.

Assuming the lookup value is in cell F1, the table array is $A$1:$C$2 (it's a good practice to lock it using absolute cell references if you plan to copy the formula to other cells), the formula goes as follows:

`=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)`

The 3^{rd} argument (*col_index_num*) is set to 3 because the *Math Score* that we want to pull is the 3^{rd} column in the table. As you can see in the screenshot below, this regular Vlookup formula works well:

But only until you insert or delete a column(s):

So, why the #REF! error? Because *col_index_num* set to 3 tells Excel to get a value from the third column, whereas now there are only 2 columns in the table array.

To prevent such things from happening, you can make your Vlookup formula more dynamic by including the following Match function:

`MATCH(E2,A1:C1,0)`

Where:

- E2 is the lookup value, which is
**exactly equal**to the name of the return column, i.e. the column from which you want to pull a value (*Math Score*in this example). - A1:C1 is the lookup array containing the table headers.

And now, include this Match function in the *col_index_num* argument of your Vlookup formula, like this:

`=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)`

And make sure it works impeccably no matter how many columns you add or delete:

In the screenshot above, I locked all cell references for the formula to work correctly even if my users move it to another place in the worksheet. A you can see in the screenshot below, the formula works just fine after deleting a column; furthermore Excel is smart enough to properly adjust absolute references in this case:

## Excel HLOOKUP and MATCH

In a similar manner, you can use the Excel MATCH function to improve your HLOOKUP formulas. The general principle is essentially the same as in case of Vlookup: you use the Match function to get the relative position of the return column, and supply that number to the *row_index_num* argument of your Hlookup formula.

Supposing the lookup value is in cell B5, table array is B1:H3, the name of the return row (lookup value for MATCH) is in cell A6 and row headers are A1:A3, the complete formula is as follows:

`=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)`

As you have just seen, the combination of Hlookup/Vlookup & Match is certainly an improvement over regular Hlookup and Vlookup formulas. However, the MATCH function doesn't eliminate all their limitations. In particular, a Vlookup Match formula still cannot look at its left, and Hlookup Match fails to search in any row other than the topmost one.

To overcome the above (and a few other) limitations, consider using a combination of INDEX MATCH, which provides a really powerful and versatile way to do lookup in Excel, superior to Vlookup and Hlookup in many respects. The detailed guidance and formula examples can be found in INDEX & MATCH in Excel - a better alternative to VLOOKUP.

This is how you use MATCH formulas in Excel. Hopefully, the examples discussed in this tutorial will prove helpful in your work. I thank you for reading and hope to see you on our blog next week!

## Practice workbook for download

Excel MATCH formula examples (.xlsx file)

## 88 comments

Trying to find a way to help using job id’s to help with head count and FTE calculations- I pulled data with employee name, specific job id’s for their roles. There are 5 directors and I can only use the the specific job id once under each director on specific roles - multiple roles across under each director. Need to pull info under each director with subcategories in some instances.

Hello Luka!

To count the number of values that match multiple criteria, use the COUNTIFS function as described in this article: How to use Excel COUNTIFS and COUNTIF with multiple criteria.

I can't give more precise advice as I can't see your data.

Row of positive and negative values. Header is date

Each positive and negative is on a column identified with a date.

Formula to search row value from left to right and list date for negative value.

Hi! If I understand your task correctly, use INDEX MATCH to search for a value by row:

=INDEX(A1:M1,,MATCH(TRUE,A2:M2<0,0))

Hi,

Need match formula help....I'm currently using the formula =MATCH(a2,c:c,FALSE) and instead of #N/A in the cell I want it to enter a symbol. Is this possible?

Hi! To replace an error message with a symbol or word, use the IFERROR function. Read more: How to use IFERROR in Excel with formula examples.

hi, I have multiple tab spreadsheet. One tab has the master list of addresses each tab represents a region. Column A has letter in each region that matches an entry on the master list. I want to use the information in from the master list column B and C to popuate columns B and C on the region tabs Any direction would be greatly appreciated

I am using Match(A1&B1,$D:$D,0) to produce the row no of that item in Col D, My problem is that the function gives the answer 0 in all cases. I can prove that there are cases when $A1 & B$1 do equal a value in D. I can find no reference to the meaning of 0 as an answer to Match. The answer #N/A is when there is no match

Hi! I can't check your formula because I don't have your data.

Hello - I'm looking to use index/match in a rank formula, but it's not quite working the way I need it to. My current formula is:

RANK(E3,INDEX(E:E,0,MATCH((F3=F:F)*(A3=A:A),0)))

E3 --> is the cell I need to rank from values in column E

BUT I don't want to rank all of the cells in column E, just a subset of the full list

So I decided to use Index/Match to just pull the subset of column E cells I want to rank against:

F3 and A3 are my keys to this subset (they're a name and number that occur multiple times in the sheet)

But my formula just brings back the entire column E in the rank equation; my index/match do not narrow it down at all!

Open to all and any help! thanks

Hi! The MATCH function can return only a single position number. Also, the RANK function cannot determine the relative position of a number separately in a group of values. I recommend using this instruction: How to rank in Excel by group.

I believe the following formula will help you solve your task:

=SUMPRODUCT((F3=F:F)*(A3=A:A)*(E3<E:E))+1

Wonderful! Thank you Alexander

HELLO SIR,

I HAVE A PROBLEM.

MY PROBLEM IS-

order-id SKU order-id

112-3195379-9870667 112-3195379-9870667 0 FORMULA -=INDEX($V$3:$V$2605,MATCH(W604,$H$3:$H$2605,0))

112-3195379-9870667 Pharma436528461 112-3195379-9870667 0

112-3195379-9870667 Pharma436528461 112-3195379-9870667 0

112-3195379-9870667 Pharma436528461 112-3195379-9870667 0

112-3195379-9870667 Pharma436528461 112-3195379-9870667 0

112-3195379-9870667 Pharma436528461 112-3195379-9870667 0

I NEED SKU VALUE BUT IT IS TAKING BLANK VALUE, PLEASE HELP

Your formula contains no errors. Check the data you are looking for. I cannot help here.

I want to create a match formula where the lookup data is in another sheet.

For example, I want to look or match the amount of 4,407.72 in Sheet called "Look", row 7

The look up value in the tab called "Data" starting from row B1 to B3. I am using the integer function with match to avoid small differences.

4,407.72

4,507.00

4,508.00

My formula array on c9 is not working. It wont even let me complete the formula array. From the data, is should see row with a value of 4407.72, since, the integer value of it is 4,407.72.

=MATCH(C7,data!INT(B1:B3),0)

Any help would be much appreciated.

Hi! The integer value 4407.72 is 4407. Read the INT function manual carefully. Only an integer can be searched among other integers. Perhaps the formula will look like this:

MATCH(INT(C7),data!INT(B1:B3),0)