Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use Named Range in Excel VLOOKUP Function

The VLOOKUP function is one the most commonly used functions in Microsoft Excel. We use the VLOOKUP function to find values in a table to extract it’s corresponding information. Sometimes we need to select a range of cells to refer to but we end up selecting incorrect ranges that make things complicated for us. In these types of cases, we can use the named range with the VLOOKUP function to create reference and use this in our formulas. The purpose of this article is to explain Excel VLOOKUP function named range in order to reference data.

excel vlookup named range

Download Practice Workbook

You can download the practice workbook from here:


2 Easiest Ways of Using VLOOKUP Function with Named Ranges

To show Excel VLOOKUP function with named range, we have taken a brief dataset that contains 11 rows and 5 columns of Order ID, Rep Name, Item, Units and Unit Cost. Applying named ranges in the VLOOKUP function enables us to simplify the formula. We can use that named range as a reference in formulas. For this article, we are going to use Excel 365 version. You can use any other version of Excel as well.

dataset for using named range in VLOOKUP function in excel


1. Using Excel VLOOKUP Function with Named Range

The VLOOKUP function is applied when we need some information from a dataset and want to extract it’s corresponding value. On the other hand, named range is a group of cells or values that is given a name so that we can access those cells in our formulas by only using that range or reference name. Using the named range in the VLOOKUP function makes formulas easier and simpler to understand. If you want to get a clear understanding, go through the following steps.

First, I will show you how to create a named range of our selected dataset. Suppose we want to create a named range or reference of column Rep Name, Item, Units which are column C, D and E respectively. Go through the below steps.

Steps:

  • First, select the range that you want to give a name. In this dataset, C5:E14 is the range that we want to give a specific name.

selecting the range to apply named range

  • After that, Right-click on your mouse and select Define Name.

clicking define name option

  • As a result, a New Name tab will be popped up.
  • Then, type your preferred reference name in the Name field. SalesInformation is the reference name in our case.
  • Finally, click OK.

Inputting preferred reference name in the new name tab

Finally, if we select our range we can see the name of the range in the Name Box and that is SalesInformation.

showing the name of created named range

You can create a named range in another way. To do so follow the below steps.

  • First, select the range (C5:E14) that you want to give a name.
  • Then, go to the Formulas tab and click on Define Name dropdown.
  • After that, select Define Name.

selecting the range to create named range

Therefore, it will take you to the same exact New Name screen where you can define your reference name and create a named range.

creating named range

Moreover, I will show you how to use this created named range in the VLOOKUP function. Suppose we want to find out Kivell’s information which is selling item and units. Let’s go through the following steps.

  • First, select cell B17:C17 to enter the Rep Name.

selecting cell B17 to enter rep name

  • Furthermore, enter Rep Name in cells B17:C17.

entering rep name

  • After that, select cells D17:E17 to enter the VLOOKUP formula.

selecting cell D17 to enter the VLOOKUP formula

  • After that, insert the following formula in cells D17:E17 to apply the VLOOKUP formula using the named range.
=VLOOKUP(B17,SalesInformation,3,FALSE)

 Here, SalesInformation is the named range that is previously created.

inserting formula to apply VLOOKUP function with named range

Formula Breakdown

  • B17 → is the lookup value that we are looking for.
  • VLOOKUP(B17,SalesInformation,3,FALSE)→ becomes
    • VLOOKUP(“Kivell”,SalesInformation,3,FALSE)
  • SalesInformation → is the named range from which we are looking for the value.
  • VLOOKUP(“Kivell”,SalesInformation,3,FALSE) → becomes
    • VLOOKUP(“Kivell”,$C$5:$E$14,3,FALSE)
  • VLOOKUP(“Kivell”,$C$5:$E$14,3,FALSE)→ Here, 3 is the column index number for the Units Column and FALSE represents the exact match
  • VLOOKUP(“Kivell”,$C$5:$E$14,3,FALSE)→ becomes
    • Output89

  • Finally, press the ENTER The output will be shown in cells D17:E17.

output after applying VLOOKUP function with named range

Here, we got the Sold Units of Kivell. We just used the created named range in the VLOOKUP function and it gave us the required information. This is the benefit of using named range in the VLOOKUP function.

You can also get the Item by applying the same formula with a little bit of change. Let’s go down the below part of this article.

  • First, select cell F17 to apply the formula.

selecting cell F17 to enter the formula

  • Thereafter, insert the following formula in cell F17 to apply the VLOOKUP formula using the named range.
=VLOOKUP(B17,SalesInformation,2,FALSE)

 Here, SalesInformation is the named range that is previously used. The only changes in this formula is we have given 2 as a col_index_num since the column index number of the Item column is 2.

inserting formula to apply VLOOKUP function with named range for Item column

Formula Breakdown

  • F17 → is the lookup value that we are looking for.
  • VLOOKUP(F17,SalesInformation,2,FALSE)→ becomes
    • VLOOKUP(“Kivell”,SalesInformation,2,FALSE)
  • SalesInformation → is the named range from which we are looking for the value.
  • VLOOKUP(“Kivell”,SalesInformation,2,FALSE) → becomes
    • VLOOKUP(“Kivell”,$C$5:$E$14,2,FALSE)
  • VLOOKUP(“Kivell”,$C$5:$E$14,2,FALSE)→ Here, 2 is the column index number for the Item Column and FALSE represents the exact match
  • VLOOKUP(“Kivell”,$C$5:$E$14,2,FALSE)→ becomes
    • OutputBinder

  • Finally, press ENTER.  The output will be shown in cell F17.

output after applying VLOOKUP function with named range for item column

Here, we got the Item associated with Kivell which is Binder. So in this way we can easily create a named range and use this named range as a reference in our formulas to get the value.

Read More: How to Use Named Range in Excel VBA (2 Ways)


2. Applying Named Range with VLOOKUP Function for Multiple Sheets

In this method I will explain how to use Excel VLOOKUP function with named range across multiple sheets. We will use the IFNA, INDIRECT, INDEX, MATCH, and COUNTIF functions along with the VLOOKUP function to do the task. Suppose we have multiple sheets and we want to apply the VLOOKUP function at a time with a named range.

We have two different sheets named Cycle1 and Cycle2. Both have 11 rows and 6 columns named Order ID, Rep Name, Region, Item, Units and Unit Cost.

product list for cycle1

The Cycle2 sheet contains the same number of rows and columns.

product list for cycle2

We have another sheet named Multiple Sheet, where we will apply our formula. After that, let’s carry out the following steps.

Steps:

  • First of all, select cells B8 and B9 to write down all the lookup sheet names.

selecting cells to write down lookup sheet names

  • Then, write down lookup sheet names which are Cycle1 and Cycle2.

writing down lookup sheet names

  • Afterward, select Cycle1 and drag your cursor to select Cycle2.
  • After that, type your preferred reference name in Name Box( list in our case).

naming range as a list

  • In addition, select cell B5 to enter Order ID.

selecting cell to enter order id

  • Following this, enter your preferred Order ID (E02968).

entering order id

  • Moreover, select cell C5 to enter the VLOOKUP formula.

selecting cell to enter VLOOKUP formula with named range

  • At this point, insert the following formula in cell C5 to apply the VLOOKUP formula using the named range.
