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
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
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
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.


Useful Links

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.

Find the 2nd largest number in a list

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

Use the Match Function with Vlookup Video

More by this Author

  • Simulate a Cup Draw using Excel
    0

    Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...

  • Create Star Wars Movie Credits in PowerPoint
    2

    This hub will explain how to create the scrolling text credits from the Star Wars movies in your PowerPoint presentation. To create the Star Wars credits we will use different animation effects simultaneously. ...

  • Brock Lesnar Nutrition and Workouts
    2

    Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...


3 comments

lauralolita profile image

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 profile image

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.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working