Match/Index – Best functions ever!

OK — there is a lot of competitions for the best Excel functions. However, Match and Index have to be near the top of any accountant’s list.

There are times that a VLOOKUP, just won’t cut it. Remember, a VLOOKUP requires the lookup value is in the first column of the table array. Sometimes, that just isn’t possible. There are some cases in which you are rearrange the data, but not always. That is where a combination of MATCH and INDEX saves the day!

In the following example, you cannot use a VLOOKUP to lookup the Item Number, using the Vendor Part Number. The Venter Part Number is not in the first column.

Excel Inventory Table

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. The INDEX function returns a value, specified by its relative position in a range, from within a range of cells. In other words, MATCH is used to find an item and INDEX is used to return the value of the item.

The MATCH function has the following syntax:

MATCH(lookup_value, lookup_array, [match_type])

The INDEX function has the following syntax:

INDEX(array, row_number, column_number) /html

Using the table above, the following example shows a comparison of the VLOOKUP and the MATCH/INDEX functions.

Match/Index formulas

To see a short video for further explanation, go to: https://youtu.be/U_g6grN7Ndg

Leave a comment

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: