Powerutils 1.9 is now available for download containing 99 utils.
Vlookup formula in Excel
HOW TO USE VLOOKUP : Example
We have two files as given below.
File1
Part No
Desc
257340100101
Tyre A
257340200111
Tyre B
257440200102
Tyre C
257440200103
Tyre D
257440200106
Tyre E
File2
Part No
Rate Issued Qty
257340100101
4345 200
257440200102
4400 400
257440200103
3908 500
257440200103
3400 230
Our aim in this exercise is to get the Description
of all the Part numbers in File 2 from File1.
1. Identify which is the Master File and which is the Transaction File. Here File1 which contain a list of Part Numbers and Description is taken as the Master file since we will be capturing data from there and putting it in File2.
2. What is the Key Column. The key column is the one which is common in both files and the search will be based on that column, in this case it is the 1st column in File1 and File2 ie. Part No.
3. Put a Range Name in the Transaction File keeping the Key columns the 1st Column
(table). This is a step which Hardcore users avoid but I advice that this is
safer way. To put a range name first select the range from the header Part No.
to the last column and last row ending with Tyre E. In the edit box in Excel
under font you get a address edit box, type a range name which should not be a
word reserved by Excel for its own purpose, let us take Table and press enter
button (VV important).
4. Put the formula in a vacant column in the Transaction file.
=Vlookup(Cell reference,Search Range Name,Offset column,False) This
formula has 4 parts within the brackets. The 1st one is the cell reference of
the key field which we will be looking at for seacrhing in the other file, in
our case it is the part no, the 2nd parameter is the look up range ie the area
where we are going to search. In our case it is file 2 range name being
"Table". The 3rd parameter is the the column number of the data which
we want to capture. In this case it is the "desc' which is the 2nd column
so put 2. The last parameter should be put as "false" in all cases.
The main reason for putting false is to avoid any errors in case of duplicate
data.
5. In case a Part No in the master file is not there in the transaction file it will show a #NA.
6. In case #NA appearing in all the cells then check for problems:-
a) Formula is correct in all respect.
b) The Key Column in both the files are either number or string using the formula
'=isnumber(cell address) . You can use the PowerUtils program, Miscellanious ,
Check whether the Cell Value is Number.
7.For exception handling:
a)Use the Master file as the transaction file; put a range name
in file 2.
b)In file2 put the formula
"=Vlookup(Cell reference,Search Range Name,Offset column,False)"
8. The #NA now will indicate those part numbers in the current file not there in the main file.
9.You can update the Main file with all the Part No in Transaction File not there in the Main file.
The above Example in Excel file can be downloaded for seeing the live examle here.