ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Using Match with Vlookup

Updated on May 6, 2011

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

Comments

    0 of 8192 characters used
    Post Comment

    • lauralolita profile image

      lauralolita 7 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
      Author

      Alan Murray 7 years ago from Ipswich, United Kingdom

      Thanks laura, much appreciated.

    • profile image

      Hemant Kumar Katiyar 5 years ago

      That's great.

      Thanks for you help.

    Click to Rate This Article