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