If you’ve ever spent precious time manually searching through a sea of data to find a particular value, VLOOKUPย could save you a ton of time.
With VLOOKUP as your ally, you can unlock new possibilities in data analysis and save valuable time.
In this comprehensive guide,weโll cover everything you need to know about the VLOOKUP Excel function. Read on to learn more.
Table of Contents
What is VLOOKUP in Excel?
VLOOKUP is a function in Microsoft Excel and most other spreadsheet programs, like Google Sheets, that allows you to search for a specific value in a vertical column (known as the lookup table) and retrieve a corresponding value from a different column within the same row.
The term “VLOOKUP” stands for “Vertical Lookup.”
VLOOKUP is a versatile tool that allows you to search for specific values in a table and retrieve related information from another column. It has a wide range of applications, from finding data in large datasets to merging information from multiple tables. Understanding how to use VLOOKUP effectively can save you time, streamline your workflow, and provide valuable insights from your data.
Please note that VLOOKUP is still supported in Excel, but they are pushing for you to learn XLOOKUPย instead, which superceded the VLOOKUP and HLOOKUP functions in Excel.
VLOOKUP Excel Formula Syntax
The syntax for the VLOOKUP function is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value:ย The value you want to search for in the first column of the lookup table.
table_array:ย The range of cells that contains the lookup table, including both the lookup column and the column(s) from which you want to retrieve the value(s).
col_index_num:ย The column number within the lookup table from which you want to retrieve the result. It represents the offset from the leftmost column in the lookup table.
range_lookup:ย (optional) A logical value that specifies whether you want an exact match or an approximate match. If set to TRUE or omitted, it assumes an approximate match. If set to FALSE or 0, it looks for an exact match.
How to Use VLOOKUP in Excel
In this section, weโll be looking at a couple of different ways we can use VLOOKUP in Excel using examples. Below is the spreadsheet weโll be using:
How to Do VLOOKUP in Excel
VLOOKUP can help us find the exact match in a column. ย For example, in our example worksheet, we can find the student with the ID number 2947.
Hereโs how to VLOOKUP in Excel:
- In the cell you want, type =VLOOKUP().
- After the opening brackets, select the cell with the search value and add a comma,
- Select the range of data you want to search and a comma:
- Enter the column index number. This is the column where you think the answers are, and it must be to the right of your lookup values:
- Enter the range lookup value, and FALSE finds exact matches. Your finished formula looks something like this:
=VLOOKUP(A13,A:2:D:10,2,FALSE)
This formula will return the correct name corresponding to ID 2947, which is Tony Jackson.
How to Use VLOOKUP to Do Two-way Lookup
The VLOOKUP can usually return a corresponding match from a column only. However, we can combine the VLOOKUP function with the MATCH function to determine the column index number of each field.
This way, we can perform a 2-dimensional lookup involving both row and column. For this example weโll be using the example sheet below:
Hereโs how to do a 2-way lookup with VLOOKUP in Excel:
- In the cell you want, type =VLOOKUP().
- After the opening brackets, select the cell with the search value and add a comma.
- Select the range of data you want to search and a comma.
- Enter the MATCH formula:
- Select the header row as the search value.
- Select the row and add a 0 for the exact match.
- Close the brackets and another 0 for an exact match.
- Enter the range lookup value, and FALSE finds exact matches. Your finished formula looks something like this:
=VLOOKUP(A13,A:2:D:10,2,FALSE)
This makes your formula more dynamic.
How to Use VLOOKUP with Multiple Criteria
Suppose we wanted to look up more than one column of information. For example, in our example worksheet below, we can find the Marks for Melissa Danielle, specifically in Biology.
To do this, first, weโll need to create a helper column that combines the criteria.
- Right-click on a column and choose Insert >ย Entire column.
- Write the header Helper column.
- In the cell type the formula = A2&โ โ&B2
- Drag the formula to the rest of the cells.
- In the cell you want to return the results, type the VLOOKUP.
- ย Combine the headers with the formula H2&โ โ&I1
- Select the range from the helper column and a comma.
- Add the column index.
- Add a 0 or FALSE for an exact match.
- Click Enter.
With this formula, we can use more than one condition for the lookup. In our example, we looked up both the names and the subjects to return the marks.
How to Use VLOOKUP with Wildcards
Wildcards in Excel are special characters that represent unknown or variable values in text-based functions and formulas. They allow you to perform flexible searches, matches, and replacements within your data.
Unlike Google Sheets, Excel only supports two main types of wildcards: the asterisk (*) and the question mark (?).
Asterisk (*) wildcard- It can be used at the beginning, middle, or end of a text string to represent a value.
Question mark (?) wildcard-ย It can be used to find a specific character in a specific position within a text string to represent a single character.
Here’s how to use VLOOKUP with Wildcards:
- In the cell you want, type =VLOOKUP().
- After the opening brackets, write the text and add the wildcard.
- Select the range of data you want to search and a comma:
- Enter the column index number.
- Enter the range lookup value, and FALSE finds exact matches. Your finished formula looks something like this:
=VLOOKUP(โMelissa*โ,A:2:D:10,4,FALSE)
This will return the first match for the column corresponding to any cell with the name Melissa in it.
These are just a few examples of how you can use VLOOKUP in Excel. You can even use VLOOKUP from another sheet.
Tips to Use VLOOKUP Function Efficiently
The VLOOKUP function is a very powerful function, but only when used right. Here are some tips for using the VLOOKUP function efficiently in Excel:
- To ensure accurate results, sort your lookup table in ascending order based on the column you are performing the lookup on. VLOOKUP requires the data to be sorted in order to function properly when using an approximate match.
- When using the VLOOKUP function, lock the lookup table range using absolute cell references (e.g., $A$1:$B$10). This will prevent the range from changing when you copy the formula to other cells, ensuring consistent lookup ranges.
- Consider assigning a named range to the lookup table. This makes it easier to refer to the table in the VLOOKUP formula, improves formula readability, and reduces the chance of errors.
- VLOOKUP may return #N/A if the lookup value is not found. To handle this, you can use the IFERROR function to display a custom message or perform an alternative action when no match is found.
- The range_lookup argument determines whether VLOOKUP should perform an approximate match (TRUE) or an exact match (FALSE). Understand the implications of each option and choose the appropriate one based on your data and requirements.
- VLOOKUP can be combined with other Excel functions like IF, INDEX, MATCH, or nested within other formulas. Exploring these combinations can enhance the flexibility and power of your calculations.
- Always test your VLOOKUP formulas with different lookup values to ensure they return the expected results. Double-check the accuracy of retrieved values against the original data.
- If you’re using VLOOKUP in complex spreadsheets, consider documenting your formulas to provide clarity and facilitate future understanding or troubleshooting.
Common Errors in VLOOKUP Function
When using the VLOOKUP function in Excel, there are several common errors that you may encounter. Here are some of the most frequent errors and their potential causes:
#N/A
This error occurs when the VLOOKUP function cannot find an exact match for the lookup value in the first column of the lookup table. It can happen due to misspellings, inconsistent formatting, or differences in the data between the lookup value and the lookup table.
For example, in the table below, we spelled Melissa Danielle incorrectly.
#REF!
This error occurs when the range specified in the table_array argument is incorrect or has been deleted. It can happen if you delete a column or row that is part of the lookup table, causing the range reference to become invalid.
It can also happen if you copy the formula from another sheet or if you use a column index that is not within the selected range.
For example, in the table below, the reference does not exist.
#VALUE!
This error occurs when there is an issue with the data types of the lookup value or the lookup table. It can happen if the data types are incompatible or if there are non-numeric characters in a column that should contain only numbers.
#NAME?
This error occurs when Excel cannot recognize the function name or when there is a typo in the formula. It can happen if you misspell the function name (e.g., “VLOOKUP” instead of “VLOOKUP”) or if you accidentally delete part of the formula.
Wrapping Up
This article aims to serve as your comprehensive guide to harnessing the power of the VLOOKUP ย Excel function, unlocking its potential to streamline your data analysis processes.
With the few examples above, you should be able to navigate around your worksheet with the VLOOKUP function.
You can also check out our premium templates below. Remember to use the code SSP at checkout to save 50% on any purchases!
Related:
- Converting Excel to Google Sheets
- How to Use HLOOKUP in Microsoft Excel
- The Best Excel Courses to Make You a Skilled User in No Time
- Microsoft Excel Vs Numbers: Which is the Better Spreadsheet Program?
- The Best Microsoft Excel Alternatives that are Mac Compatible
- A Simple Guide on How to Lock Cells in Excel
- How to Combine Rows in Excel