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.
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.
Excel Function Syntax
MATCH(lookup_value, lookup_array, [match_type])
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 |
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.
Table of Contents
- Lookup_array in ascending order
- Lookup_array in any order
- Lookup_array in descending order
- Array formula
- Using wildcard character
- VBA Example
- Download excel *.xlsx file
Example 1 - 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.
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.
Example 3 - 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.
Example 4 - 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.
Wildcard character
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.
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.
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.
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.
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.
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.
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.
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.
VBA code
Sub VBA_MATCH() MsgBox Application.WorksheetFunction.Match(Range("C2"), Range("C5:C8"), 0) End Sub
MATCH function links
Articles with the 'MATCH' Function
The following 3 articles have formulas that contain the MATCH function.
Extract table headers based on a condition
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 D12 matches two values in two columns each and returns a value on the same […]
Match a range value containing both text and numerical characters
Formula in cell C11: =LOOKUP(MID(C10, 4, 999)*1, MID(B3:B8, 4, 999)*1, D3:D8) This formula is an array formula. To enter an array […]
Functions in 'Lookup and reference'
The MATCH function function is one of many functions in the 'Lookup and reference' category.
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]
How to use the COLUMN function
The COLUMN function returns the column number of the top-left cell of a cell reference. If the argument is not […]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]
How to use the FILTER function
The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category […]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Gets a value in a specific cell range based on a row and column number.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Identify the position of a value in an array.
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]
The SORT function lets you sort values from a cell range or array. It returns an array with a size […]
How to use the SORTBY function
The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
How to use the UNIQUE function
The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
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:
Download 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 […]