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:
MATCH(lookup_value, lookup_array, [match_type])
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) - find 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 - find the first value in the array that is exactly equal to the lookup value. No sorting is required.
- -1 - find the smallest value in the array 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 5th 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:
MATCH(TRUE, EXACT(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:
IF(ISNA(MATCH(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 3rd argument (col_index_num) is set to 3 because the Math Score that we want to pull is the 3rd 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 .
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)
You may also be interested in
- 6 most efficient uses of the INDEX function in Excel
- Why VLOOKUP is not working?
- How to compare two columns for matches and differences
- INDEX MATCH with multiple criteria
FAQs
What are some examples with the match formula in Excel? ›
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
How do you get a yes or no answer in Excel with a formula? ›- Select the cells where you want to include this information. ...
- Select Data Validation from the Data tab on the taskbar.
- Insert the text "yes,no" into the Source field on the popup on the Settings tab.
- Check the boxes next to Ignore blank and In-cell dropdown.
- Press OK.
If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because: The cell has unexpected characters or hidden spaces. The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.
How do I compare two lists in Excel to find matches? ›Use the formula “=IF(A1=B1, “Match”,”Not a match”)" to test if the cell in A1 is the same as B1, replacing the references to match your own data. Press the “Enter” key or select another cell to apply the formula. Identify whether your cell reads “Match” or “Not a match”, depending on the data in cells A1 and B1.
How do I compare two columns in Excel for matches? ›The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty. To compare two columns in Excel for differences, replace the equals sign with the non-equality sign (<>).
How do you write an IF THEN formula in Excel with multiple criteria? ›Another way to get an Excel IF to test multiple conditions is by using an array formula. To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.
Can Vlookup return yes or no? ›One of the most common scenarios when you combine If and Vlookup together is to compare the value returned by Vlookup with a sample value and return Yes / No or True / False as the result. Translated in plain English, the formula instructs Excel to return True if Vlookup is true (i.e. equal to the specified value).
How do I use VLOOKUP to match? ›In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
What is the formula for match two cells in Excel? ›If you want to compare columns with any two or more cells with the same values in the same row, then you might use the following formulas: =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "")
What is an example of an if statement? ›if (score >= 90) grade = 'A'; The following example displays Number is positive if the value of number is greater than or equal to 0 . If the value of number is less than 0 , it displays Number is negative .
What is the formula for perfect match? ›
In graph theory, a perfect matching in a graph is a matching that covers every vertex of the graph. More formally, given a graph G = (V, E), a perfect matching in G is a subset M of edge set E, such that every vertex in the vertex set V is adjacent to exactly one edge in M.
Why won't my cell recognize a formula? ›The most common reason for an Excel formula not calculating is that you have inadvertently activated the Show Formulas mode in a worksheet. To get the formula to display the calculated result, just turn off the Show Formulas mode by doing one of the following: Pressing the Ctrl + ` shortcut, or.
Does match formula work with text? ›The Match function can be used to match numeric values, logical values, or text strings. Note that, when looking up a text string, the function is NOT case-sensitive. So, for example, the text strings "TEXT" and "text" will both be considered to be a match.
How do you match two columns and pull information from a third in Excel? ›- In a cell next to the two columns being compared, type an equal sign.
- Enter the references of two cells to be compared, separated by an equal sign.
- Hit Enter and copy that formula to the remaining rows.
- Open WPS Excel /Spreadsheet file where you want to check if a value exists in list in excel.
- Click on the cell where you want your output to reflect whether a value exists in list.
- Type “=ISNUMBER(MATCH” and press Tab.
Comparing strings in Excel can be done with a simple function. The =EXACT(A1,B1) function will return TRUE if the two cells contain the same text string.
Can you use VLOOKUP to compare two spreadsheets? ›Using VLOOKUP between two Excel sheets
Maintain organized data: A VLOOKUP between two spreadsheets allows you to organize your data as needed on separate sheets. Though you might record data on these individual worksheets, a VLOOKUP allows you to still access and reference data across each.
You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column. The syntax for MATCH() is =MATCH(lookup_value, lookup_array, [match_type]) . Using MATCH, you can look up a value both horizontally and vertically.
Can you put a formula in an if function? ›As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet. It is possible to nest multiple IF functions within one Excel formula.
How to do an if statement with 3 outcomes? ›- Method 1: Nested IF Function =IF(C2<15, "Bad", IF(C2<20, "OK", IF(C2<25, "Good", "Great")))
- Method 2: IF Function with AND Logic =IF(AND(A2="Mavs", B2="Guard", C2>25), "Yes", "No")
What is the difference between VLOOKUP and index match? ›
The main difference between VLOOKUP and INDEX MATCH is in column reference. VLOOKUP requires a static column reference whereas INDEX MATCH requires a dynamic column reference. With VLOOKUP you need to manually enter a number referencing the column you want to return the value from.
What is the difference between VLOOKUP and Xlookup? ›To recap, the key differences between an XLOOKUP and a VLOOKUP are: XLOOKUP can look for values to the left and right of the lookup array, while VLOOKUP is limited to only looking for values to the right of the lookup value column.
How do you do a VLOOKUP if a cell contains specific text? ›By using the asterisk “wildcard” (*), within a VLOOKUP, we can lookup values that contain (partial match) certain text, instead of values that match the lookup text exactly.
How do you write an if statement with multiple conditions? ›- AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)
- OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False)
- NOT – =IF(NOT(Something is True), Value if True, Value if False)
Nested IF functions, meaning one IF function inside of another, allows you to test multiple criteria and increases the number of possible outcomes.
Why is VLOOKUP not picking up values? ›Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, take time to verify that the referenced cells don't have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type.
What is better than VLOOKUP? ›One of the popular improvements to the VLOOKUP limitations is to combine 2 Excel functions, INDEX and MATCH. Also, the INDEX/MATCH combination runs faster than VLOOKUP, which can make a difference on large sets of data.
Does VLOOKUP only work with exact match? ›You Need an Exact Match
This argument is optional, but if left empty, the TRUE value is used. The TRUE value relies on your data being sorted in ascending order to work. The image below shows a VLOOKUP with the range_lookup argument omitted, and the incorrect value being returned.
Select the cell or range of cells. Select Copy or press Ctrl + C. Select Paste or press Ctrl + V.
What is the shortcut for matching cells in Excel? ›On the Home tab, click Find & Select > Go To (in the Editing group). Keyboard shortcut: Press CTRL+G.
What are 3 real world examples of a conditional statement? ›
- Statement 1: If you work overtime, then you'll be paid time-and-a-half.
- Statement 2: I'll wash the car if the weather is nice.
- Statement 3: If 2 divides evenly into , then is an even number.
- Statement 4: I'll be a millionaire when I win the lottery.
There are three forms of IF statements: IF-THEN , IF-THEN-ELSE , and IF-THEN-ELSIF . The simplest form of IF statement associates a Boolean expression with a sequence of statements enclosed by the keywords THEN and END IF .
What is an example match formula? ›The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
Where is match formula in Excel? ›The MATCH Function[1] is categorized under Excel Lookup and Reference functions. It looks up a value in an array and returns the position of the value within the array.
Why is my match formula not working? ›If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because: The cell has unexpected characters or hidden spaces. The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.
Why is Excel not recognizing Text as formula? ›Check the Cell Format for Text
If the format shows Text, change it to Number. When a cell is formatted as Text, Excel makes no attempt to interpret the contents as a formula. After you change the format, you'll need to reconfirm the formula by clicking in the Formula Bar and then pressing the Enter key.
- Investigate possible issues with files in Startup folders. ...
- Install the latest updates. ...
- Check to make sure Excel is not in use by another process. ...
- Investigate Excel file details and contents. ...
- Check whether your file is being generated by a third party.
The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error. To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT functions to parse and compare text.
What is the difference between match and Xmatch? ›XMATCH defaults to an exact match, while MATCH defaults to an approximate match. XMATCH can find the next larger item or the next smaller item. XMATCH can perform a reverse search (i.e. search from last to first). XMATCH does not require values to be sorted when performing an approximate match.
How to do a fuzzy match formula in Excel? ›- Step 1: Download Fuzzy Lookup Add-In. First, we need to download the Fuzzy Lookup Add-In from Excel. ...
- Step 2: Enter the Two Datasets. ...
- Step 3: Create Tables from Datasets. ...
- Step 4: Perform Fuzzy Matching.
What are the match types in Excel? ›
MATCH types: One can use three match types with the MATCH function: 0, 1, and -1. The default match type is 0, which finds an exact match. Match type 1 finds the largest value less than or equal to the lookup_value, while match type -1 finds the smallest value greater than or equal to the lookup_value.
What is the difference between Xmatch and match formula in Excel? ›XMATCH defaults to an exact match, while MATCH defaults to an approximate match. XMATCH can find the next larger item or the next smaller item. XMATCH can perform a reverse search (i.e. search from last to first). XMATCH does not require values to be sorted when performing an approximate match.
What is the Hlookup with match formula in Excel? ›A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match.
What are the three match types? ›There are 3 different match types available when you target keywords in your search campaigns – broad match, phrase match, and exact match.
What is the best match search in Excel? ›Excel's Best Lookup Method: INDEX-MATCH
lookup_array—A range of cells with data. match_type—Specifies how Excel matches the lookup_value with values in the lookup_array. For exact matches, always use 0 for this argument.
- 2.1 Early matches.
- 2.2 Chemical matches.
- 2.3 Friction matches.
- 2.4 Replacement of white phosphorus.
- 2.5 Safety matches.
- Start with: =MATCH( ...
- Now your formula should look like this: =MATCH(G2,A:A, ...
- Your formula should now look like this: =MATCH(G2,A:A,0) ...
- Your formula should look like this by now: =INDEX(MATCH(G2,A:A,0) ...
- Your formula should look like this by now: =INDEX(D:D,MATCH(G2,A:A,0)
If you want to compare columns with any two or more cells with the same values in the same row, then you might use the following formulas: =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "")
Why is match better than VLOOKUP? ›One of the biggest issues with using VLOOKUP is that it uses a static column reference. On the other hand, INDEX MATCH uses a dynamic column reference which means it can lead to fewer errors when moving around columns. This is an advantage that becomes more apparent as you create larger and more complex spreadsheets.
Why use Xmatch instead of match? ›In summary, the XMATCH function is same as MATCH but more flexible and robust. It can look up both in vertical and horizontal arrays, search first-to-last or last-to-first, find exact, approximate and partial matches, and use a faster binary search algorithm.
What is the difference between VLOOKUP and match function? ›
The main difference between VLOOKUP and INDEX MATCH is in column reference. VLOOKUP requires a static column reference whereas INDEX MATCH requires a dynamic column reference. With VLOOKUP you need to manually enter a number referencing the column you want to return the value from.
Can you lookup 2 values in a VLOOKUP? ›You can't specify two lookup values in a VLOOKUP formula, so we'll need to use a workaround, which consists of two steps: Step1: Create a separate column where we will create unique lookup_values by merging our two lookup criteria – name and country – for example “MellaThailand“, “MellaNigeria“, etc.
Can VLOOKUP and Hlookup be used together in the same formula? ›We can use a nested formula that combines the VLOOKUP and HLOOKUP Functions in excel to retrieve values from a table. Approximate and exact matching is supported by this combined formula and wildcards (* ?) are for finding partial matches.
What is the difference between VLOOKUP and Hlookup? ›The most commonly used LOOKUP functions in Excel are VLOOKUP and HLOOKUP. VLOOKUP allows you to search a data range that is set up vertically. HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns.