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.
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.
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.
 After that, Rightclick on your mouse and select Define Name.
 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.
Finally, if we select our range we can see the name of the range in the Name Box and that is SalesInformation.
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.
Therefore, it will take you to the same exact New Name screen where you can define your reference name and create a 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.
 Furthermore, enter Rep Name in cells B17:C17.
 After that, select cells D17:E17 to enter the VLOOKUP formula.
 After that, insert the following formula in cells D17:E17 to apply the VLOOKUP formula using the named range.
Here, SalesInformation is the named range that is previously created.
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
 Output → 89
 Finally, press the ENTER The output will be shown in cells D17:E17.
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.
 Thereafter, insert the following formula in cell F17 to apply the VLOOKUP formula using the named range.
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.
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
 Output → Binder
 Finally, press ENTER. The output will be shown in cell F17.
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.
The Cycle2 sheet contains the same number of rows and columns.
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.
 Then, write down lookup sheet names which are Cycle1 and Cycle2.
 Afterward, select Cycle1 and drag your cursor to select Cycle2.
 After that, type your preferred reference name in Name Box( list in our case).
 In addition, select cell B5 to enter Order ID.
 Following this, enter your preferred Order ID (E02968).
 Moreover, select cell C5 to enter the VLOOKUP formula.
 At this point, insert the following formula in cell C5 to apply the VLOOKUP formula using the named range.
Here, list is the named range of lookup sheets Cycle1 and Cycle2.
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”)
 IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&”Cycle2”&”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → that becomes
 IFNA(VLOOKUP(“E02968”, INDIRECT(“‘”&INDEX($B$8:$B$9,2) &”‘!$B$5:$G$14″), 4, FALSE),”Not found”) → which therefore becomes
 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(VLOOKUP(“E02968”,Cycle2!$B$5:$G$14, 4, FALSE),”Not found”) → which becomes
 IFNA(“Pencil”,”Not found”) → The IFNA Function finds out #N/A errors and returns the value we have specified which is ‘Pencil’
 Output → Pencil
 Finally, press ENTER The output will be shown in cell C5.
Therefore, we got the output in cell C5 which is the Item ‘Pencil’ 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.
 After that, insert the following formula in cell D5 to apply the VLOOKUP formula using the named range.
The only changes in this formula is , here we have put 6 for column index number for column Unit Cost.
 Then, press ENTER The output will be shown in cell D5.
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.
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
 How to Edit Named Range in Excel (With Easy Steps)
 Excel VLOOKUP Exercises: 10 Tricky Problems
 Create Dynamic Named Range in Excel (2 Easy Ways)
 Apply Double VLOOKUP in Excel (4 Quick Ways)
 How to Delete Named Range Excel (3 Methods)
 VLOOKUP Not Working (8 Reasons & Solutions)
 Check If Named Range Exists with Excel VBA