MATCH function



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:

Index

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.



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:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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: