My e-Notes about DataScience, Machine Learning, Python, Data Analytics, DataStage, DWH and ETL Concepts


Saturday, 22 August 2015

VLookup in MS Excel

We all agree on this, we have to use MS Excel in our day to day work whether it is technical or not. A day before I came across a Excel Function which is same as DS Lookup :-) called VLookUp. I loved this function cause this reduce my work of 30 min to few seconds. So just want to share this function here, by the way there are lot of material you can find on net for this but this is my version ---

What was my task ? 
I have to pickup a hyperlink value from another sheet by matching a doc name.
If Sheet1.Doc = Sheet2.Doc Then HyperLink Else ''

My Excel sheets were look like --

The syntax of the VLOOKUP function is:
=VLOOKUP(lookup value, table range, output column index , [true/false])

Lookup Value        --- First Key column ( A1 )
Table Range         --- Lookup Table Range ( Sheet2!$A$2:$B$15 )
Output column index --- output column number ( for my case 2 (col B))
true/false          --- True means that an approximate match is acceptable, and False means that only an exact match is acceptable.

Table Range can be defined like below --
Lookup_Sheet_Name ! First_Row_First_Col : Last_Row_Last_Col 

Suppose , your sheet name is - Sheet2
First_Row_First_Col - A2    --> $A$2
Last_Row_Last_Col  - B15  --> $B$15

Table Range will be -- Sheet2!$A$2:$B$15

So,  Our VLOOKUP function will be - 
=VLOOKUP(A1, Sheet2!$A$2:$B$15, 2 , true)

Place this function to that cell where you want to populate the value from lookup table, this will work like - 
If A1 = ( table range) then pick sheet 2 column no 2 Else ''

No comments:

Post a comment


The postings on this site are my own and don't necessarily represent IBM's or other companies positions, strategies or opinions. All content provided on this blog is for informational purposes and knowledge sharing only.
The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of his information.