Wednesday, December 25, 2013

VLOOKUP for seraching and collating information

Edit - 4/22/2015 - 

1) The column we are VLOOKUP ing should be the first column of the tab. 
2)Using $ is really important, especially when we are trying to copy data to lakhs of rows. 

Learnt a bit about VLOOKUP in excel yesterday. Taking a note of it.

What I need to do?

I have two excel workbooks - "MainInfo" and "MoreInfo".

"MainInfo" looks like the following -

"MoreInfo" looks like the following - 

I need to get information related to the "EmpID" column from "MoreInfo" workbook and copy in "MainInfo". The final result should look like the following -

The Trick is to use "VLOOKUP".

To get the value of cell E2 write the following formula - 

= VLOOKUP($B2,MoreInfo!$A1:$D7,2,TRUE)

Let us check what the parameters are - 

  1. The fist parameter tells what to search - B2 - i.e. 1. 
  2.  The second parameter shows what is the result value? In our case we say that we want to select all columns in "MoreInfo" workbook. 
  3. The third parameter shows what to display within the data returned. 2 will return data from second column of the result set, 1 will return data from first column of the result set and so on.
  4. The last parameter tells whether the words matched have to be matched exactly or not.

$ is used for the east of drag and drop of formulas.

So For F2 we have - =VLOOKUP($B2,MoreInfo!$A1:$D7,3,TRUE)

and for G3 we have = =VLOOKUP($B2,MoreInfo!$A1:$D7,4,TRUE)

Note - This is really useful with larger spreadsheets.

No comments: