Using Named Ranges in VLOOKUP in Google Sheets

Published on

The purpose of using Named Ranges in VLOOKUP in Google Sheets is to make the formula look cleaner. However, other hidden benefits come with combining VLOOKUP and Named Ranges. In this guide, I’ll share these benefits, as they have come to my attention.

Introduction

VLOOKUP is easy to learn once you understand its purpose. You can think of it like looking for specific information in a dataset—much like how we often use our eyes to find flight or train schedules in public places.

For example, at a metro station, if you have a train number, you can easily find the train timing or route on the display panel. In VLOOKUP, we’re doing something similar. The display panel represents the dataset, and the train number is the search key.

You can name your dataset, such as “train_info”—this is known as Named Ranges. Let’s see how you can use Named Ranges in VLOOKUP in Google Sheets.

Additionally, I’ll also share an advanced use case involving VLOOKUP, Named Ranges, and the INDIRECT function.

The Basic Use of Named Ranges in VLOOKUP in Google Sheets

Named Ranges in Vlookup in Google Sheets

Sample Data (Range A2:C5)

Let’s say I have a price list of industrial items in columns A, B, and C (range A2:C5). Column A contains item codes, while column B lists the item descriptions. Column C contains the unit prices of these items.

Search Key:

In cell E3, I have a search key, which is the item code for one of the items.

Expected Formula Output:

In cell F3, I want to search for the item code (E3) in column A and return its unit price from column C.

I will demonstrate how to use VLOOKUP to return the price, both with and without Named Ranges.

VLOOKUP Without Named Ranges

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

Formula:

=VLOOKUP(E3, A2:C5, 3, 0)

Explanation:

In this formula:

  • The search_key is the value in cell E3 (e.g., item code).
  • The lookup range is A2:C5.
  • The index is 3, which corresponds to the column with the unit prices.
  • The [is_sorted] is set to 0 (false), indicating that an exact match is required.

VLOOKUP With Named Ranges

As shown in the screenshot, I have a price list in the range A2:C5. I’ll first name this range “pricelist” for use in VLOOKUP.

Creating a Named Range for VLOOKUP:

  1. Select the data in range A2:C5.
  2. Click on the Data menu, then select Named ranges.
  3. Type the name “pricelist” and click Done.
Create Named Ranges for Vlookup

Using the Named Range in VLOOKUP:

Now that I have created the Named Range, I can use it in the VLOOKUP formula.

Formula:

=VLOOKUP(E3, pricelist, 3, 0)

In this formula, I’ve replaced the lookup range A2:C5 with the Named Range “pricelist”.

Benefits of Using Named Ranges in VLOOKUP

Here are two key benefits of using Named Ranges in VLOOKUP in Google Sheets:

1. Use of the Same VLOOKUP Formula Across Different Sheet Tabs

For example, if the search key is in cell E3 on Sheet5 and the data with the Named Range is on Sheet1, the formula in Sheet5 will be:

  • With Named Ranges:
=VLOOKUP(E3, pricelist, 3, 0)
  • Without Named Ranges:
=VLOOKUP(E3, Sheet1!A2:C5, 3, 0)

2. Switch Lookup Tables Using a Drop-down Menu with Named Ranges

In a previous tutorial, I discussed switching tables in VLOOKUP using an IF logical test. Here, I’ll show you how to use a drop-down menu to switch between different lookup tables.

This approach combines VLOOKUP, Named Ranges, and the INDIRECT function.

Using VLOOKUP, Named Ranges, and the INDIRECT Function

There are two tables in the ranges A2:B5 and D2:E5, where the first row in each table contains headers. We will name these ranges, create a drop-down with those names, and use VLOOKUP to retrieve data dynamically.

Switch lookup tables dynamically using Named Ranges in VLOOKUP

Steps:

Define Named Ranges for VLOOKUP:

  • Name the range A3:B5 as “jennie” and D3:E5 as “allison”.
  • Select each range and assign a name via Data > Named ranges.

Create a Drop-down Menu:

  • In cell H2, create a drop-down menu with the Named Ranges “jennie” and “allison” as options.
  • Go to Insert > Drop-down, replace “Option 1” with “jennie” and “Option 2” with “allison”, then click Done.

Enter the Search Key:

  • In cell G2, enter the search key (e.g., “Sedan”).

Apply VLOOKUP with Named Ranges and INDIRECT:

In cell I2, use the following formula:

=VLOOKUP(G2, INDIRECT(H2), 2, 0)

This formula retrieves the result based on the Named Range selected in H2:

  • If “jennie” is selected, VLOOKUP searches in A3:B5.
  • If “allison” is selected, VLOOKUP searches in D3:E5.

Lookup Multiple Search Keys:

If you need to look up multiple search keys (e.g., “Sedan” and “SUV”), modify the formula as follows:

=ARRAYFORMULA(VLOOKUP(G2:G3, INDIRECT(H2), 2, 0))

This setup allows you to dynamically switch lookup tables in VLOOKUP using a drop-down menu.

Sample Sheet

Conclusion

That’s a detailed guide on how to use Named Ranges in VLOOKUP in Google Sheets. By using Named Ranges, you can simplify your formulas, improve their readability, and create more dynamic lookup systems.

Additional Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.