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 list, and get me value in this column, Exact Match/FALSE/0])


Excel`s VLOOKUP function is arguably the most used function in Excel but can also be the most tricky one to understand.  I will show you a VLOOKUP example and in a few steps you will be able to extract values from a table and use them to do your custom reports and analysis.

You will be using VLOOKUP with confidence after this tutorial!

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We need to enter the VLOOKUP function in a blank cell:

=VLOOKUP(

VLOOKUP formula

 

STEP 2: The VLOOKUP arguments:

Lookup_value

What is the value that you want to look for?

In our first example, it will be Laptop, so select the Item name

=VLOOKUP(G15,

VLOOKUP formula

Table_array

What is the table or range that contains your data?

Make sure to select the stock list table so that our VLOOKUP formula will search here

=VLOOKUP(G15, B14:D17,

VLOOKUP formula

Ensure that you press F4 so that you can lock the table range.

=VLOOKUP(G15, $B$14:$D$17,

VLOOKUP formula

Col_index_num

What is the column that you want to retrieve the value from?

Since we want to get the price, our price is on the 2nd column of our source data

=VLOOKUP(G15, $B$14:$D$17, 2,

VLOOKUP formula

Range_lookup

What kind of matching do you need?

We want an exact match of the Laptop text so make sure FALSE is selected.

=VLOOKUP(G15, $B$14:$D$17, 2, FALSE)

VLOOKUP formula

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

VLOOKUP formula

You now have all of the results!

 

How to Use the VLOOKUP Formula in Excel

Vlookup_Intro3

 

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

Free Excel Formulas & Functions Webinar Train... In this popular Free Excel Formulas & Functions Webinar Training which goes for 60 minutes you will learn the MUST KNOW Excel Formulas to ADVANCE your Excel skills! You will learn the following Excel Formulas & Functions: IF, SUMIF, VLOOKUP, INDEX/MATCH + MORE...! P...
Consolidate with 3D Formulas in Excel 3D Formulas or References in Excel are a great way to consolidate data from multiple sheets. 3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function. Formula breakdown: SUM(Sheet1:Sheet4!A1) ...
Index Match 2 Criteria with Data Validation We can use the INDEX-MATCH formula and combine it with Data Validation drop down menus to return a value based on 2 criteria. This is a little advanced so you will need to drop what you are doing and really focus.  Let's go... First we need to convert our data into an Excel...
Excel´s EndOfMonth function   What does it do? Returns the last day of the month after a start date Formula breakdown: =EOM(start_date, months) What it means: =EOM(Your Start Date, enter 0 for current end of month, 1 for the next end of month, and so on...) The EOMONTH (EndO...