This post details some of the common limitations you will encounter while using the Vlookup function.
Following are some of the limitations of the Great Vlookup
- Returns data related to only the first occurrence of the lookup value
- Work with only one criterion
- Returns an error when no match of the lookup value is found
- The lookup value is required to be in the first column of the array
Vlookup can return data related to only the first occurrence of the lookup value
Let’s take an example of the following menu. Notice that there are two Classic Chai in the menu. One appears under the Hot Chai category and the other under Iced Chai. If we use a Vlookup to get the price of Classic chai, the price of the first occurrence (under Hot Chai) is pulled without knowing that there was another type of Classic Chai (under Iced Chai) listed in the menu. A typical Vlookup cannot be used to get the price of the Classic Iced Chai from the following menu. Although we could manipulate the data to get the required data point.
Works with only one criterion
As you would have noticed, Vlookup has place for only one lookup value. Therefore, it works with only one criterion. To extend the above example, let us see a scenario where we had the chai category in the first column and the chai name in the second column. This provides us an opportunity to pull the price of the required chai using both category and item name as criteria.
In simple words, I should be able to pull the price of the Classic Chai in the Iced Chai category with both of them laid out as in the following snippet. Due to the limitation of Vlookup having provision for only one lookup value, it cannot be done through a simple Vlookup. I could lookup either Classic Chai or Iced Chai but not both. Although by design, Vlookup doesn’t allow two criteria, we could manipulate our lookup value to achieve the target. The following lookup can be achieved, if we combine the category and item name into one helper column creating unique lookup criteria.
Returns an error when no match of the lookup value is found
Vlookup returns a #N/A error if the lookup value is not available in the lookup array. In the following example, “English Tea” doesn’t appear in the menu and therefore, Vlookup doesn’t have any price to return from the array. The #N/A error needs to be separately handled. One way to handle the error is to encapsulate the Vlookup in an Iferror formula. The first argument of the Iferror needs to be the Vlookup formula. The second argument needs to be the value to be returned in case of an error. In this case if English Tea is not in the menu, we can display a message “Item Not Available”.
IFERROR(VLOOKUP(H6, $A$2:$D$11, 3, 0), “Item Not Available”)
With the Vlookup encapsulated in the Iferror, the result is presented as “Item Not Available” as seen in H10 in the snippet below.
Lookup value is required to be in the first column of the array
By default, Vlookup looks for the lookup value in the first column of the array. There is no argument in a Vlookup which defines a different column to lookup. For example, in the following snippet, it is possible to lookup name using the phone number. But it is not possible to lookup the phone number using the name. Although this could be achieved using a combination of Vlookup and Choose functions. We would discuss the topic in another blog post.
Through the above examples, I have attempted to highlight the limitations while using Vlookups. Although it is possible to overcome many of them through manipulations, it may not be worthwhile doing those manipulations in all scenarios.
Hope you had a good tea while learning!
Vlookup is one of the most commonly used functions of excel. There are numerous data lookup tasks that we want to do and vlookup does them just as a charm. Before getting into the syntax of the function, let us first understand what it really does.
We use the vlookup function in our head all the time. It may be while shopping for our favorite products on amazon, reading the table of contents of a book to find the page number, reading a train status on the railway station or while looking for the price of a tea at our favorite tea shop. Below is a snippet of the beverage menu of my favorite tea shop, Chai Point. How would you find the price of the Classic Chai which serves 3-4 people?
If we had to list down the steps involved, it would be as follows:
- Scroll down the Menu to find the Classic Chai
- Scroll to the right to the column where price for the required size is noted
- Read the price!
Well! This is exactly what Vlookup in excel does!
A few points to note though:
- The above menu data is organized in a vertical format, therefore, Vlookup (read Vertical lookup)
- Classic Chai appears only once in the above menu
- If the Classic Chai appeared twice, your mind (or rather excel Vlookup) would have stopped on the first instance it saw Classic Chai
- The product name “Classic Chai” appeared in the left most column of the menu.
Now that we understand what Vlookup actually does, lets now see how it is implemented in excel.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Lookup_value: The reference to value that you are looking for. In the example above, we are finding out the price of Classic Chai. Hence, “Classic Chai” is the lookup value.
- Table_array: The array of data having the value that you are looking. For the above example, the whole menu is our table array. Refer below snippet having the menu in excel format. Region A2:D11 should be the table array for this example.
Point to note: The column having the lookup value should be the leftmost column of the table array
- Col_index_num: Column number of the value in the table array. This field tells excel which value to pull once the lookup value is found. Once excel finds “Classic Chai” in the first column of the table array, it should pull the value in the third column of the array to get the price of Classic Chai of size “Serves (3-4)”.
Point to note: The number of columns should be counted including the first column having the lookup value
- Range_lookup: Exact match (0 or False) or Approximate match (1 or True). In this field, you have to tell excel that it should exactly match the lookup value when looking for the lookup value. This field is useful while looking up a sorted list when the lookup value is numerical and a range of value should give the same result. Will discuss this more in a separate post.
In simple plain English:
VLOOKUP(value you are looking up,
table region in which the value is available,
the column from which result should be pulled,
having an exact match)
With the above understanding, let us build the formula to pull the price of “Serves (3-4)” size Classic Chai.
Vlookup(“Classic”, A2:D11, 3, False)
And you have the desired price!!!
In the above example, try replacing the hard-coded menu item “Classic” with a cell reference where you have the criteria listed. In the snippet below, the lookup value is placed in cell “H4”. Therefore, replace the criteria “Classic” with the cell reference H4.
Notice, in the following snippet, I have used “0” instead of “False” for the range_lookup argument.
I hope this helps you to understand how Vlookup works. Follow the other posts to get into more detail of the tricks and limitations with Vlookup.
Hope you had a good tea while learning!