Excel Functions Tutorials
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- HLOOKUP Formula in Excel
- HLOOKUP Examples
- Hyperlink Excel Function
- Hyperlink Formula in Excel
- INDIRECT Function in Excel
- LOOKUP Excel Function
- LOOKUP Formula in Excel
- Match Excel Function
- MATCH Formula in Excel
- How to Match Data in Excel?
- VLOOKUP Excel Function
- VLOOKUP Formula
- VLOOKUP Tutorial in Excel
- VLookup in VBA Excel
- VLOOKUP in Pivot Table
- VLOOKUP with SUM
- VLOOKUP with Match
- SUMIF With VLOOKUP
- VLookup with IF Statement
- Vlookup to the Left
- VLOOKUP from Another Sheet / Workbook
- VLOOKUP Examples in Excel
- VLOOKUP Table Array
- VLOOKUP vs HLOOKUP
- INDEX Excel Function
- INDEX Formula in Excel
- Indirect Formula in Excel
- TRANSPOSE Excel Function
- Row Function in Excel
- Rows Function in Excel
- OFFSET Excel Function
- OFFSET Formula in Excel
- VLOOKUP with Multiple Criteria
- IFERROR with VLOOKUP in Excel
- Fixing VLOOKUP Errors
- Alternatives to Vlookup
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
MATCH Function in Excel (Table of Contents)
MATCH in Excel
The Excel MATCH function is simple lookup function used in Excel. MATCH Function is used to find out the position of a lookup value in a lookup array (row, column, or table). MATCH function search the lookup_value in lookup_array for approximate and exact matching, and also supports (*&?) for partial matches values. Excel Match function often used with index formula to find the lookup value.
Basically, MATCH function returns a relative position of a lookup value in a range of cells. In simple words Excel MATCH function searches for a lookup_value in a lookup_array and provides the relative position of that lookup_value.
MATCH Formula in Excel
The MATCH Formula in excel is as follows
Explanation of MATCH Function in Excel
MATCH Formula in Excel has three parameter two (lookup_value,lookup_array) are compulsory parameters and one (match_type) is optional.
- lookup_value: lookup value is value which you want to search in lookup array. Suppose you want to search a word in dictionary than that particular word will be your lookup value and dictionary will be your lookup array.
- lookup_array: lookup_array is the set of data where the lookup value is located and your search applied in lookup array.
- [match_type]: :match_type is limited to -1, 0, or 1, depending on the type of ‘MATCH’.
- It will be 0 for the first exact match lookup value.
- 1 for the largest value < or = toour ‘lookup_value’.
- And -1 for the smallest value that is > or = toour ‘lookup_value’.
How to use MATCH Function in Excel?
MATCH formula in excel is very simple and easy to use. Let understand the working of Excel Match Formula by some MATCH Excel examples.
MATCH Excel Example #1
Consider a MATCH Excel example for exact match value. Suppose you have a data table with S.N., Name, and Dept. name and you have to find out the position of an employee whose name is ‘Tanuj’ then you can use MATCH Formula in excel with 0 match type to return the exact position of ‘Tanuj’ in the table array.
= MATCH(F4,$B$4:$B$13,0) the output is 1.
MATCH Excel Example #2
Consider the MATCH Excel example for approximate match value. Suppose you have a list of values wherein you need to find out the approximate position of any value here we have searched for ‘525’ and it will return an approximate position for it.
= MATCH(E19,B19:B28,1) output will be 5.
MATCH Excel Example #3
Match in Excel support (? & *) wildcard characters search in lookup value. You can understand MATCH Formula in Excel by using the below MATCH Excel example.
=MATCH(“*”&E33,$B$33:$B$42,0) the output will be 5.
MATCH Excel with INDEX Function – Example 4
MATCH in Excel with index function is used to lookup value in table from right to left.
Suppose you have to find out the S.N. of any employee by their name then you can achieve it by using a combination of excel match index function.
=INDEX(B46:D56,MATCH(G48,C46:C56,0),1) an output will be 1.
Things to Remember
- Match is not case-sensitive
- It considers tanuj and Tanuj as same value means it does not distinguish b/w lower case and upper case.
- MATCH in Excel returns the #N/A error if there is no matching value is found in the lookup_array.
- The Excel MATCH function returns the position value of the lookup value in the lookup_array, not the value itself.
- If there are multiple match values in lookup_array then it will return first exact match value.
- If match_type is 0 and lookup_value is text, then this function supports the use of wildcard characters question mark (?) and asterisk (*) in lookup_value.
This has been a guide to MATCH Excel Function. Here we discuss the MATCH Formula in excel and how to use the MATCH Excel with INDEX function along with practical MATCH Excel examples and downloadable excel templates. You may also have a look at these other lookup and reference functions in excel.