Pinterest Pixel

Named Ranges with Vlookup Formula

  What does it do? Searches for a value in the first column of a table array... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

 

What does it do?

Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.

Formula breakdown:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What it means:

=VLOOKUP(this value, in this Named Range, and get me value in this column, Exact Match/FALSE/0])


 

A Named Range makes it easier to understand Excel formulas, especially if the said formula contains an array argument.

A Named Range can be a cell, a cell range, a Table, a function, or a constant.

Watch this video on YouTube to learn more about Vlookup Range:

Named Ranges with Vlookup Formula | MyExcelOnline

Follow the step-by-step tutorial on how to use Vlookup with named range and make sure to download the workbook and follow along:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: To define a Named Range in Excel you need to select the cell/cell range/Table/function/constant and go to the Name Box which is located on the top left-hand corner of the workbook – next to the Formula Bar.

Named Ranges with Vlookup Formula

 

See also  How to use ChatGPT with Microsoft Excel - The Ultimate Guide

STEP 2: In here you can name your range whatever you like (make sure there are no spaces) and press Enter.  You can view your Named Range by clicking on the drop-down box in the Name Box.  In our example, we will give this a name of StockList.

Named Ranges with Vlookup Formula

You can also view/edit/delete your Named Range by going to the Formulas tab in the Ribbon menu and selecting Name Manager.

 

STEP 3: Now that you are all set, each time you are creating a formula, like a Vlookup formula, it is best to use a Named Range as it makes the formula easier to understand and maintain.

We need to enter the Vlookup function:

=VLOOKUP(

Named Ranges with Vlookup Formula

The Vlookup arguments:

lookup_value

What are we looking for?

Reference the cell that contains the text or value:

=VLOOKUP(G15,

Named Ranges with Vlookup Formula

table_array

From which list are we doing a lookup on?

The formula for Excel VlookUp Named Range will be:

=VLOOKUP(G15, StockList,

Named Ranges with Vlookup Formula

col_index_num

From which column do we want to retrieve the value?

See also  IF Function Combined With The AND Function

We want to retrieve the Price which is the SECOND column from our table array:

=VLOOKUP(G15, StockList, 2,

Named Ranges with Vlookup Formula

[range_lookup]

Do we want an exact match?

Place in FALSE to signify that we want an exact match:

=VLOOKUP(G15, StockList, 2, FALSE)

Named Ranges with Vlookup Formula

This is how the price will now dynamically change based on your selection with Vlookup using named range:

Named Ranges with Vlookup Formula

Further Learning:

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!