Posted by: tonyteaching | April 1, 2009

Ms. Excel 2007: VLOOKUP

 

We use VLOOKUP function when we create a table that stores information about your products, or employees, or other data we want to refer to frequently.
From other cells in the workbook, you can use an Excel VLookup formula to look up data from the master table.

For example:

– to change marking scores (10, 20, 30, .. )to grades (A, B, C, D, E) ….. so we have created/had a Reference Table to compare between marks and grade

– to count Bonus based on the Sell ….so we have had a reference table how much bonus for each level of selling achievement.

STEPS TO USE VLOOKUP

As an example: I wanna have 2 columns table, every time  on the first column I type a Product’s code, on the second column on the same row will come up with the price automatically!

How to make it?

I’ll need a ‘reference list’ containing all information about product codes and the prices.

I need a VLOOKUP function (on the cell where I wanna show the result) to look up the data from the reference list.

The steps are:

Step 1. Create a ‘Reference Table’ or a ‘Lookup Table’

For example in here, I made a reference table about Product consisting information about ‘Code’, ‘Product’ name, and ‘Price’

Lookup formulas can work vertically, looking for values down a column, or they can work horizontally, looking for values across a row. In this example, the information will be stored vertically, with values down a column, and later we’ll use an Excel VLookup formula to do a vertical lookup.

A
B
C
D
1
Code
Product
Price
2
A23
Paper
5.00
3
B14
Lamp
15.00
4
A27
Desk
75.00
5
C45
Pencil
0.50
6
  1. Enter the headings in the first row
  2. The first column should contain the unique key values on which you will base the lookup. In this example, you can find the price for a specific product code.
  3. If you have other data on the worksheet, leave at least one blank row at the bottom of the table, and one blank column at the right of the table, to separate the lookup table from the other data.

Note: To make it easier to refer to the table, you can name the range (highlight all cells in reference table and on ‘Name box’ in Excel (next to formula box) add name for these cell), e.g. I gave a name of “product”

*****************

Step2: Create a VLOOKUP formula

First, let’s we understand the VLOOKUP arguments

The Excel VLOOKUP function has four arguments:

  1. lookup_value: What value do you want to look up? In this example, the product code is in cell A7, and you want to find its product name.
  2. table_array: Where is the lookup table? If you use an absolute reference ($A$2:$C$5), instead of a relative reference (A2:C5), it will be easier to copy to formula to other cells. Or, name the lookup table, and refer to it by name.
  3. col_index_num: Which column has the value you want returned? In this example, the product names are in the second column of the lookup table.
  4. [range_lookup]: Do you want an exact match? Is an approximate match okay?
    If you use TRUE as the last argument, or omit the last argument, or number zero 0 an approximate match can be returned. This example has FALSE as the last argument, so if the product code is not found, the result will be #N/A. (Note: Excel is rather forgiving, and will accept 0 instead of FALSE, and 1 instead of TRUE.) Here I recommend you using 0

for example:

= VLOOKUP (A7, $A$2:$C$5, 3, 0)

please fill this cell based on value on A7 by referring to cell range A2:C5 and take the value in column 3 of the range/table, found the approximate value

or we can give name to cell range F1:G12….by selecting the cell range and put name on name box, e.g. “product”

= VLOOKUP (A7, product, 3, 0)

Note: Data in the first column of the reference table must be in ascending order!!!

____________________________________________________________________

2nd Case:

I have a table with 3 column, On the first and second column have contained data about Product’s code and Quantity of the product…then in the third column I wanna get the total price (=price*quantity).

So the ‘price’ variable is depend on the product code in the first column and lookup the price from the reference list (that is cell range A2:C5) then multiplied by the quantity

Here I combined multiplication and VLOOKUP function

A
B
C
D
7
Code
Quantity
Total Price
8
A23
3
9
B14
5
10
A27
7
11
C45
1
12

Answer: on the third column (a cell where I wanna type the result), I add formula

= (VLOOKUP (A8, $A$2:$C$5, 3, 0))*B8

 

A
B
C
D
7
Code
Quantity
Total Price
8
A23
3
= (VLOOKUP (A8, $A$2:$C$5, 3, 0))*B8
9
B14
5
10
A27
7
11
C45
1
12

____________________________________________________________________

3rd  Case:

I wanna GRADE student paper (change from Score –> Grade)

In such situations, an approximate match is preferred, so several values will return the same result. For example, all papers with a grade of 85 or over should receive an A grade.

A
B
1
Score
Grade
2
0
F
3
50
D
4
60
C
5
75
B
6
85
A

Answer:

n this example, the lookup table is created on a sheet named Grades. To create the lookup table, enter the minimum score for each grade in column A. Enter the matching Grade in column B. Sort the Scores in Ascending order.

Cells A2:B6 were named GradeList.

The scores are entered on a sheet named Report Card, where an Excel VLOOKUP formula calculates the grade.

  1. On the Report Card sheet, in cell B4, enter the score 77.
  2. In cell C4, enter the VLOOKUP formula:
    =VLOOKUP(B4,GradeList,2)
  3. Press the Enter key, and the grade for English — B — is returned. 

 

 


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: