I have created a learning guide in Google Sheets where you can practice VLOOKUPs. Check out the guide here.
What is a VLOOKUP?
A VLOOKUP allows you to search a table, sheet, or dataset and return specific information and data. I’ll often use VLOOKUPs to create reports that combine multiple data sources. For example, I’ll use VLOOKUPs to combine Google Analytics data and Google Search Console data.
Here’s an example: the table below contains sales for four employees. To look up the sales specifically for Mary, we’ll use a VLOOKUP formula.
How to Create a VLOOKUP Formula?
A VLOOKUP formula has four parameters:
Parameter 1 – Lookup Value. The first parameter is the value you want to look up. In the example above, we want to look up “Mary” (cell F11).
Parameter 2 – Lookup Range. The second parameter contains the range that contains your data. This is our sales table (cells B9 to D13).
Parameter 3 – Return Column. The third parameter contains the column of data you want to return. In our example, sales for Mary is located in column 3 of our sales table.
Parameter 4 – Exact or Approximate Match. The last column will determine if our lookup will be an exact match (meaning the exact text “Mary” must be found) or an approximate match (“Marylyn” could be a match).
For this last parameter, 99.99% of the time you’ll set this last parameter to FALSE for an exact match.
Preventing Errors With Your VLOOOKUPs
Improperly structured VLOOKUPs can cause errors and inaccurate data in your spreadsheets. Here are tips to ensure your data stays accurate and error-free:
1. When looking up a table within a sheet containing multiple data sets, use absolute values for the “Where to Look” range
Using absolute values (such as $A$1:$E$8, not A1:E8) will ensure that your lookup range stays consistent when copying formulas from one cell to another.
2. When looking up a table within a sheet containing a single data set, lookup the entire column in the “Where to Look” range
Looking up entire columns (such as A:E, not A1:E8) will allow you to dynamically lookup rows that you later add to a dataset
3. You will almost always perform an Exact Match lookup
In 99.99% of VLOOKUPS (most likely will be 100%, but I’m leaving room for the one weird time you’ll need it), you’ll set the 4th parameter to FALSE for an exact match.
4. The first column in your range (parameter 2) has to be your lookup column
If, for example, your range has data in columns A-C, column A must have the lookup value. If not, you may have to re-arrange columns or use a different formula, such as INDEX-MATCH or XLOOKUP (XLOOKUP is an Excel-only formula).
Now that you understand the basics of using VLOOKUPs, here are some additional ways you can structure your formulas:
Use the IFERROR formula to remove “#N/A” values
Often, you’ll perform a VLOOKUP and the value you have in the first parameter is not found in your dataset. Instead of leaving “#N/A” values throughout your report, you can add an IFERROR statement to your formula to handle lookups that generate an error.
IFERROR formulas have two parameters: the first is your formula (your VLOOKUP formula in this tutorial) and the second is what happens if that formula generates an error.
Using our sales data example, we can set any employees that generate an error to show $0 sales.
VLOOKUPs by column name instead of column number
A basic VLOOKUP formula uses the column number in parameter 3. However, when importing new data to your sheet (for example, internal CRM data), you may find that column positions have changed.
Instead of using the column number in your formula, with a MATCH formula nested in your VLOOKUP you can return data based on the column name.
MATCH formulas have three parameters: the first is your column name, the second is the range that contains your heading row, and the third sets the MATCH as either exact match (0) or approximate match (1 or -1). Like a VLOOKUP formula, you’ll always want to set this to exact match (0).