How to Use VLOOKUP with Named Range in Google Sheets

Named range can be a useful feature in Google Sheets if you are working with a large volume of data. It can help you memorize important parts of your range or a full range by naming them with unique names. In this article, we will discuss how you can use VLOOKUP with a named range in Google Sheets easily and effectively.

Overview of VLOOKUP with Named Range in google sheets

The above screenshot is an overview of the article, representing how we can use VLOOKUP with named range in Google Sheets.


A Sample of Practice Spreadsheet

Download this spreadsheet to practice yourself.


What Is Named Range in Google Sheets?

A Named Range allows you to give a unique name to important parts of a range or a full range in Google Sheets. The ability to use Named Range in functions and formulas can help you manage your data easily and help you save precious time.

We have a data table with employee Names and their respective Sales Amount, we named the Sales Amount range which is C5:C9 as Sales.

named range

We use this named range to calculate the Total sales made by the employees.

select range for named range

How to Apply Named Range in Google Sheets

Consider the dataset we used before. The table contains employee Names and their respective Sales Amount. We will try to name the  Sales Amount range with Named Range.

  • First, select the range where you want to apply the Named Range. We select Range C5:C9 in our example.

dataset used for named range

  • Then, go to Menu bar > Data > Named ranges.

select named range from menu bar

  • Now, you will see a sidebar called Named ranges appear on the right side of your workbook.

named range side panel

  • After that, input the name that you want to use for your selected range. We use Sales as the name in our example.

specific name of the range

  • Then, you can even select a different range from the Select data range icon on the side panel.

select different range

  • Finally, press Done and the selected range is named according to your choice.

final result after naming range


3 Simple Approaches to Use VLOOKUP with Named Range in Google Sheets

The VLOOKUP function lets you look for a particular value down the first column in a range of cells. VLOOKUP can be used with Named Range to have a cleaner feel about the formula and it can help you by saving lots of precious time.

1. From Same Worksheet

We use the following dataset with Student ID, their Name, marks obtained by the students in Test 1 and Test 2, and the Total Marks obtained by each of the students. We will use this table to show the Total Marks obtained by each student in a separate cell.

dataset used to use vlookup with named range in the same sheet in google sheets

Steps:

  • First, name the entire table by following the steps shown above. We name the table Student_Info.

select range to use vlookup with named range in the same sheet in google sheets

  • Then, go to the cell where you want to show the lookup value. We go to cell C17 in our example.

select cell to show result

  • After that, insert the VLOOKUP function.

insert vlookup function to use with named range in google sheets

  • Then, type the value you want the formula to look for. We kept the ID we want to look for in a different cell and so we type that cell reference which is B17.

insert value to look up

  • Now, type the name that you assigned as the name for the range in place of the range parameter of the VLOOKUP We type Student_Info (the named range) in our example.

insert named range use with vlookup with named range in google sheets

  • After that, type the column number whose data you want the formula to return if the VLOOKUP finds a match. We type 5 as we want the formula to return the Total Mark obtained by the students which are in the 5th column of our data table.

input column number

  • Type 0 or FALSE as you want an exact match for the value.

type 0 to get exact match

  • Finally, close the parenthesis for the VLOOKUP function and press ENTER to close up the formula and show the result. This is what the final formula looks like:
=VLOOKUP(B17,Student_Info,5,0)

final formula after using vlookup with named range in google sheets

  • We press ENTER to show the mark obtained by ID 106.

frinal result after using vlookup with named range in google sheets

Read More: How to Check If Value Exists in Range in Google Sheets (4 Ways)


Similar Readings


2. From Another Sheet

Named Range can be a useful tool to use when using VLOOKUP to import and show data from another sheet from the same workbook.

Suppose we have the data table used in the previous method in Sheet 1. We will show the Total Marks obtained by each student in Sheet 2 of the same workbook.

dataset used to use vlookup with named rangefrom different sheet in google sheets

Steps:

  • First, name the entire table of Sheet 1 by following the steps shown above. We name the table Student_Info.

name range

  • Then, go to the cell of the new sheet where you want the Total Mark to appear. We go to cell C5 of Sheet 2.

select cell to show result

  • Type the following Formula:
=VLOOKUP(B5,Student_Info,5,0)

formula used to show result after using vlookup with named range in google sheet from another sheet

Note:
The above VLOOKUP formula will look like this without using the named range:
=VLOOKUP(B5,'Sheet 1'!B4:F14,5,0)
This is the main benefit of using named ranges.

Formula Explanation:

  • B5 is the value that we want to look for.
  • Student_Info is the name of the range from where we want to look for the data.
  • 5 is the column number of the table whose data we want the formula to return.
  • 0 or FALSE is used because we want an exact match for our data.
  • Finally, press ENTER to show the result in cell C5 of Sheet 2.

final result after applying vlookup with named range from another sheet in google sheets

Read More: How to VLOOKUP from Another Sheet in Google Sheets (2 Ways)


3. Use of INDIRECT Function

We use the Named range in place of the range in the VLOOKUP function. The INDIRECT function can be used to indicate the Named range in place of the range in the VLOOKUP function. We have a dataset with two different tables. Table Test_1 contains the marks obtained by the students in Test 1 and table Test_2 contains the marks obtained by the Students in Test 2. We kept the names of students in cell B18 by using a data validation drop-down list. We kept the different test types in cell C18.

dataset used to apply vlookup with indirect function with named range

Steps:

  • First, name the tables by following the steps shown above. We named the first range Test_1.

naming of first range

  • And named the second range Test_2.

naming of second range

  • Then, go to the cell where you want the Mark of the tests to appear. We go to cell D18 in our example.

select cell

  • Then, type the following formula:
=VLOOKUP(B18,INDIRECT(C18),2,0)

formula used to use vlookup and indirect function with named range

Formula Explanation:

  • B18 is the value that we want to look for.
  • INDIRECT(C18) is the range of the two tables from where we want to look for the data.
  • 2 is the column number of the table whose data we want the formula to return.
  • 0 or FALSE is used because we want an exact match for our data.
  • Finally, press ENTER to show the result.

final result after using vlookup and indirect function with named range

  • We can select different students’ names from the drop-down list in cell C18 and select between two different test types from cell B18. The result will be changed accordingly.

another result after using vlookup and indirect function with named range

Read More: Highlight Cell If Value Exists in Another Column in Google Sheets


Things to Remember About Named Ranges

  • You cannot use space between words or letters while naming a range.
  • You cannot use duplicate names for Named Range in a workbook.
  • The name for Named ranges cannot contain anything other than numbers, letters, and underscores.
  • You cannot use the reference of a cell or a range to name a range. For example, A5, D6:D11, cannot be used as a Named range.

Conclusion

In this article, we showed you what Named range is and how you can use VLOOKUP with named ranges in Google Sheets. Keep practicing the methods that we have shown here for a better understanding of the concept. We hope this article was useful to you to help you.

Also, check out other articles on OfficeWheel to keep on improving your Google Sheets work knowledge.


Related Articles

Maruf Niaz

Maruf Niaz

My Name is Niaz. As a regular Google Sheets user, I have a good understanding of the users' needs and bad experiences. In my articles, I will share my knowledge which I hope, will be helpful to you. My latest educational degree was BSc in Textile Engineering from Bangladesh University of Textiles.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo