# DataGenX

My scrapbook about almost anything I stumble upon but mostly around Cloud, K8s, OpenShift, DataScience, Machine Learning, Golang, Python, Data Analytics, DataStage, DWH and ETL Concepts. If you find any pages useful don't forget to give thumbs-up :)

## Saturday, August 22, 2015

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 ---

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 ''