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

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.

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.

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:

• How to Use Named Ranges in QUERY in Google Sheets
• Mastering SUMIF with Named Ranges in Google Sheets
• Importrange Named Ranges in Google Sheets
• Dynamic Column Id in Query Importrange Using Named Ranges
• Auto-Expand Named Ranges in Google Sheets to Accommodate New Rows
• Highlighting Named Ranges in Google Sheets
• Dynamic H&V Named Range in Google Sheets
• How to Use Named Ranges in Conditional Formatting in Google Sheets