=IFNA(VLOOKUP($B$5, INDIRECT(“‘”&INDEX(list, MATCH(1, –(COUNTIF(INDIRECT(“‘”& list&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)

 Here, list is the named range of lookup sheets Cycle1 and Cycle2.

inserting VLOOKUP formula with named ranges across multiple sheets

Formula Breakdown

  • $B$5 → is the lookup value that we are looking for.
  • IFNA(VLOOKUP($B$5, INDIRECT(“‘”&INDEX(list, MATCH(1, –(COUNTIF(INDIRECT(“‘”& list&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX(list, MATCH(1, –(COUNTIF(INDIRECT(“‘”& list&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • list → is the named range of Cycle1 and Cycle2.
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX(list, MATCH(1, –(COUNTIF(INDIRECT(“‘”& list&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF(INDIRECT(“‘”& list&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • Cycle1 and Cycle2 → are the lookup sheets.
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF(INDIRECT(“‘”& list&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF(INDIRECT(“‘”&{“Cycle1”;“Cycle2”&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • $B$5:$B$14 → is the range of Order ID.
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF(INDIRECT(“‘”&{“Cycle1”;“Cycle2”&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF(INDIRECT({“‘Cycle1’!$B$5:$B$14”;“‘Cycle2’!$B$5:$B$14”}), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • $B5 → is the cell value of Order ID which is E02968.
  • INDIRECT({“‘Cycle1’!$B$5:$B$14”;“‘Cycle2’!$B$5:$B$14”}) → The INDIRECT function returns a valid cell reference from a given text string.
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF(INDIRECT({“‘Cycle1’!$B$5:$B$14”;“‘Cycle2’!$B$5:$B$14”}), $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF({#Value!;#Value!}, $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF({#Value!;#Value!}, $B5)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • FNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF({#Value!;#Value!}, “E02968”)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • COUNTIF({#Value!;#Value!}, “E02968”) → The COUNTIF Function returns how many times the value occurred based on the criteria.
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –(COUNTIF({#Value!;#Value!}, “E02968”)>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –({0;1}>0), 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → therefore, it becomes
      • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –({FALSE;TRUE}, 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • MATCH(1, –({FALSE;TRUE}, 0)) → The MATCH Function searches for a specific item in the range and returns the item’s position. In this case , MATCH returns in which worksheet lookup value is allocated.
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9, MATCH(1, –({FALSE;TRUE}, 0)) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9,2) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → which therefore becomes
      • IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&”Cycle2”&”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → that becomes
        • IFNA(VLOOKUP(“E02968”, INDIRECT(“’Cycle2”&”‘!$B$5:$G$14″), 4, FALSE),”Not found”)
  • INDIRECT(“’Cycle2”&”‘!$B$5:$G$14”) → The INDIRECT Function returns the total range of cells of the worksheet Cycle2 in which the lookup value is present.
  • IFNA(VLOOKUP(“E02968”, INDIRECT(“’Cycle2”&”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → becomes
    • IFNA(VLOOKUP(“E02968”,Cycle2!$B$5:$G$14, 4, FALSE),”Not found”) → which becomes
      • IFNA(“Pencil”,”Not found”)
  • IFNA(“Pencil”,”Not found”) → The IFNA Function finds out #N/A errors and returns the value we have specified which is ‘Pencil’
    • OutputPencil

  • Finally, press ENTER The output will be shown in cell C5.

output after applying VLOOKUP function with named range across multiple sheets

Therefore, we got the output in cell C5 which is the ItemPencil’ corresponding to Order ID E02968 from Cycle2 sheet. We could easily do that by applying named ranges in the VLOOKUP function.

After that, you can also find Unit Cost corresponding to Order ID E02968 by applying the same formula with a little bit of change. To do so carry out the below steps.

  • First, select cell D5 to enter the VLOOKUP formula.

selecting cell to enter VLOOKUP formula with named range

  • After that, insert the following formula in cell D5 to apply the VLOOKUP formula using the named range.
=IFNA(VLOOKUP($B$5, INDIRECT(“‘”&INDEX(list, MATCH(1, –(COUNTIF(INDIRECT(“‘”& list&”‘!$B$5:$B$14″), $B5)>0), 0)) &”‘!$B$5:$G$14″), 6, FALSE),”Not found”)

 The only changes in this formula is , here we have put 6 for column index number for column Unit Cost.

inserting VLOOKUP formula for Unit Cost column with named ranges across multiple sheets

  • Then, press ENTER The output will be shown in cell D5.

output after applying VLOOKUP function with named range across multiple sheets

Here, we got the output in cell D5 which is the Unit Cost$43’ corresponding to Order ID E02968 from Cycle2 sheet. So, in this way we can retrieve the corresponding item from multiple sheets using the VLOOKUP function with name ranges. It makes calculation easier and quicker.

Read More: How to Name a Range in Excel (5 Easy Tricks)


Practice Section

You can use the following dataset to practice by yourself. You can modify it too if it is necessary. Hope it will help you to practice how to use the named range in the VLOOKUP function effectively.

dataset for practicing using named range in VLOOKUP function in Excel


Conclusion

In this article, I tried my utter level best to show you how to use Excel VLOOKUP Function with named range across multiple sheets and retrieve corresponding information. I hope this article will be helpful for you to solve these types of problems. You have any suggestions and query leave a comment below and you can follow ExcelDemy for more articles like this one.


Related Articles

Nujat Tasnim

Nujat Tasnim

Hello everyone!! Welcome to my profile. I'm currently working and researching Microsoft Excel, and I'll be sharing articles about it here. My most recent academic qualification was BSc in Computer Science and Engineering from American International University-Bangladesh.I have a bachelor's degree in computer science and am really interested in research and development. I'm always enthusiastic about picking up new knowledge and abilities. I enjoy cooking and experimenting with new recipes in my free time.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo