How to Use VLOOKUPs in Excel and Google Sheets

VLOOKUP Practice

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.

vlookup with sales data

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.

vlookup formula parameters

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).

Advanced VLOOKUPs

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.

vlookup with iferror

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).

vlookup with match formula

Have Questions?

Have questions about your VLOOKUP formulas? Add a comment below or connect with me on LinkedIn or Twitter.

How to Create Monthly SEO Reports

Documents Needed:

Monthly reports are important to me for two reasons. First, they provide much-needed communication to stakeholders about the SEO health of a website. Second, they help identify opportunities for growth and next actions that need to be taken to capture that growth.

This monthly report Data Studio template uses data from Google Analytics, Google Search Console, and a crawl from Screaming Frog to highlight the previous month’s SEO performance.

Of course, just providing raw data to stakeholders is useless. So included in this template is the ability to add a written analysis and summary of the data presented.

How to Build a Monthly SEO Report with Data Studio

The end result of this process will be two documents:

  • A Google Sheets file that will contain your crawl data from Screaming Frog.
  • A Data Studio document containing your monthly SEO report and analysis

The Data Studio template is a remix of a report created by Webris. You can check out their version here.

Step 1: Make a copy of the Google Sheets template

Step 2: Crawl Your Website with Screaming Frog

  • In Screaming Frog, perform a full crawl of your website

Step 3: Export Your Site Crawl

  • Within the “Internal” tab of Screaming Frog, set the filter drop down to “All” and Export to CSV

Step 4: Import Your Site Crawl CSV to the Google Sheets Doc

  • Go to the “Site Crawl Import” tab
  • Go to File >> Import
  • Find your Screaming Frog CSV export
  • In the “Import” dialog box, select “Replace current sheet”

Step 5: Identify Your Website’s Sitemap(s)

There are two types of sitemaps:

  • Normal sitemap: contains actual website URLs
    • Example:
  • Sitemap Index: a list of sitemaps
    • Example:

Search for a sitemap index first. If there is no index, then search for a normal sitemap.

Step 6: Crawl Your Website’s Sitemap(s)

Be sure that you are using version 10 of Screaming Frog. If not, update Screaming Frog.

  • Open Screaming Frog. Go to Mode > List
  • Click on the Upload dropdown and select the appropriate sitemap option.
    • If you have a sitemap index, select “Download Sitemap Index” and enter the index URL.
    • If you have a normal sitemap, select “Download Sitemap” and enter the sitemap URL.

Step 7: Export All Sitemap Crawl Data

  • Within the “Internal” tab of Screaming Frog, set the filter drop down to “All” and Export to CSV

Step 8: Import Sitemap Crawl Data to Google Sheets Doc

  • Go to the “Sitemap Crawl Import” tab
  • Go to File >> Import
  • Find your Screaming Frog CSV export
  • In the “Import” dialog box, select “Replace current sheet”

Step 9: Make a Copy of the Monthly Report Data Studio Template

  • Open the Monthly Report Data Studio template
  • In the upper-right corner, click the “Copy” icon

Step 10: Set up Data Connectors

You will be presented with a prompt to set up data connectors for Google Analytics, Google Search Console, and your Google Sheet Crawl Data Doc. For each connector, click the drop-down arrow for “Select a Data Source.”

If this is your first time using Data Studio, then you’ll need to “Create New Data Source” for Google Analytics and Google Search Console. Then, create a new data source for the following worksheets (tabs) within your Google Sheets doc:

  • 4xx/5xx pages
  • Crawl Metrics
  • Sitemap Metric

Learn more about Data Studio Connectors with these resources:

Step 11: Analyze Data

Now that you have connected all your data, the last step is to analyze the data for insights, opportunities, and next actions. The first page of the Data Studio Template has an example report you can create for your stakeholder.

Feel free to reach out to me at [email protected] or connect with me on LinkedIn if you have any questions about this template.

How to Create a Strategy for SEO Traffic Growth

My keyword research and SEO growth process involves the following:

  1. Identify keywords your target market uses
  2. Group those keywords into semantic groups
  3. Map those groups to current and future URLs on the site
  4. Prioritize URLs by traffic or revenue opportunity

A Google Sheets tool I built called Evie Strategy helps me efficiently perform these steps.

The tool uses keyword data from SEMRush to build a comprehensive roadmap for growth on your site.

Here’s an example of a completed version.

Also, to implement this process for yourself, get a blank copy here. This template has a link to instructions with screenshots on how to use the tool.