Excel MATCH Function
The Excel MATCH function searches for a specified value in a range and returns its position relative to the range
Example: Excel MATCH Function
|
=MATCH("Milk",$B$5:$B$11,0)
|
Result in cell D5 (2) - returns the position of Milk relative to the selected range.
|
|
=MATCH("Cereal",$B$5:$B$11,FALSE)
|
Result in cell D6 (4) - returns the position of Cereal relative to the selected range.
|
|
=MATCH("banana",$B$5:$B$11,0)
|
Result in cell D7 (6) - returns the position of banana relative to the selected range.
|
|
=MATCH("*ple",$B$5:$B$11,0)
|
Result in cell D8 (5) - returns the position of a string ending in ple relative to the selected range.
|
|
=MATCH("Ch*",$B$5:$B$11,0)
|
Result in cell D9 (3) - returns the position of a string starting with Ch relative to the selected range.
|
|
=MATCH("*cre*",$B$5:$B$11,0)
|
Result in cell D10 (7) - returns the position of a string containing cre relative to the selected range.
|
|
=MATCH("?????",$B$5:$B$11,0)
|
Result in cell D11 (1) - returns the position of the first five character string relative to the selected range.
|
METHOD 2. Excel MATCH function with links
EXCEL
|
=MATCH(C5,$B$5:$B$11,0)
|
Result in cell D5 (2) - returns the position of the value in cell C5 relative to the selected range.
|
|
=MATCH(C6,$B$5:$B$11,FALSE)
|
Result in cell D6 (4) - returns the position of the value in cell C6 relative to the selected range.
|
|
=MATCH(C7,$B$5:$B$11,0)
|
Result in cell D7 (6) - returns the position of the value in cell C7 relative to the selected range.
|
|
=MATCH(C8,$B$5:$B$11,0)
|
Result in cell D8 (5) - returns the position of the value in cell C8 relative to the selected range.
|
|
=MATCH(C9,$B$5:$B$11,0)
|
Result in cell D9 (3) - returns the position of the value in cell C9 relative to the selected range.
|
|
=MATCH(C10,$B$5:$B$11,0)
|
Result in cell D10 (7) - returns the position of the value in cell C10 relative to the selected range.
|
|
=MATCH(C11,$B$5:$B$11,0)
|
Result in cell D11 (1) - returns the position of the value in cell C11 relative to the selected range.
|
METHOD 3. Excel MATCH function using the Excel built-in function library with hardcoded values
EXCEL
Formulas tab > Function Library group > Lookup & Reference > MATCH > populate the the input boxes
| =MATCH("Milk",$B$5:$B$11,0) Note: in this example we are populating all of the input boxes associated with the MATCH Function arguments, however, you are only required to populate the required arguments (Lookup_value and Lookup_array). |
 |
METHOD 4. Excel MATCH function using the Excel built-in function library with links
EXCEL
Formulas tab > Function Library group > Lookup & Reference > MATCH > populate the the input boxes
| =MATCH(C5,$B$5:$B$11,0) Note: in this example we are populating all of the input boxes associated with the MATCH Function arguments with links, however, you are only required to populate the required arguments (Lookup_value and Lookup_array). |
 |
