The MATCH function is similar to the VLOOKUP function as they both categorized under Excel Lookup/Reference Functions. VLOOKUP searches for a specific value in a column and returns a value in the same row while the MATCH function searches for a certain value in a range and returns the position of that value.
Excel MATCH function looks for a specified value in a range of cells or an array and returns the relative position of the first appearance of that value in the range. The MATCH function can also be used to look up a certain value and return its corresponding value with the help of the INDEX function (just like Vlookup). Let’s see how to use the Excel MATCH function to find the position of a lookup value in a range of cells.
Excel MATCH Function
The MATCH function is a built-in function in Excel and its primarily used for locating the relative position of a lookup value in a column or a row.
Syntax of MATCH Function:
=MATCH(lookup_value,lookup_array,[match_type})Where:
lookup_value – The value you want to look up in a specified range of cells or in an array. It can be a numeric value, text value, logical value, or a cell reference that has a value.
lookup_array – The arrays of cells in which you are searching for a value. It must be a single column or a single row.
match_type – It is an optional parameter that can be set to 0,1, or -1 and the default is 1.
- 0 looks for an exact match, when it’s not found, returns an error.
- -1 looks for the smallest value that is greater than or equal to lookup_value when the lookup array in ascending order.
- 1 looks for the largest value that is less than or equal to the look_up value when the lookup array in descending order.
Find Position of An Exact Match
Let’s assume, we have the following dataset where we want to find the position of a certain value.
In this table, we want to find the position a city name (Memphis) in column (A2:A23), so we use this formula:
=MATCH("memphis",A2:A23,0)The third argument is set to ‘0’ because we want to find an exact match of the city name. As you can see that the city name “memphis” in the formula is in lower case while in the table the first letter of the city name is in upper case (Memphis). Still, the formula is able to find the position of the specified value in the given range. It’s because the MATCH function is case-insensitive.
Note: If the lookup_value is not found in the lookup range or if you specify the wrong lookup range, the function will return the #N/A error.
You can use a cell reference in the first argument of the function instead of a direct value. The formula below finds the position of the value in cell F2 and returns the result in cell F3.
Find Position of An Approximate Match
There are two ways you can look for an approximate or exact match of the lookup value and return its position.
- One way is to find the smallest value that is greater than or equal (next largest match) to the specified value. It can be achieved by setting the last argument (match_type) of the function as ‘-1’
- Another way is the largest value that is less than or equal (next smallest match) to the given value. It can be achieved by setting the match_type of the function as ‘1’
Next Smallest Match
If the function can’t find an exact match to the specified value when the match type is set to ‘1’, it locates the largest value that is slightly less than the specified value (which means the next smallest value) and returns its position. For this to work, you need to sort the array in ascending order, if not it will result in an error.
In the example, we use the below formula to find the next smallest match:
=MATCH(F2,D2:D23,1)When this formula couldn’t find the exact match for the value in cell F2, it points to the position (16) of the next smallest value i.e. 98.
Next Largest match
When the match type is set to ‘-1’ and the MATCH function can’t find an exact match, it finds the smallest value that is greater than the specified value (which means the next largest value) and returns its position. The lookup array must be sorted in descending order for this method otherwise it will return an error.
For example, enter the following formula to find the next largest match to the lookup value:
=MATCH(F2,D2:D23,-1)This MATCH function searches for the value in F2 (55) in the lookup range D2:D23, and when it can’t find the exact match, it returns the position (16) of the next largest value i.e. 58.
Wildcard Match
Wildcards can be used in the MATCH function only when match_type is set to ‘0’ and the lookup value is a text string. There are wildcards you can use in the MATCH function: an asterisk (*) and a question mark (?).
- Question mark (?) is used to match any single character or letter with the text string.
- Asterisk (*) is used to match any number of characters with the string.
For example, we used two ‘?’ wildcards in the lookup_value (Lo??n) of the MATCH function to find a value that matches the text string with any two characters (in the wildcards places). And the function returns the relative position of the matching value in cell E5.
=MATCH("Lo??n",A2:A22,0)
You can use (*) wildcard the same way as (?), but an asterisk is used to match any number of characters while a question mark is used to match any single character.
For instance, if you use ‘sp*’, the function could match with speaker, speed, or spielberg, etc. But if the function finds multiple/duplicate values matching the lookup value, it will only return the position of the first value.
In the example, we entered “Kil*o” in the lookup_value argument. So the MATCH() function searches for a text which contains ‘Kil’ in the beginning, ‘o’ at the end, and any number of characters in between. ‘Kil*o’ matches Kilimanjaro in the array and therefore the function returns the relative position of Kilimanjaro, which is 16.
INDEX and MATCH
MATCH functions are rarely used alone. They often paired with other functions to create powerful formulas. When the MATCH function is combined with the INDEX function, it can perform advanced lookups. Many people still prefer using VLOOKUP to lookup a value, because it’s simpler but INDEX MATCH is more flexible and faster than VLOOKUP.
VLOOKUP can only look up a value vertically i.e columns while INDEX MATCH combo can do both vertical and horizontal lookups.
INDEX function used to retrieve a value at a specific location in a table or a range. The MATCH function returns the relative position of a value in a column or a row. When combined, the MATCH finds the row or column number (location) of a specific value, and the INDEX function retrieves a value based on that row and column number.
Syntax of INDEX function:
=INDEX(array,row_num,[col_num],)Anyway let’s see how INDEX MATCH works with an example.
In the example below, we want to retrieve the ‘Quiz2’ score for the student ‘Anne’. To do that we’ll use the below formula:
=INDEX(B2:F20,MATCH(H2,A2:A20,0),3)INDEX needs a row and column number to retrieve a value. In the above formula, the nested MATCH function finds the row number (position) of the value ‘Anne’ (H2). Then we supply that row number to the INDEX function with a range B2:F20 and a column number (3), which we specify. And the INDEX function returns the score ’91’.
Two-way lookup with INDEX and MATCH
You can also use the INDEX and MATCH functions to lookup a value in a two-dimensional range (two-way lookup). In the above example, we used the MATCH function to locate the row number of a value, but we entered the column number manually. But we can find both row and column by nesting two MATCH functions, one in row_num argument and another in column_num argument of the INDEX function.
Use this formula for a two-way lookup with INDEX and MATCH:
=INDEX(A1:F20,MATCH(H2,A2:A20,0),MATCH(H3,A1:F1,0))As we know, the MATCH function can look for a value both horizontally and vertically. In this formula, the second MATCH function in the colum_num argument finds the position of Quiz2 (4) and supplies it to the INDEX function. And the INDEX retrieves the score.
Now, you know how to use the Match function in Excel.
Member discussion