Use Excel or Google Sheets long enough, and you’re bound to come across the term VLOOKUP. What exactly is it and what does it do? In this guide, we discuss what VLOOKUP does, how you can use it to make your work easier, and provide you with instructions on how to best utilize it in both Excel and Google Sheets.
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:
=VLOOKUP( the value you want to lookup, the range of cells you want to look for it in, the column number of the value you want to display, whether you want an exact or approximate match )
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.
- You’ll need a table of information to draw from. We are using the same idea as the previously provided example and adding information under three columns: Name, Address, and Phone Number.
- We want to pull up the corresponding phone number of whatever name in a certain field. In this case, we want to pull up the phone number for “Iris Watson.”
- Start the string by double-clicking an empty cell and typing in
=VLOOKUP(. You’ll see that the first value required is “lookup_value.” which is the information you’ll use to search for the phone number.
- Since we already added the name “Iris Watson” in the cell preceding our VLOOKUP, that cell is what you’ll use as the lookup_value. Your string should currently look something like this:
=VLOOKUP(e12,
- Next is table_array. This is the entire table of information that you’re drawing data from. Simply highlight the entire table where your data is located. At this stage, your string should look similar to this:
=VLOOKUP(e12,A1:C5,
- The third value in your string is the “col_index_number” or column index number. This is the number of the column from which you’re trying to extract information. In the table array we selected, “Name” would be considered 1, “Address” is 2, and “Phone Number” is 3. Since we’re trying to VLOOKUP a phone number, our string looks like this:
=VLOOKUP(e12,A1:C5,3,
- Finally, the last part of the string is whether or not you want an exact match or an approximate match for the value you’re searching with. You’ll need to type in TRUE for approximate and FALSE for exact. In our case, we went with the latter. Our final string is
=VLOOKUP(E12,A1:C5,3,FALSE)
- As you can see, our VLOOKUP successfully pulled up the corresponding phone number for “Iris Watson.”
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.
- The first thing we did was create a helper column that combines the “Name” and “Phone Number” cells to create a unique identifier. We used the CONCATENATE function, which simply combines strings from different cells. Your string should look something like this:
=CONCATENATE(B2," | ",D2)We added the pipe"|"symbol and some spaces between the name and phone number to make it easier to understand. If you want to learn more about this function, check out our article on how to use CONCATENATE.
- Once you successfully concatenate, click the bottom-right corner of the highlighted cell and drag it down to the other cells in the helper column. That will apply the same concatenation to them as well.
- You’ll need two fields for VLOOKUP. We added “Name” and “Number” lookup fields as well as an “Address” field that will display the VLOOKUP result.
- What we want to do is have VLOOKUP combine the information provided in the “Name” and “Number” search fields in such a way that they would fit the same format we’re using in our helper column. That way, VLOOKUP would be able to spot the unique identifier in the helper column and pull up the corresponding address.
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.
- You now only need to follow the rest of the VLOOKUP formula. You’ll have to select the table array, input the column index number, and set it to EXACT match. This was our end string:
=VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)
- You’ll see that it’s not enough to enter just a name or address in the search fields. Both have to be filled for a successful lookup to take place. Below you can see two different VLOOKUP results since we have two different “Iris Watsons.” You’ll get a different result depending on which phone number you enter in the second search field.
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.
Frequently Asked Questions
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.
Our latest tutorials delivered straight to your inbox
