Excel Vlookup Named Range

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.

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.

Excel Vlookup Named Range

 

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.

Excel Vlookup Named Range

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(

Excel Vlookup Named Range

The Vlookup arguments:

lookup_value

What are we looking for?

Reference the cell that contains the text or value:

=VLOOKUP(G15,

Excel Vlookup Named Range

table_array

From which list are we doing a lookup on?

Place in the Named Range of the Stock List:

=VLOOKUP(G15, StockList,

Excel Vlookup Named Range

col_index_num

From which column do we want to retrieve the value?

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

=VLOOKUP(G15, StockList, 2,

Excel Vlookup Named Range

[range_lookup]

Do we want an exact match?

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

=VLOOKUP(G15, StockList, 2, FALSE)

Excel Vlookup Named Range

The price now dynamically changes based on your selection:

Excel Vlookup Named Range

Excel Vlookup Named Range

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

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

Share on Google+

Google+

Related Posts

Advanced SUMPRODUCT Function: Conditional Sum Another great way that you can use the SUMPRODUCT function is to create a conditional sum criteria. For example, you may want to find out how much sales were made above the $3,000 transactional level.  See how easy it is with this quick SUMPRODUCT example. DOWNLOAD WORKBOOK...
Top Excel Formulas & Function Examples To Get... Below you will find many Excel formula examples for key functions like VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more. DATE & TIME DateDif EndOfMonth Time Weekday Workday ...
Create a Named Range in Excel Whenever I work with spreadsheets, there's no escaping the fact that I have to work with a lot of ranges. Some of these ranges I have to reuse time and time again to create different calculations! Good thing there are Named Ranges in Excel! By using a Named Range, you ca...
How To Use INDEX-MATCH Formula   What does it do? Searches the row position of a value/text in one column (using the MATCH function) and returns the value/text in the same row position from another column to the left or right (using the INDEX function) Formula breakdown: =INDEX(array, MATCH...