Microsoft Excels VLOOKUP Function is perhaps one of the most popular, more advanced, functions. Any time the conversation turns to Excel functions, during my live Excel classes, VLOOKUP tends to be one of the more sought after topics to learn.
A brief introduction for those that haven’t worked with Excel’s VLOOKUP function.
Purpose of the VLOOKUP
There are a few different reason why someone would use the VLOOKUP function in an Excel Worksheet. According to Microsoft, the general purpose of the VLOOKUP is:
The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.
Take a look at the following example.
Imagine you’ve been given a list of Employee ID’s, see column K above. You have been asked to find the respective DEPT values for each of the EMP ID’s. The information needed is found in the list to the left. This information could be on another worksheet or even in a completely different workbook.
Using the ID’s found in column K, you could use the VLOOKUP to search the first column in the list on the list, column A, for a matching ID. Once a match is found you can direct the VLOOKUP to return a value from a specific column, DEPT value.
General VLOOKUP Syntax
=VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP])
- LOOKUP_VALUE: The Value you are searching for in the first column of the master list
- TABLE_ARRAY: Reference to the table that you are searching
- COL_INDEX_NUM: Which column you want to return the value from
- [RANGE_LOOKUP]: OPTIONAL, find an exact match (FALSE) or the closest match (TRUE or OMITTED)
I got a question from a student in my Excel course on Udemy about using the VLOOKUP to lookup records that match two requirements. The VLOOKUP relies on a value, such as the EMP ID, in order to search and return a value. But, what if a single value is not unique enough to get the job done.
Searching Products with the VLOOKUP
In the above example, there are two lists. The one of the right represents all products. the list on the right represents products that are on the shelf. Using the list on the left I want to search the list on the left to find where they match in order to see which products are currently on the shelf out of all the products.
This sounds like a good use case for Excel’s VLOOKUP. But, looking at the list on the left closer, you will see that PRODUCT ID’s are duplicated. Each PRODUCT ID shows up potential multiple times as there are different colors for that product. Using the PRODUCT ID only is not enough.
In the above example it is necessary to create a unique value that can be used to identify each product. The combination of PRODUCT ID and COLOR, creating a unique value, can be used to identify each record.
In the above example, using the “&” character you can combine two, or more, cell values in order to create a new value. In this case, taking the PRODUCT ID and COLOR, in order to create a new unique value.
Next, using the VLOOKUP function, combined with an IF and the ISERROR function, you can now identify which records match between the two lists.
When you don’t have the necessary data needed, sometimes you need to create it yourself.