The Excel MATCH function helps you find the position of a value within a range of cells. It’s a neat way to locate an item’s placement, and unlike the VLOOKUP function which returns a value, MATCH just tells you where the value is located. This can be super useful for different kinds of lookups.
The MATCH function searches through a row or column for a particular value and returns its position. By using it with the INDEX function, you can retrieve values much like you would with VLOOKUP. This guide will show you how to use the MATCH function in Excel to pinpoint the position of your lookup value.
What is the excel match function?
The MATCH function is designed to identify the relative location of a value within a row or column.
Here’s the function’s structure:
=MATCH(lookup_value, lookup_array, [match_type])
Where:
-
lookup_value – This is the value you’re searching for. It can be text, a number, a logical value, or a cell reference.
-
lookup_array – This is the range of cells or the array where the search happens. It should be a single row or column.
-
match_type – This is optional and specifies how you want the match to be done. It defaults to 1. You can use 1, 0, or -1.
-
0 – Finds the first value that’s an exact match. If no match is found, it throws an error.
-
1 – Finds the largest value that’s less than or equal to
lookup_value. Thelookup_arraymust be in ascending order. -
-1 – Finds the smallest value that’s greater than or equal to
lookup_value. Thelookup_arraymust be sorted in descending order.
Finding an exact match
Let’s say you have some data, and you need to find out where a specific city appears in the list.
To find the location of “Memphis” in the range A2:A23, use this formula:
=MATCH("memphis", A2:A23, 0)
The 0 tells the function to look for an exact match. Even if you enter “memphis” in lowercase, and the list has “Memphis” with an uppercase “M”, the function will still find the match because MATCH isn’t case-sensitive.
If the lookup_value isn’t found or the range is incorrect, you’ll get a #N/A error.
You can also reference a cell for the lookup_value. For example, the formula below uses the value in cell F2 to locate it in the range:
=MATCH(F2, A2:A23, 0)
Finding an approximate match
Sometimes you might need an approximate match, not an exact one. The match_type argument helps with this.
1: Finds the largest value less than or equal tolookup_value. Thelookup_arrayhas to be sorted in ascending order.-1: Finds the smallest value greater than or equal tolookup_value. Thelookup_arraymust be sorted in descending order.
Find next smallest match
When match_type is 1 and no exact match is found, MATCH returns the position of the largest value less than or equal to your lookup_value. Remember, your data must be sorted in ascending order for this to work correctly.
For example, if you want the closest value to a number without exceeding it, the formula using match_type as 1 looks like this:
=MATCH(F2, D2:D23, 1)
If the exact value in F2 isn’t found, the function returns the location of the next smallest number.
Find next largest match
If match_type is set to -1, MATCH returns the smallest value that’s greater than or equal to the lookup_value. Your lookup_array has to be sorted in descending order.
If the values in D2:D23 are sorted in descending order, and there’s no exact match, the function will show the location of the next largest value.
=MATCH(F2, D2:D23, -1)
Using wildcards for matching
The MATCH function allows wildcard characters in text searches, but only when match_type is set to 0. These are:
?: Represents any single character.*: Represents any number of characters.
To find a city that starts with “Lo”, followed by any two characters, and ends with “n”, use:
=MATCH("Lo??n", A2:A22, 0)
The two
? symbols represent two arbitrary characters, so “London” is a match.
You can also use the asterisk * to match any number of characters. For example, to find a word that starts with “Kil”, has any number of characters in between, and ends with “o”, you can use:
=MATCH("Kil*o", A2:A22, 0)
This formula will find “Kilimanjaro” in the list.
Using INDEX and MATCH together
The MATCH function is often paired with INDEX for more flexible and robust lookups. While VLOOKUP is useful for vertical lookups, combining INDEX and MATCH allows lookups in any direction and can improve performance.
The INDEX function returns a value from a given range or array based on row and column numbers.
The INDEX function has the following syntax:
=INDEX(array, row_num, [column_num])
For example, if you want to get the score of a particular student in “Quiz2”, use INDEX and MATCH like this:
=INDEX(B2:F20, MATCH(H2, A2:A20, 0), 3)
Here:
MATCH(H2, A2:A20, 0)finds the row where the student’s name (inH2) appears inA2:A20.INDEXthen uses this row number and the column number (3for “Quiz2”) to get the score from the rangeB2:F20.
Two-way lookup with index and match
You can perform a two-way lookup by using two MATCH functions – one for the row and another for the column. This lets you specify both the row and the column dynamically in the INDEX function.
=INDEX(B1:F20, MATCH(H2, A2:A20, 0), MATCH(H3, B1:F1, 0))
In this formula:
MATCH(H2, A2:A20, 0)gets the row number for the student’s name inH2.MATCH(H3, B1:F1, 0)gets the column number for the quiz name inH3.INDEXreturns the value at the intersection of that row and column fromB1:F20.