# Using Match with Vlookup

The Match function can be used within the Vlookup function to create a dynamic column index number. Let’s look at why this can be useful beginning with a quick review of VLOOKUP.

Vlookup - Looks vertically down a list to find a record and returns information related to that record

The syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The example below shows the VLOOKUP function being used to extract the Stock Level and Stock Value information of specific products from the data table.

Using VLOOKUP to return data from a record in a range of cells

The function used for the Stock Level in cell H7 is;

=VLOOKUP(\$H\$4, \$A\$3:\$F\$9, 5, FALSE)

If columns are inserted in the future, the VLOOKUP function begins to extract the wrong data from the table.

The image below shows a column inserted into the previous example. The VLOOKUP function entered in cell H7 to return the stock level is still returning the content of column 5, which is now the products price.

VLOOKUP function returning incorrect data

Now wouldn’t it be great if VLOOKUP could recognise that the Stock Level and Stock Value columns have moved, and change automatically to continue to extract the correct data. Let’s introduce the MATCH function.

Match - Returns the position of a value in a list

=MATCH(lookup_value, lookup_array, [match_type])

The MATCH function can be entered inside the VLOOKUP function to return the position of the required column. This ensures that if more columns are inserted, or if the cell is copied, the function continues to work efficiently.

The revised function for cell H7 will be;

=VLOOKUP(\$I\$4, \$A\$3:\$G\$9, MATCH(I6, \$A\$3:\$G\$3, 0), FALSE)

This formula can be copied across to H8 and will work equally well at returning the product’s stock value.

Using the MATCH function with VLOOKUP

The MATCH function is being used to look at the cell above where the formula is entered, then find that value in the row of headings in cell range A3:G3, and return its position as a number.

Use the INDEX and MATCH functions in Excel

The INDEX and MATCH functions can be used to lookup and retrieve data from any column of an Excel table. These functions are seen as an alternative to the VLOOKUP function.

Excel Functions

This page is dedicated to listing the most useful Microsoft Excel functions. Explanations on how to write each function and examples of its use are provided.

Use the Large function in Excel to find and return the 2nd or 3rd largest number in a list.

## More by this Author

lauralolita 6 years ago from Florida

Great info! I had to use VLookup in one of my college courses and you explained it very clearly! Thanks

almurray 6 years ago from Ipswich, United Kingdom Author

Thanks laura, much appreciated.

Hemant Kumar Katiyar 5 years ago

That's great.

Thanks for you help.