How to Use 'Named Range' in Excel VLOOKUP Function (2 Ways)

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.

selecting the range to apply named range

Right-click 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:

=VLOOKUP(B17,SalesInformation,3,FALSE)

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:

=VLOOKUP(B17,SalesInformation,2,FALSE)

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.

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:

=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, 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”)

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’
- 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:

=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 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.

Practice Section

You can use the following dataset to practice.

Download the Practice Workbook

You can download the practice workbook from here:

VLOOKUP Named Range.xlsx

<< Go Back to Named Range | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Use ‘Named Range’ in Excel VLOOKUP Function (2 Ways)

Method 1 – Using Excel VLOOKUP Function with Named Range

Method 2 – Applying Named Range with VLOOKUP Function for Multiple Sheets

Practice Section

Related Articles

Leave a reply Cancel reply