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 
 Enter the headings in the first row
 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.
 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:
 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.
 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.
 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.
 [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. 

Leave a Reply