Method 1 – Using Excel VLOOKUP Function with Named Range
Steps:
 Select the range for which you want to give a name. In this dataset, C5:E14 is the range we chose.
 Rightclick and select Define Name.
 A New Name tab will pop up.
 Enter your preferred reference name in the Name field. SalesInformation is the reference name in our case.
 Click OK.
If we select our range, the name of the range, SalesInformation, appears in the Name Box.
You can create a named range in another way.
 Select the range (C5:E14) that you want to give a name.
 Go to the Formulas tab and click on the Define Name dropdown.
 Select Define Name.
It will take you to the same exact New Name screen where you can define your reference name and create a named range.
To use this created named range in the VLOOKUP function,
 Select cell B17:C17 to enter the Rep Name.
 Enter the Rep Name in cells B17:C17.
 Select cells D17:E17 to enter the VLOOKUP formula.
 Insert the following formula in cells D17:E17 to apply the VLOOKUP formula using the named range:
Here, SalesInformation is the named range that was 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
 Press ENTER. The output will be shown in cells D17:E17.
Here, we have the Sold Units of Kivell. We just used the created named range in the VLOOKUP function, which gave us the required information. This is the benefit of using a named range in the VLOOKUP function.
You can also get the Item by applying the same formula with a little change.
 Select cell F17 to apply the formula.
 Insert the following formula in cell F17 to apply the VLOOKUP formula using the named range:
Here, SalesInformation is the named range that was previously used. The only change 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
 Press ENTER. The output will be shown in cell F17.
Here, we have the Item associated with Kivell, which is Binder. So, we can easily create a named range and use it as a reference in our formulas to get the value.
Read More: Excel Reference Named Range in Another Sheet
Method 2 – Applying Named Range with VLOOKUP Function for Multiple Sheets
We have two different sheets, Cycle1 and Cycle2. Both have 11 rows and 6 columns, each containing the Order ID, Rep Name, Region, Item, Units, and Unit Cost.
The Cycle2 sheet contains the same number of rows and columns.
We will apply our formula in another sheet named Multiple Sheet.
Steps:
 Select cells B8 and B9 to enter all the lookup sheet names.
 Enter the lookup sheet names Cycle1 and Cycle2.
 Select Cycle1 and drag your cursor to select Cycle2.
 Enter your preferred reference name in the Name Box( list in our case).
 Select cell B5 to enter the Order ID.
 Enter your preferred Order ID (E02968).
 Select cell C5 to enter the VLOOKUP formula.
 Insert the following formula in cell C5 to apply the VLOOKUP formula using the named range:
Here, the 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
 Press ENTER. The output will be shown in cell C5.
We have the output in cell C5, which is the Item ‘Pencil’ corresponding to Order ID E02968 from the Cycle2 sheet. We could easily do that by applying named ranges in the VLOOKUP function.
After that, you can also find the Unit Cost corresponding to Order ID E02968 by applying the same formula with a little bit of change.
 Select cell D5 to enter the VLOOKUP formula.
 Insert the following formula in cell D5 to apply the VLOOKUP formula using the named range:
The only change in this formula is that we have put 6 as the column index number for the column Unit Cost.
 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 the Cycle2 sheet. So, we can retrieve the corresponding item from multiple sheets using the VLOOKUP function with name ranges. It makes calculation easier and quicker.
Read More: Excel INDIRECT Function with Named Range
Practice Section
You can use the following dataset to practice.
Download the Practice Workbook
You can download the practice workbook from here:
Related Articles
 How to Ignore Blank Cells in Named Range in Excel
 How to Create Dynamic Named Range in Excel
 How to Use Dynamic Named Range in an Excel Chart
<< Go Back to Named Range  Excel Formulas  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!