Match formula is used for referencing among the cell values, this is an inbuilt function in excel which tells us the position of a certain value in a range of given values from a range or group of cells, Match formula is usually used with index function as a replacement for the VLOOKUP function.
Excel Match Formula (Table of Contents)
What is MATCH Formula in Excel?
If you have a certain value and in the main data sheet how do you tell exactly which row or column it resides. For example, look at the below data series.
From the above list, the position of the product D is 4th row. This looks easy, isn’t it? But when you have hundreds of rows and columns how do you identify manually. It is almost an impossible task to do.
Fortunately, in excel, we have a built-in function to find the lookup value’s position in the selected range of values.
MATCH excel formula matches the lookup value in the main database of in the main data table and returns the position of the lookup value. MATCH is one of those important lookup functions in excel. It helps us to identify the position of the value in the array.
The Syntax of MATCH Formula in Excel
MATCH formula in excel includes 3 parameters in the syntax.
- Lookup Value: For this value, we are trying to find the position in the main table. In simple terms, we want the position for the available value.
- Lookup Array: This is the data array i.e. main table data range.
- [Match Type]: What should be the math type whether it is approximate or exact? If you want approximate match 1 is the argument or else 0 is the argument.
How to Use MATCH Formula in Excel?
Below are the examples of MATCH Formula in excel.
To understand it practically let’s apply a simple formula. Assume you have product list and sales values in excel.
From this identify the position of Product D.
Step 1: Open MATCH function first.
Step 2: Select the lookup as D2 cell. Lookup array is nothing but our lookup value’s column in the main table. Lookup values column in the main table is A2 to A7.
Step 3: Match type should be exact, so select zero (0) as the argument and close the bracket.
Step 4: Hit the enter key. We will have the position of Product D from the range A2 to A7.
So, D is there in the 4th row from A2 cell. If the range is supplied from A1 then the result would have been 5, since we have selected the lookup array from the second row it is showing as 4.
Example #2 – MATCH Formula with Wildcard Characters
In excel wildcard characters are a question mark (?) and asterisk (*).
Question Mark (?) will replace any single character
An Asterisk (*) will replace any order or character.
MATCH formula with wildcard characters can be best fitted to use when you want to match an only specific set of the string, not the full string. For example, look at the below data.
Now using asterisk I will apply MATCH Formula and see what happens.
This MATCH Formula will give you the result as 2.
Even though original Kumar is there in A6 cell i.e. 5th row, formula returned the result as 2 instead of 5. Since we have used asterisk along with the lookup value it has found the first partial character Kumar Abhishek as the result, so returned 2 as the answer.
In order to overcome this issue, I have modified the MATCH formula. I have used an asterisk before the lookup value.
Now it has returned 5 as the answer.
Example #3 – MATCH Formula with VLOOKUP Function
I hope you are known VLOOKUP function. When you apply VLOOKUP we require to mention the column index number i.e. from which column actually we want the data to be extracted.
For example, look at the below image.
I have applied VLOOKUP to extract North region’s sales value. I have mentioned the column index number as 3. Instead of entering this as 3 actually, we can automate this by using the MATCH formula in excel.
Apply the VLOOKUP function and stop right at the column index number.
Instead of manual column number let’s make it dynamic by using MATCH formula in excel. Open MATCH excel function now.
Select lookup value as Sales i.e. F2 cell.
Now select the lookup array as our table headers right from where our table array starts.
Now mention the match type as exact i.e. 0. Close the bracket for MATCH function and enter range lookup as FALSE i.e. exact match for VLOOKUP function.
Close bracket for VLOOKUP formula and hit enter it will return sales value for North Region.
Now change the heading from Sales to Product in the F2 cell.
Wow!!! Superb it has returned me product name as well without changing the column index number because MATCH Formula has returned the column index number for me.
Things to Remember
- Lookup value should be accurate as it is in the lookup array.
- If the lookup value is correct we will get #N/A error.
- When the wildcard characters used match type should be exact i.e. zero (0)
This has been a guide to MATCH Formula in Excel. Here we discuss how to use MATCH Formula in Excel with practical examples and downloadable excel template. You may learn more about excel from the following articles –