We also tackle various questions surrounding VLOOKUP and the common pitfalls you may encounter when trying to use the command.
What Is VLOOKUP?
VLOOKUP – short for “vertical lookup” – is a function that originated in Microsoft Excel that allows you to search for a particular value in a column, then uses that information to pull up a different value along the same row. For example: you could have three columns labeled “name,” “number,” and “address,” and these columns could be filled with information. Using VLOOKUP, you would be able to search for a particular name in the “name” column, then use that information to display a corresponding number or address that can be found along the same row as that name. Keep in mind, however, that VLOOKUP is not case sensitive. While it may not seem especially useful when used in a small pool of data as in the aforementioned example, it comes in very handy when you have a large amount of information on your sheet and you want to use certain values in other areas. You could create a master list of information in one sheet, then just use VLOOKUP in the succeeding sheets to pull data from the master list. That way, you only need to update one sheet, and the values would automatically follow across the rest. To put things simply, a VLOOKUP string is as follows:
How to Use VLOOKUP in Excel and Google Sheets
At first glance, the VLOOKUP string may seem daunting, but it’s actually a lot simpler than it looks, as these in-depth instructions will show. Note: this guide uses Microsoft Excel to perform the VLOOKUP, but you can use the same method with Google Sheets.
How to Filter Data Using Multiple Criteria
VLOOKUP was designed to only pull up a single value, and there are other functions for looking up multiple sources of information – that’s not to say that you can’t use VLOOKUP to search multiple criteria. By using a helper column, you can create unique identifiers that store information from multiple cells, then tweak VLOOKUP to look for these unique identifiers instead. This is useful in cases where you have multiple cells with the same value. Let’s say, for example, that our table has multiple cells with the name “Iris Watson.” Under normal circumstances, VLOOKUP would only pull up the first “Iris Watson” it finds on the list, but you could be looking for a different one. Through the use of a helper column, you’d have a unique identifier that can help VLOOKUP differentiate between the different “Iris Watsons” in your sheet. For this example, we are using VLOOKUP to display an address instead of a phone number. We also added a second “Iris Watson” to the sheet with a different address and phone number. This is what our string looked like initially: =VLOOKUP(F9&" | “&F10, F9 and F10 are our “Name” and “Address” search fields respectively, and the & symbol works like a concatenate in that it joins both fields together. The “ | “ part of the string is the same divider we used in the concatenation in our helper column.
Is VLOOKUP Better than INDEX-MATCH?
This question has been heavily debated since the earlier days of Microsoft Excel. Before we answer this question, it’s important that we understand what INDEX-MATCH even is. INDEX and MATCH are two separate functions that are often combined to create a more versatile lookup system than VLOOKUP. The MATCH function is used when you want to find the position number of a certain value within a range of cells. INDEX, on the other hand, uses two potential formats to display a value from a table array or range of cells. By combining both, one can utilize the MATCH function to determine the position numbers of the information provided, then have INDEX use those position numbers to return values. Which of the two is better boils down to who is using the function. VLOOKUP is much more friendly towards newbies and mid-tier Excel or Google Sheet users, given how much easier it is to set up and utilize. However, INDEX-MATCH is far more flexible and can be used in a wider variety of situations. At the end of the day, many more people know how to use VLOOKUP compared to INDEX-MATCH, so the former is probably the better pick if the sheet will be accessed by many non-advanced users. But if the sheet is meant for experts, then INDEX-MATCH is probably the way to go.
Common Dos and Don’ts When Using VLOOKUP
There’s no shame in making a few mistakes when you’re new to the VLOOKUP function. Most Excel veterans have been there at some point in their lives. Here are a few things to keep in mind when trying VLOOKUP.
1. DO make sure that the Lookup_Value is the first column in your table.
VLOOKUP functions under the assumption that your lookup_value is in the first column in your table array. It’s only able to display information if that value is in a succeeding cell from the same row. Put your lookup_value anywhere other than the beginning, and the function will fail.
2. DON’T forget to use FALSE for exact matches.
The last part of your VLOOKUP string lets you dictate whether the value is FALSE for an exact match or TRUE for a partial match. Many users mistakenly use TRUE, which could lead to inaccurate results, or they forget to set a value at all.
3. DO double-check the column index number.
What VLOOKUP displays depends heavily on you setting the correct “col_index_num” when typing out the string. The “col_index_num” or column index number is the numerical value set for each column in your table array. The value of the first column is considered 1, the second 2, and so on. Input the wrong “col_index_num” value in your string, and the function will show a completely different result.
4. DO use F4 when copying the formula to other cells.
One of the most useful things about VLOOKUP is that you can drag the formula down to copy it across various cells. The problem is, the values specified in the function string will shift downwards as well, ruining the whole formula. To prevent this, place your cursor on the values in your formula and hit the F4 key. This turns them into absolute values that won’t shift when the formula is copied.
Usual VLOOKUP Errors and How to Fix Them
The most common error you’ll come across when using VLOOKUP is the “#NA” error, though it’s worth noting that there are a variety of reasons this error would appear.
1. Your Lookup_value is not in the first column of your table array.
One of VLOOKUP’s biggest limitations is that it can only search for values in the very first column of your table array. If your lookup_value isn’t there, it’ll result in an #NA error. To remedy this, you can either tweak your formula to reference a different column or move your columns so that the lookup_value is in the right place.
2. VLOOKUP is not finding an exact match.
The last value in your VLOOKUP formula is your range_lookup argument that you either set to TRUE for an approximate match or FALSE for an exact one. If you have this argument set to FALSE, and VLOOKUP is unable to find an exact match, you’ll end up with an #NA error. If you’re absolutely certain there should be a match in your lookup_value, then you’ll want to double-check the information in your table array to make sure everything is properly formatted and doesn’t have unwanted spaces. Non-printing characters can also prevent VLOOKUP from finding items properly.
3. Excessively large floating point numbers
Essentially, floating point numbers are numbers that have decimal points. With VLOOKUP, if you have a figure that has too many numbers after the decimal point, you will end up with an #NA error. Fixing this is pretty easy: round up your number by a maximum of five decimal places, and it should work. You can do so using the ROUND function.
1. What happens when you leave the range_lookup argument blank?
The fourth argument in a VLOOKUP string – which should be set to either “TRUE” or “FALSE” – is considered optional. Setting it to “TRUE” means that your VLOOKUP will search for an approximate match, while “FALSE” requires the value to be exact. The thing is, if you leave this argument blank, VLOOKUP defaults it to “TRUE,” which could mess with your desired results.
2. What are the alternatives to using VLOOKUP?
The best alternative to utilizing the VLOOKUP function would be the INDEX-MATCH combo. Bear in mind, however, that this combination of functions is a bit more difficult to learn since you have to master two different functions and ensure they work properly together.
3. Can you search for values by rows instead of columns?
Yes. You can do so with HLOOKUP or “horizontal lookup” in Excel and Google Sheets. This function lets you look up a value from a specific row, then displays a value from a different row but on the same column. If you want to limit your searches to a single row or column, you can use the LOOKUP function.
