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.

clicking define name option

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.

Inputting preferred reference name in the new name tab

If we select our range, the name of the range, SalesInformation, appears in the Name Box.

showing the name of created named range

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.

selecting the range to create named range

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

To use this created named range in the VLOOKUP function,

Select cell B17:C17 to enter the Rep Name.

selecting cell B17 to enter rep name

Enter the Rep Name in cells B17:C17.

entering rep name

Select cells D17:E17 to enter the VLOOKUP formula.

selecting cell D17 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.

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
- Output → 89

Press ENTER. The output will be shown in cells D17:E17.

output after applying VLOOKUP function with named range

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.

selecting cell F17 to enter 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.

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
- Output → Binder

Press ENTER. The output will be shown in cell F17.

output after applying VLOOKUP function with named range for item column

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.

product list for cycle1

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

product list for cycle2

We will apply our formula in another sheet named Multiple Sheet.

Steps:

Select cells B8 and B9 to enter all the lookup sheet names.

selecting cells to write down lookup sheet names

Enter the lookup sheet names Cycle1 and Cycle2.

writing down lookup sheet names

Select Cycle1 and drag your cursor to select Cycle2.
Enter your preferred reference name in the Name Box( list in our case).

naming range as a list

Select cell B5 to enter the Order ID.

selecting cell to enter order id

Enter your preferred Order ID (E02968).

entering order id

Select cell C5 to enter the VLOOKUP formula.

selecting cell to enter VLOOKUP formula with named range

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.

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’
- Output → Pencil

Press ENTER. The output will be shown in cell C5.

output after applying VLOOKUP function with named range across multiple sheets

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.

selecting cell to enter VLOOKUP formula with named range

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.

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

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