Function Type: Lookup and Reference
Description:
MATCH function searches for the specified lookup_value in a range or array then returns the relative location of that lookup_value in the range or array. It will always give us a number. This function is commonly used with INDEX function to give us a result same with VLOOKUP and HLOOKUP. MATCH can also be used with VLOOKUP and HLOOKUP through the 3rd argument, (column or row index number).
Function Structure:
=MATCH(lookup_value, lookup_array, [match_type])
Argument Breakdown:
lookup_value – This is the data that excel will lookup into the range or array. It may be an alphanumeric, numbers, or a function. There should only be 1 data / cell of lookup value per MATCH.
lookup_array – This is the range or array that excel will lookup into. It should contain the lookup_value.
match_type – This specifies how it will match the lookup_value in the lookup_array. Input here can -1, 0, or 1. This is an optional argument. When this argument is omitted, the default input here is 1.
Example:
Say we want to look into the Price of the Tool Box, The formula will be
=INDEX(A2:C8,6,2)
This means that Excel will return the value in the intersection of 6th row and 2nd column of the array A2:C8.