One of the most commonly used formulas in Excel is the VLOOKUP formula. This is used to get details from one table and insert them into another table. If the records in the lookup table are always the same, then the VLOOKUP formula is a valuable tool. But what if you have more than one lookup table in Excel? For example, you have a table A with your contact information and a table B with your contact information. You want to find the contact information of the person who has the same phone number as the person in the lookup table.
VLOOKUP is one of the most useful Excel functions. Unfortunately, it has several limitations that can easily be broken. In this article, we will show you 3 VLOOKUP limitations that can be easily broken using some simple VLOOKUP tricks.
Excel has a function named “VLOOKUP” that can be used to find values within a range of columns, based on a value in a different column. However, it is very important to know that VLOOKUP is always slower than other methods for finding a specific value, and it is also not consistent in making sure which rows it should return.
VLOOKUP is one of Excel’s most popular functions.
Many Excel users who wish to look at and get data from a table will find it extremely useful (or dataset).
VLOOKUP isn’t perfect, as essential as it is. It does have certain drawbacks.
You can solve a lot of issues with Excel if you understand the VLOOKUP restrictions and the methods for getting past them.
Without further ado, here are some of the VLOOKUP function’s limitations and where to discover solutions.
VLOOKUP is unable to glance to the left (First Limitation)
For most Excel users, this seems to be the most significant VLOOKUP restriction.
Once the lookup value is found, the function is structured in such a manner that it can only get data from the columns on the right-hand side.
Consider the following data: the VLOOKUP function, for example, cannot look up a date in the 5th (or final) column and return the product that was sold on that day.
VLOOKUP is only intended to look in the correct direction after the lookup column, thus this is the case.
See How to use index-match to do a left lookup.
This is a restriction since it restricts your ability to display facts in the manner you want. If the column from which you want to obtain data is to the left of the lookup column, you’ll need to rearrange the columns in your data table to accommodate VLOOKUP.
A solution to the VLookup Limitation has been proposed.
Switching to INDEX-MATCH, one of VLOOKUP’s competitor procedures, is the best method to overcome this restriction.
When used simultaneously, the =INDEX() and =MATCH() methods may get around the VLOOKUP restriction.
You may use them together to do a lookup in the lookup table in either direction (which VLOOKUP can’t do).
Take, for example, the INDEX-MATCH, which allows you to search up a date and get matching data in any column to the left, such as product. Take a look at the screenshot below:
To learn more about how to use these functions for left lookups, see the following link.
VLOOKUP to the left using INDEX-MATCH is a useful connection.
VLOOKUP is unable to lookup two columns (Second Limitation)
Normally, VLOOKUP will check for your search word in the first column of your chosen range. What if you wish to get data from two columns at the same time? This is when you’ll run into trouble. Consider the following scenario:
Let’s suppose you’re looking for a client called David and want to know when you last sold to him.
When you look at the table above, you’ll see that David appears twice.
If you execute this job using VLOOKUP, it will utilize the first match and return 8-Jan-19 as a result.
This is technically true, and it is also valid if you are searching for David from the West area. If you’re searching for David in the North, though, you should be concerned about the outcome.
If only VLOOKUP could handle multiple criteria and get the Date based on the two columns (David, who hails from the North). This is another another flaw in the all-powerful VLOOKUP function. It is not feasible to use VLOOKUP 2 criterion.
This Vlookup Limitation has a proposed workaround.
You’ll need to utilize the following method to get past this limitation. This information came from trumpexcel.com’s Samit Bansal.
Making use of a helpful column
This method teaches a very basic concept. You want VLOOKUP to search across two different columns (which it cannot). Why not merge those two columns into one, then search that combined column instead?
After that, the combined column will be used as an assistance column (i.e. for the purpose of the lookup only).
See what I’m talking about below:
Now that you have a helper column, you can search across it using VLOOKUP.
However, since the helper column utilized it, you need add a hyphen (-) to separate the two values when providing the lookup value.
Take a look at this example:
VLOOKUP does not have the ability to look in both directions (Third Limitation)
Some may refer to this as a two-way lookup. Others will suggest doing a double lookup. It’s just a formula that looks in both vertical and horizontal directions at the same time, whatever you name it. Such a formula has both vertical and horizontal lookup capabilities.
If only these two functions (VLOOKUP and HLOOKUP) had been able to operate together, this job would have been much easier. However, when it comes to double lookup, both functions are disabled. So, let’s just call this another VLOOKUP function limitation.
A possible solution to this constraint
The INDEX-MATCH routines nearly always take precedence when VLOOKUP fails. You may gaze in both directions with these two functions.
As an example, consider the table below. Consider the following scenario: You want to create a formula that returns a student’s grades depending on the name and kind of exam you provided.
Based on the column number you provided, VLOOKUP may search the name column and return a student’s grades. The index number, on the other hand, may search through the name column as well as the heading row (first row) to obtain a student’s grades.
Take a look at this example:
When you look at the formula in the example above, you’ll see that it uses the INDEX and MATCH functions to conduct a two-way lookup. To obtain the row number and column number, it utilizes the MATCH function twice as parameters for the INDEX function.
Allow me to fine-tune the spreadsheet for you in order to better comprehend this. Take a look at the following:
However, see this article for a more comprehensive description of how to conduct a double lookup in Excel using the INDEX-MATCH functions: How to do a double lookup in Excel.
The formula used to get the marks in the above spreadsheet is as follows:
=INDEX(E2:I12,MATCH(A6,E2:E12,0),MATCH(B6,E2:I2,0))
To obtain someone’s grades, the spreadsheet user just has to input the student’s name and the exam, whether it’s test 1, 2, 3, or the final test, as seen above.
This worksheet’s formula is identical to the one used in the preceding one. All I did was refer to the numbers in cells A6 and B6 that are utilized as lookup values.
Conclusion
As you can see, the VLOOKUP function, despite its strength, has certain drawbacks.
You may, however, get past these issues and become unlimited by using the methods described in this book.
Thank you so much for taking the time to read this post.
VLOOKUP is a powerful tool. I remember being a little intimidated by it when I first started using Excel, as it seems to be one of those crazy functions you use for complex workbooks. VLOOKUP is great for situations where you have a large table with a fairly complex structure, like a database, that you want to achieve a different result for in another table.. Read more about vlookup with mixed text and numbers and let us know what you think.
Frequently Asked Questions
What are the limitations of VLOOKUP in Excel?
VLOOKUP is limited to a maximum of 255 characters.
How do you overcome limitations in VLOOKUP?
You can use the INDEX function to find a specific value in a list.
What is an alternative for VLOOKUP What are the limitations of this function?
VLOOKUP is a function that allows you to look up values in a table of data. It has limitations such as not being able to handle large tables, and it can only return one value at a time.
Related Tags
This article broadly covered the following related topics:
- vlookup example
- vlookup and hlookup examples
- vlookup in excel
- vlookup excel
- excel vlookup
