Buzz
The VLOOKUP function in Excel is always the ideal choice when users need to retrieve information from a spreadsheet. Parts 1 and 2 of the 23 Things Everyone Should Know About VLOOKUP series have been introduced by Mytour, and here are 23 Things Everyone Should Know About VLOOKUP: Part 3, the final part providing you with essential knowledge about this function.
- 23 Things About VLOOKUP Part 1
- 23 Things About VLOOKUP Part 2
15. Utilize the ROW or COLUMN function to calculate column indices
Feeling annoyed after altering a copied formula? You can employ the ROW or COLUMN function to create dynamic column indices. With data from contiguous columns, this trick allows you to set up a VLOOKUP formula and copy it without any changes.
For instance, with the employee data below, you can use the COLUMN function to generate dynamic column indices. For the first formula in cell C3, COLUMN returns the result as 3 (since column C is the third column in the spreadsheet), so simply subtract 1, and copy the formula across the remaining cells:
All formulas remain the same and require no adjustments.
The formula you're using is:
=VLOOKUP(id,data,COLUMN()-1,0)
16. Use VLOOKUP + MATCH for comprehensive dynamic column indices
Additionally, to enhance the above trick, you can employ the MATCH function to search for the position of a column in a table and return the result as a complete dynamic column index.
This is also known as two-way lookup because you're searching both rows and columns.
You can apply this trick to find the sales revenue of a specific employee in a particular month, or to search for the price of a certain product from a specific supplier.
For example, suppose your sales revenue is divided by salesperson:
Another thing everyone should know about VLOOKUP is that while VLOOKUP can easily find the name of the salesperson, there's no way to handle automatic month names. The solution here is to use the MATCH function to replace the static column index.
The MATCH function will return the ordinal number of the column to fetch the lookup value, encompassing all columns in the table to 'sync' with the column index used in the VLOOKUP function.
=VLOOKUP(H2,data,MATCH(H3,months,0),0)
Note: You should utilize both methods of lookup with INDEX and MATCH. Both methods are flexible and perform well when working with large datasets.
17. VLOOKUP allows wildcards for partial matching
Whenever using the VLOOKUP function in exact match mode, you have the option to utilize wildcard characters within the lookup value.
Excel provides 2 wildcard characters: the asterisk (*) matches one or more characters, and the question mark (?) matches a single character. You can directly input these symbols into a cell and treat them as a lookup value with VLOOKUP.
In the example below, entering 'Mon *' into cell H3, with a named range called 'val', VLOOKUP will return the result as 'Monet'.
The formula in this case is quite simple:
=VLOOKUP(value,data,1,0)
If desired, you can adjust the VLOOKUP formula to use appropriate wildcard characters, in the example below, simply concatenate the value in H3 with an asterisk.
In this case, the VLOOKUP formula to append the asterisk (*) to the lookup value would be:
=VLOOKUP(value&'*',data,1,0)
Note: Be cautious with wildcard characters and VLOOKUP. This trick can give you an unexpected result.
18. You can catch #N/A errors and display a friendly message
When in exact match mode, VLOOKUP will display the #N/A error message if it can't find a matching result. This error informs you that the lookup value couldn't be found in the lookup table. However, you can catch this error and display a different message instead.
The #N/A error appears when VLOOKUP can't find a matching lookup value. This error indicates that the desired result couldn't be found in the lookup table. In the example below, with the beverage table, Latte doesn't exist so VLOOKUP returns the #N/A error.
The standard formula to find an exact match in the given scenario is:
=VLOOKUP(E6,data,2,0)
If you want to display a more friendly message instead of the #N/A error, you can easily do so.
The simplest way is to nest the VLOOKUP function within the IFERROR function. The IFERROR function allows you to 'catch' any error and return the result you want.
To catch the #N/A error and display the 'not found' error message, you can wrap the original formula in an IFERROR function and set the result you want:
If the lookup value is found, no error occurs, and the VLOOKUP function returns the normal result. Here's the formula:
=IFERROR(VLOOKUP(E6,data,2,0),'Not found')
19. Numbers displayed as text can cause lookup errors
In some cases when working with VLOOKUP, it may contain numbers entered as text. If simply extracting numbers as text from a column in the table, this isn't important.
But if the first column in the table contains numbers entered as text, you'll get a #N/A error if the lookup_value is not text.
In the following example, the ID numbers in the Planet table are entered as text, which causes the VLOOKUP error message #N/A when the lookup_value in cell H3 is entered as the number 3:
To overcome this issue, ensure that the lookup value and the first column of the table have the same data type (either both number or both text).
To achieve this, you convert the values in the lookup column to numbers by adding a zero when using the Paste Special feature.
If you can't control the lookup table, you can also adjust the VLOOKUP formula to convert the lookup values to text by concatenating '' to the formula as follows:
=VLOOKUP(id&'',planets,2,0)
If you're unsure whether the data in the lookup table is in text or number format, there's still a way to cater to both options. By wrapping the VLOOKUP function in an IFERROR function and writing a formula to handle both cases:
=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&'',planets,3,0))
20. Using VLOOKUP to replace nested IF functions
One interesting point is that VLOOKUP can replace nested IF functions in Excel. If you've ever worked with formulas involving nested IF functions in Excel, you'll know how well these formulas work, but the downside is that nested IF functions use many parentheses.
Also, you should be careful about the order of parentheses when building a formula, to ensure that no logical errors occur.
Nested IF functions are typically used to calculate letter grades based on existing numerical grades. In the example below, you can see a formula built from nested IF functions to calculate letter grades based on numerical grades:
The formula of nested IF functions in the example above is:
=IF(C5<><><><>
The formula works well but note that both the logic and the numerical grades must be incorporated into the formula. If for any reason you need to modify the calculation formula, you'll have to update the formula carefully and then copy the formula down the entire table.
In contrast, VLOOKUP can assign the same grade with a simple formula. All you need to do is ensure the grade table is set up for VLOOKUP (meaning it's sorted by score from smallest to largest and contains parentheses to handle all scores).
After defining the data range name for the letter grade table as 'key', you have a straightforward VLOOKUP formula returning the letter grade result similar to the original nested IF functions:
=VLOOKUP(C5,key,2,TRUE)
Furthermore, both the logic and the score values are not directly incorporated into the formula, but rather placed in cells and tables. Therefore, if there are any changes, you just need to update them directly in the table, and the VLOOKUP formulas will automatically adjust.
21. VLOOKUP can only handle a single condition
VLOOKUP is designed to find values based on a single condition, provided as a lookup value to search in the first column of the table (the lookup column).
This means you can't easily perform tasks like finding an employee named 'Smith' in the 'accounting' department, or searching for an employee based on both first and last names in two separate columns.
However, there are still some ways to overcome this limitation. One of them involves creating a helper column to concatenate values from other columns and generate search values, then handling them similar to multiple conditions.
For instance, you want to find the department and group of an employee, but the first and last names appear in two separate columns. So how do you look up both at once?
First, add a helper column to concatenate the first and last names together:
Next, configure VLOOKUP to use the spreadsheet including this new column and concatenate the first and last names for the lookup value:
The VLOOKUP formula to search for both first and last names using the helper column looks like this:
=VLOOKUP(D3&C3,data,4,0)
22. Using 2 VLOOKUP functions is faster than 1 VLOOKUP function
It may sound a bit crazy, but when dealing with large datasets that require precise results, you might consider using 2 VLOOKUP functions to speed up the process.
Imagine you have a lot of order data, let's say over 10,000 orders, and you're using VLOOKUP to look up the total number of orders based on the order ID. Suppose your formula looks like this:
=VLOOKUP(order ID,data, 5, FALSE)
The FALSE parameter at the end of the formula forces VLOOKUP to search for an exact match because in this case, some orders might be missed. With exact match type, VLOOKUP would return an #N/A error in such cases.
The issue with exact match type is that it makes VLOOKUP function very slow because Excel has to linearly go through all values until it finds the corresponding result. On the contrary, approximate match type would be much faster as Excel can perform binary search.
Another issue is that with approximate match type, VLOOKUP may return incorrect results when a value is not found. Moreover, the result might look perfectly fine, making it very difficult for you to detect errors.
The solution here is to use VLOOKUP twice, both in approximate match type. The first VLOOKUP simply checks if the value actually exists. The other VLOOKUP (also in approximate match type) retrieves the data you want. Otherwise, you can return any value you wish, such as “Not found”.
The final formula looks like this:
=IF(VLOOKUP(order_id,order_data,1,TRUE)=order_id, VLOOKUP(order_id,order_data,5,TRUE), 'Missing')
Note: To apply this method, your data must be sorted. It's just a simple precaution to limit the chance of missing a lookup value.
23. Combining INDEX + MATCH is faster than VLOOKUP
Engaging in Excel forums, you'll find yourself drawn into debates between VLOOKUP and INDEX + MATCH.
Essentially, combining INDEX + MATCH can do everything VLOOKUP (and HLOOKUP) can do, but with faster processing speed and greater flexibility. Moreover, you might as well start learning about INDEX and MATCH now as it provides you with a better toolkit.
Arguments against INDEX + MATCH argue that the formula requires using a combination of 2 functions instead of 1, thus increasing complexity for users, especially newcomers.
If you're an Excel enthusiast, you'll surely want to delve into mastering INDEX + MATCH. It's a valuable combination. However, that doesn't mean you overlook the VLOOKUP function.
Certainly, VLOOKUP comes in handy everywhere, especially when retrieving data from others. In straightforward scenarios, VLOOKUP will help you get the job done better.
So, in this article on the 23 things about VLOOKUP everyone should know (Part 3), Mytour has covered all the features, usage, and provided examples of the function. If you have any doubts or questions, feel free to leave your comments below the article for clarification.
