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!