How to use the MATCH function
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
The formula in cell D10 searches for value "A" in cell range C3:C7 and finds an exact match in cell C5. The relative position of C5 in cell range C3:C7 is 3, shown in column B.
The MATCH function is probably one of the most used Excel functions, it is more versatile combined with the INDEX function than the VLOOKUP function.
Table of Contents
- MATCH Function Syntax
- MATCH function Arguments
- Lookup_array in ascending order
- Lookup_array in any order
- Lookup_array in descending order
- How to use the MATCH function in an array formula
- How to do a partial match - wildcard character
- Match cell that ends with the condition
- Match cell that begins with the condition
- Match cell that contains a given string
- Match cell that begins with a specific string and ends with another string - any number of characters in between
- Match cell that begins with string and ends with another string - a single character in between
- VBA Example
- How to do a case sensitive match
- MATCH function not working
- #NAME! error
- #N/A! error
- Get excel *.xlsx file
1. MATCH Function Syntax
MATCH(lookup_value, lookup_array, [match_type])
2. MATCH function arguments
lookup_value | Required. Is the value you use to find the value you want in the array, a number, text or logical value, or a reference to one of these. |
lookup_array | Required. Is a contiguous range of cells containing possible lookup values, an array of values, or a reference to an array. |
[match_type] | Optional. How to match, a number -1,0,1. If omitted default value is number 1. |
match_type
-1 | Finds the largest value that is less than or equal to lookup_value. Lookup_array must be in ascending order. |
0 | Finds the value that is exactly equal to lookup_value. Lookup_array can be in any order |
1 | Finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be sorted in a descending order |
Be careful with the third argument [match_type], remember to use 0 (zero) in the third argument if you want to find an exact match which you almost always want to.
If you don't use 0 (zero) in the third argument, the values in the lookup_array argument must be sorted in an ascending or descending order based on the lookup_array number you choose.
2.1 Comments
- The lookup_array must be a one dimensional horizontal or vertical cell range
- The lookup_value can also be an array, one or two dimensional. (array formula)
- The lookup_array can be one value. (array formula)
- You can use other Excel functions in the lookup_value argument.
- You can use other Excel functions in the lookup_array argument as long as they return a one-dimensional array.
- I use match_type = 0 almost all the time.
Below are examples demonstrating what the formula returns using different arguments.
3. Lookup_array in ascending order
This example shows what happens and what is required if you use 1 as the [match_type] argument. Cell range C19:C21 har numbers sorted in an ascending order, which is required to get reliable results.
In this setup, the MATCH function finds the largest value that is less or equal to the lookup_value. You can use text values as well, however, make sure they are sorted in ascending order.
becomes
MATCH(2, {1; 5; 10; 12}, 1)
{1; 5; 10; 12} is an array of values separated by semicolons which means they are located in a row each.
[Match_type] 1 - Find the largest value {1; 5; 10; 12} that is less or equal to lookup_value which is 2.
MATCH(2, {1; 5; 10; 12},1)
There is only one value that is less or equal to the lookup value and that number is 1. The relative position of number 1 in array {1; 5; 10; 12} is 1. 1 is returned in cell D14.
4. Example 2 - Lookup_array in any order
This setup is what I recommend using and is what I am using myself the most. [match_type] is 0 (zero) which allows you to have the lookup_array in any order you want.
The image above demonstrates the following formula in cell D14:
The first argument (lookup_value) is in cell C17, and the second argument (lookup_array) is in cell range C19:C22.
=MATCH(C17, C19:C21,0)
becomes
MATCH(1, {10; 12; 1; 5},0)
{10; 12; 1; 5} is an array of values separated by semicolons which means they are located in a row each.
MATCH(1, {10; 12; 1; 5},0)
Match_type 1 - Find the first value {10; 12; 1; 5} that is exactly equal to lookup_value (1)
MATCH(1, {10; 12; 1; 5},0)
The relative position of number 1 in array {10; 12; 1; 5} is 3. 3 is returned in cell D14.
5. Lookup_array in descending order
The following formula has -1 as the third argument [match_type], the lookup_array must be sorted in descending order for this setup to work.
In this case, the MATCH function finds a value in cell range C19:C22 that is larger than or equal to the lookup_value which is specified in cell C17.
becomes
MATCH(2, {12; 10; 5; 1},-1)
Match_type 1 - Find the smallest value {12; 10; 5; 1} that is greater than or equal to lookup_value (2)
MATCH(2, {12; 10; 5; 1},-1)
The relative position of number 5 in the following array {12; 10; 5; 1} is 3. 3 is returned in cell D14.
6. How to use the MATCH function in an array formula
This example demonstrates what happens if you use multiple values in the first argument lookup_value. The MATCH function returns an array of values and you are required to enter the formula as an array formula.
- Select cell range B19:B22.
- Copy the formula below and paste to cell or formula bar.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
Excel adds curly brackets to the formula automatically, don't enter these characters yourself.
becomes
MATCH({12;1;10;5}, {10; 12; 1; 1},0)
Match_type 0 (zero) finds the first value {10; 12; 1; 1} that is exactly equal to lookup_value {12;1;10;5}
Lookup value 12 is the second value in the lookup_array {10; 12; 1; 1}.
Lookup value 1 is the third value in the lookup_array {10; 12; 1; 1}.
Lookup value 10 is the first value in the lookup_array {10; 12; 1; 1}.
Lookup value 5 is not found in the lookup_array {10; 12; 1; 1}.
{2; 3; 1; #N/A} is returned in cell range B19:B22.
Update!
Dynamic arrays were recently introduced to Excel 365 subscribers, they are different from regular array formulas. You don't need to enter the formula as an array formula, simply enter them as a regular formula.
Excel knows that the formula returns multiple values and extends the selection automatically, this is called spilled array behavior.
Excel returns a #SPILL! error in case there are values in cells below that prevent all array values to be displayed.
7. How to do a partial match - Wildcard characters
The asterisk character allows you to perform wilcard searches, it represents 0 (zero) to any number of characters. If this is unclear then check out the examples below.
7.1 Match cell that ends with condition
The formula in cell F3 looks for values in cell range C3:C8 that match any character and then ends with an a. No cell value in cell range C3:C8 matches that condition so the function returns #N/A error.
The formula in cell F4 is similar to cell F3 but it ends with a capital letter A. No values match that condition either and the formula returns a #N/A! error which means that the value is not available.
Formula in cell F10:
Cell E10 contains "*car" which matches the first value in cell range C3:C8, remember that the asterisk also matches no character. The formula returns 1.
7.2 Match cell that begins with condition
The next formula looks for a value that starts with an r and can contain any number of characters after that. Cell E5 contains r*.
Formula in cell F5:
The formula returns 5 which is the relative position of value "rocket" in cell range C3:C8, note that the condition would also have matched value "Rocket" and "r" and "R". The asterisk means no character up to any number of any character.
7.3 Match cell that contains string
Formula in cell F6:
The formula in cell F6 looks for a value that matches *o* which means any number of characters before and after o. The first value that matches that condition is found in position 2 which is "boat", however, there are more values that match. Value "rocket" would have matched the condition but the MATCH function returns only the number of the first value found in cell range C3:C8.
7.4 Match cell that begins with string and ends with another string - any number of characters in between
Formula in cell F7:
Cell E7 contains b*e which means that the value must begin with a b or B and must end with a e or E. Only one value matches that condition which is "bike". 7 is returned in cell F7.
7.5 Match cell that begins with string and ends with another string - a single character in between
The question mark character is different than the asterisk character, it matches only a single character.
Formula in cell F8:
Cell E8 contains b?e and the MATCH function cant find a value in cell range C3:C8 that starts with a "b", matches any single character, and ends with an "e".
Value "bike" has two characters between b and e. The formula returns #N/A! in cell F8.
Formula in cell F9:
Cell E9 contains "b?ke" and matches cell C6 which is the last value in cell range C3:C8. The formula returns 6.
8. VBA Example
The following macro uses the MATCH function to find the position of the lookup value in cell range C5:C8. It then displays a message box containing that position.
8.1 VBA code
Sub VBA_MATCH() MsgBox Application.WorksheetFunction.Match(Range("C2"), Range("C5:C8"), 0) End Sub
9. How to do a case sensitive match
The formulas in column F perform a case-sensitive match, the lookup values are in column E and the lookup array is in cell range C3:C8.
Array formula in cell F4:
The result is a number representing the position of the lookup value in the lookup array.
9.1 Explaining formula in cell F4
Step 1 - Case sensitive comparison
The EXACT function performs a case-sensitive comparison. The result is a boolean value TRUE or FALSE.
EXACT(E4,$C$3:$C$8)
becomes
EXACT("TRAIN",{"Car";"boat";"Train";"airplane";"TRAIN";"bike"})
and returns
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.
Step 2 - Match boolean value TRUE
MATCH(TRUE, EXACT(E4, $C$3:$C$8), 0)
becomes
MATCH(TRUE, {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, 0)
and returns 5 in cell F4.
10. MATCH function not working
10.1 #NAME! error
10.2 #N/A! error
11. Get Excel file
MATCH function links
'MATCH' function examples
The following 215 articles contain the MATCH function.
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This post explains how to lookup a value and return multiple values. No array formula required.
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
This stock chart built in Excel allows you to change the date range and the chart is instantly updated. What's […]
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
Lookup with criteria and return records.
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
The image above shows a formula in cell D3 that extracts the most recent date in cell range B3:B15. =MAX(B3:B15) […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]
The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]
Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
Functions in this article
Functions in 'Lookup and reference' category
The MATCH function function is one of many functions in the 'Lookup and reference' category.
Excel function categories
Excel categories
12 Responses to “How to use the MATCH function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[...] MATCH(lookup_value;lookup_array; [match_type]) Returns the relative position of an item in an array that matches a specified value [...]
Oscar,
You might want to read the comments of the below post to see couple of more interesting uses of the MATCH Function
https://fastexcel.wordpress.com/2013/02/27/speedtools-avlookup2-memlookup-versus-vlookup-performance-power-and-ease-of-use-shootout-part-1/#comments
sam,
Very interesting comment! I never thought of that.
Thank you for commenting!
Respected sir,
You solved one of my excel related problem some months back. This time I have another problem. This time I have some fields as below:
Capacity City books Big_Boxes small_Boxes Total
90 Kanpur 400 4 1 5
Lucknow 690 7 1 8
Jhansi 240 2 1 3
Allahabad 20 0 1 1
Total 17
Now, I have to generate slips to paste on boxes which are to be despatched:
City: Kanpur
No_of_Text_Books in this Box 90 Box No.1
Total_No._of_Books 400 in 5 Boxes
These slips are to be generated till end. Please help me.
neeraj kumar,
Formula in cell A10:
Formula in cell A11:
Get the Excel *.xlsx file
neeraj-kumar.xlsx
[…] MATCH(lookup_value, lookup_array, [match_type] Returns the relative position of an item in an array that matches a specified value […]
[…] MATCH(lookup_value, lookup_array, [match_type] Returns the relative position of an item in an array that matches a specified value. […]
[…] Match function […]
[…] and returns 2. Read more about MATCH function. […]
[…] MATCH(lookup_value;lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value […]
[…] MATCH(lookup_value,lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value […]