Sub Excel_MATCH_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("MATCH")
'apply the Excel MATCH function
ws.Range("D5").Value = WorksheetFunction.Match("Milk", Range("$B$5:$B$11"), 0)
ws.Range("D6").Value = WorksheetFunction.Match("Cereal", Range("$B$5:$B$11"), False)
ws.Range("D7").Value = WorksheetFunction.Match("banana", Range("$B$5:$B$11"), 0)
ws.Range("D8").Value = WorksheetFunction.Match("*ple", Range("$B$5:$B$11"), 0)
ws.Range("D9").Value = WorksheetFunction.Match("Ch*", Range("$B$5:$B$11"), 0)
ws.Range("D10").Value = WorksheetFunction.Match("*cre*", Range("$B$5:$B$11"), 0)
ws.Range("D11").Value = WorksheetFunction.Match("?????", Range("$B$5:$B$11"), 0)
ws.Range("D5").Value = WorksheetFunction.Match("Milk", Range("$B$5:$B$11"), 0)
ws.Range("D6").Value = WorksheetFunction.Match("Cereal", Range("$B$5:$B$11"), False)
ws.Range("D7").Value = WorksheetFunction.Match("banana", Range("$B$5:$B$11"), 0)
ws.Range("D8").Value = WorksheetFunction.Match("*ple", Range("$B$5:$B$11"), 0)
ws.Range("D9").Value = WorksheetFunction.Match("Ch*", Range("$B$5:$B$11"), 0)
ws.Range("D10").Value = WorksheetFunction.Match("*cre*", Range("$B$5:$B$11"), 0)
ws.Range("D11").Value = WorksheetFunction.Match("?????", Range("$B$5:$B$11"), 0)
End Sub
OBJECTS
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
PREREQUISITES
Worksheet Name: Have a worksheet named MATCH.
Worksheet Name: Have a worksheet named MATCH.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5"), ("D6"), ("D7"), ("D8") and ("D9") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Excel MATCH function using VBA with links
VBA
Sub Excel_MATCH_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("MATCH")
'apply the Excel MATCH function
ws.Range("D5").Value = WorksheetFunction.Match(Range("C5"), Range("$B$5:$B$11"), 0)
ws.Range("D6").Value = WorksheetFunction.Match(Range("C6"), Range("$B$5:$B$11"), False)
ws.Range("D7").Value = WorksheetFunction.Match(Range("C7"), Range("$B$5:$B$11"), 0)
ws.Range("D8").Value = WorksheetFunction.Match(Range("C8"), Range("$B$5:$B$11"), 0)
ws.Range("D9").Value = WorksheetFunction.Match(Range("C9"), Range("$B$5:$B$11"), 0)
ws.Range("D10").Value = WorksheetFunction.Match(Range("C10"), Range("$B$5:$B$11"), 0)
ws.Range("D11").Value = WorksheetFunction.Match(Range("C11"), Range("$B$5:$B$11"), 0)
ws.Range("D5").Value = WorksheetFunction.Match(Range("C5"), Range("$B$5:$B$11"), 0)
ws.Range("D6").Value = WorksheetFunction.Match(Range("C6"), Range("$B$5:$B$11"), False)
ws.Range("D7").Value = WorksheetFunction.Match(Range("C7"), Range("$B$5:$B$11"), 0)
ws.Range("D8").Value = WorksheetFunction.Match(Range("C8"), Range("$B$5:$B$11"), 0)
ws.Range("D9").Value = WorksheetFunction.Match(Range("C9"), Range("$B$5:$B$11"), 0)
ws.Range("D10").Value = WorksheetFunction.Match(Range("C10"), Range("$B$5:$B$11"), 0)
ws.Range("D11").Value = WorksheetFunction.Match(Range("C11"), Range("$B$5:$B$11"), 0)
End Sub
OBJECTS
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
PREREQUISITES
Worksheet Name: Have a worksheet named MATCH.
Worksheet Name: Have a worksheet named MATCH.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5"), ("D6"), ("D7"), ("D8") and ("D9") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
DESCRIPTION
The Excel MATCH function searches for a specified value in a range and returns its position relative to the range.
The Excel MATCH function searches for a specified value in a range and returns its position relative to the range.
SYNTAX
=MATCH(lookup_value, lookup_array, [match_type])
=MATCH(lookup_value, lookup_array, [match_type])
ARGUMENTS
lookup_value: (Required) The value that you intend to search for in the relevant range (lookup_array).
lookup_array: (Required) Range of cells from where you intend to search for the lookup_value.
match_type: (Optional) This specifies how the function will match the lookup_value to the lookup_array. The options that are available for this match type are:
1 - Locates the larges value that is less than or equal to the lookup_value from the lookup_array. This is the Default option if match_type is omitted.
0 - Locates the first value that exactly matches the lookup_array
-1 - Locates the smallest value that is greater than or equal to the lookup_value from the lookup_array.
lookup_value: (Required) The value that you intend to search for in the relevant range (lookup_array).
lookup_array: (Required) Range of cells from where you intend to search for the lookup_value.
match_type: (Optional) This specifies how the function will match the lookup_value to the lookup_array. The options that are available for this match type are:
1 - Locates the larges value that is less than or equal to the lookup_value from the lookup_array. This is the Default option if match_type is omitted.
0 - Locates the first value that exactly matches the lookup_array
-1 - Locates the smallest value that is greater than or equal to the lookup_value from the lookup_array.
ADDITIONAL NOTES
Note 1: * searches to find a match for the sequence of characters.
Note 2: ? searches to find a match to any single character.
Note 3: The MATCH function is not case-sensitive.
Note 4: If the MATCH function cannot locate the specified value from the selected range it will return an #NA error.