MATCH function

Function Type: Lookup and Reference


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.



Say we want to look into the Price of the Tool Box, The formula will be


This means that Excel will return the value in the intersection of 6th row and 2nd column of the array A2:C8.

Published by Cash Heaven

Excel Expert and Financial Analyst Excel / Google Sheets / Financial Modelling, Valuation and Analysis Effective communicator / High quality / Affordable / Reliable / Quick Turnover I've successfully completed over 200 financial modelling, valuation and analysis projects with start-up stage and corporate companies over the past 4 years. I love to solve problems, have an eye for design, and have built business excel templates. I fix Excel formulas in 10 minutes.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: