If you’re working with Google Sheets, VLOOKUP is a function that allows you to quickly find information in your spreadsheet based on known data. The syntax for VLOOKUP is as follows:
=VLOOKUP(search_key, range, index, [sorted])You can use it to answer specific questions using your dataset. For example, if you have a spreadsheet with employee names and job titles, you can use VLOOKUP to answer questions like “What is Susie’s job title?” or “Who is the project manager?”
By the end of this tutorial, you’ll be able to use the VLOOKUP function to find specific information in your spreadsheet and recognize when you might want to use an alternative function like HLOOKUP or XLOOKUP.
VLOOKUP Formula
The syntax for VLOOKUP is as follows:
=VLOOKUP(search_key, range, index, [sorted])Here’s a quick overview of the input elements:
search_key: The known value that matches the information you’re looking for.range: The entire range of your spreadsheet where you want to perform the lookup.index: The column number where your information is located relative to the range.sorted: (Optional) For an exact match with your search value, write FALSE, and for an approximate match, write TRUE.
How to Use VLOOKUP
VLOOKUP stands for vertical lookup. You should use this function when your data is organized in columns, meaning each column lists a specific criterion and the data is linked across each row.
If your data is organized in rows, with related data in each column, you should use HLOOKUP (horizontal lookup). We’ll talk about HLOOKUP later in this article. Here, we’ll detail step by step how to use VLOOKUP.
1. Decide where you want to display this information.
You’ll enter the VLOOKUP function in an empty cell of your spreadsheet, so your first step is to decide where you want to display your answer.
With your data organized in columns, it’s generally recommended to keep your VLOOKUP formula in a separate column next to your data rather than in a row above or below your data. This can make your organization a bit easier if you want to add new rows, sort or filter your data. Alternatively, you can also create a separate sheet (tab) in your spreadsheet to host your functions.
If you’re following along with the practice sheet, I’ll begin my VLOOKUP function in cell I4 by typing =VLOOKUP(.
2. Determine your search key.
Your search key is the known value that matches the information you’re looking for. In our practice sheet, if you want to know which movie topped the box office in 1994, your search key would be 1994.
You can type your search key directly in the function, or you can reference a specific cell in your spreadsheet. By typing your search key in a new cell and referencing the cell in your function, you can quickly answer multiple questions with your VLOOKUP function. Instead of retyping the function with multiple search keys, you can use the same function to find the movies that topped the box office in 1994, 1995, and 1996 by simply changing the text in your referenced cell.
In my practice sheet, I’ll reference cell I3 for my search key, and in cell I3, I’ll type 1994. Now, my function reads =VLOOKUP(I3,.
3. Identify your range.
Your range is the entire range of your spreadsheet where you want to perform the lookup. If you select the entire area where your data is located, the range would be indicated as top left cell:bottom right cell. If your range includes entire columns, you can indicate your range as leftmost column:rightmost column.
In my practice sheet, I’ll write my range as A:F. I could also write my range as A2:F47, but then I would have to remember to adjust my VLOOKUP function if I add new rows of data regarding future box office winners. Now, my function reads =VLOOKUP(I3,A:F,.
4. Count columns in your range to find your index.
Your index is the column number where your information is located relative to the range. This will always be a positive integer.
To find your index, start with your first column as 1 and count up to the column where your range is located.
In my practice sheet, I’m looking for a movie title, which I know is in column B, or the second column of my range A:F. My index is 2, so now my function reads =VLOOKUP(I3,A:F,2,.
5. Answer with TRUE or FALSE for the sorted input.
For the last input, you’ll either write TRUE or FALSE. If you want an exact match with your search value, write FALSE; if you want an approximate match, write TRUE. This input is optional and will default to TRUE if you don’t use it, but generally, FALSE is considered best practice here.
In my practice sheet, because I want an exact match, I’ll write FALSE, and then I’ll finish my function with a closing parenthesis: =VLOOKUP(I3,A:F,2,FALSE).
By pressing Enter on my keyboard, the answer to our question will appear: the highest-grossing film of 1994 was Disney’s instant classic, The Lion King!
Optional Bonus Step: Formatting Your VLOOKUP for Repeated Use
If you plan on using your VLOOKUP function to answer multiple questions, it can be helpful to add labels and formatting around your function. You can also add additional VLOOKUP functions to quickly extract more data related to your search key.
For example, in my practice sheet, I completed the VLOOKUP function for the movie title with additional functions to extract the main director and box office revenue, all related to the original search key in I3. I also added labels in the column to the left of my functions to clearly see the data my VLOOKUP functions extract, titled my functions in the row above my search key for easy recall of my question, and added borders to differentiate my functions from the rest of the data in my sheet.
Now, I can quickly answer multiple questions about the highest-grossing movie for any given year by simply changing the year indicated in cell I3.
Related VLOOKUP Tasks
Here are a few other ways to use VLOOKUP in Google Sheets:
VLOOKUP with Multiple Criteria
You can only use a single search value in a VLOOKUP function, but there are workarounds if you want to search for information with two (or more) search values. The most common way to approach a VLOOKUP with multiple criteria is by creating a “helper column”.
A helper column is essentially an additional column that you create which combines your two criteria into a single column. You can do this using a formula, =[cell]&[cell].
Then, in your VLOOKUP function, your search value combines your two criteria in a similar way. If you’re referencing cells in your workspace, you can format your search value as [cell]&[cell], or you can directly type the information you’re looking for in the function, [criteria1]&[criteria2].
Take a look at the image below to see how to use a helper column and two search value cells to find out how much money the movie Harry Potter directed by Chris Columbus grossed.
VLOOKUP with IF Statements
If you want to answer a question with a yes or no using your data, you can wrap your VLOOKUP function with an IF statement. This will return customized text under specific conditions.
To do this, you use a regular IF statement, =IF(logical_expression, value_if_true, value_if_false), with your VLOOKUP function as part of your logical expression.
Take a look at the image below to see how to use an IF statement to answer the question of whether the highest-grossing film of a given year made over 10 billion dollars in revenue.
VLOOKUP with IFNA Statements
You can use your VLOOKUP with an IFNA function to return customized text if your search value cannot be found. Your customized text will appear in place of an #N/A error.
To do this, wrap your VLOOKUP function with an IFNA function:
=IFNA(VLOOKUP(search_key, range, index, [sorted]),"[return_value]")Take a look at the image below to see how to use an IFNA function to return the phrase “Unavailable” if you try to find box office data for a year outside of the dataset, from 1977 to 2022.
Common VLOOKUP Errors and Solutions
If you don’t write your function correctly, you’ll get a formula parse error. Here are a few common error messages associated with VLOOKUP and some suggestions for troubleshooting:
#NAME?: A name error indicates that one of your inputs is unexpected. For example, if you write a letter instead of a number for the index, or write NO instead of FALSE for sorted. Google Sheets functions follow certain linguistic conventions, and this error tends to appear if an input is outside of those conventions.#REF: This error indicates that your index is outside of your range. When counting your index, make sure to count only the columns of your range starting with 1 for the first column. For example, if your range consists of columns B through D (B:D), B would be index 1, C would be 2, and D would be 3. Using an index of 4 here would result in a #REF error.#VALUE!: This error typically indicates a problem with your index, such as using a negative number or zero. Make sure to use only positive integers as indexes, counting the columns of your range from left to right, with your search value being in the first column of your range.#N/A: This error indicates that your search value cannot be found in your first column. Double-check that you spelled your search value correctly and used correct punctuation throughout your function.- Returning the wrong value: There are a few common reasons why a VLOOKUP function might return an incorrect value:
- It could be related to the sorted input. If you leave this input blank (thus defaulting to TRUE) or write TRUE, but your first column is not sorted in ascending order, you will get an incorrect value. Try sorting your column or changing sorted to FALSE.
- It could be related to the dataset. If your search value appears multiple times in your first column, VLOOKUP will only return the value associated with the first occurrence of your search value.
- It could be related to your data cleaning process. VLOOKUP doesn’t distinguish between uppercase and lowercase letters, but it is sensitive to spaces and extra characters. Make sure to work with clean data and use consistent character conventions in your dataset and search value.
Learn More: How to Fix Formula Parse Error – Google Sheets
Limitations and Alternatives
VLOOKUP is a vertical lookup, meaning it searches columns and only looks from left to right. If your data is organized differently, you may want to use a different but similar function to perform your lookup.
VLOOKUP vs HLOOKUP
HLOOKUP is a horizontal lookup. Use an HLOOKUP function if your data is organized by rows.
The syntax for HLOOKUP is as follows:
=HLOOKUP(search_key, range, index, [sorted])You’ll notice that the syntax for HLOOKUP is almost identical to VLOOKUP. The difference is that you’re considering rows instead of columns. Your search value must be found in the first row of your range, and to find your index, you need to count rows instead of columns.
VLOOKUP vs XLOOKUP
XLOOKUP is a newer function in Google Sheets that allows you to search for data in any direction.
The syntax for XLOOKUP is as follows:
=XLOOKUP(search_key, search_range, result_range, not_found_value, match_mode, search_mode)This syntax may seem more complex than VLOOKUP, but several inputs are optional. In its simplest form, you only need to type =XLOOKUP(search_key, search_range, result_range).
Here’s a quick overview of the inputs:
search_key: Like VLOOKUP, this is the known value that matches the information you’re looking for.search_range: The single column or row of your spreadsheet where your search value will be found.result_range: The single column or row of your spreadsheet where your result will be found.not_found_value: (Optional) The customized text you want to return if your search value cannot be found. If left blank, it will default to displaying #N/A.match_mode: (Optional) How you want to search thesearch_rangefor thesearch_key, either:- 0 for an exact match (default, if left blank).
- -1 for an exact match or, if not found, a match lower than the search value.
- 1 for an exact match or, if not found, a match higher than the search value.
- 2 for a wildcard match.
search_mode: (Optional) How you want to search theresult_range, either:- 1 to search from first entry to last (default, if left blank).
- -1 to search from last entry to first.
- 2 to perform a binary search when the range is sorted in ascending order.
- -2 to perform a binary search when the range is sorted in descending order.
Continue Your Learning
Do you want to further strengthen your data work skills using Google Sheets? Enroll in the Google Data Analytics Professional Certificate. You’ll learn more about spreadsheets and other key analytical tools.
Take advantage of the powerful use of VLOOKUP in Google Sheets and turn your data into valuable insights for informed decision-making!
For more interesting articles, visit Crawlan.com.
