Witty Sparks

Witty Sparks

Using Vlookup for multiple sheets in Excel 2007

Posted: 29 Apr 2009 07:45 AM PDT


Vlookup is one function which is widely used across the excel users. However, for those who are beginning to use Excel, this post would give a few tips on how to use Vlookup across multiple sheets. So, before we begin, a brief into about what vlookup does. The function is used to retrieve values corresponding to a particular value. This value might be in a particular sheet or in some other sheet. It is the latter that shall be looked into in this post.

Let's say there are a series of values in a sheet- Temp1.xls in the following manner.

vlookup-across-multiple-sheets_i

 

 

 

 

 

 

 

 

Now say, there is a series of values in another sheet named –Sheet 2

vlookup-across-multiple-sheets_ii

 

 

 

 

 

 

 

Tiger is an entry which is there in the file temp2.xls and also present in file temp1.xls, say you want to find the value corresponding to tiger in file temp1. Before you type the formula in the formula bar of file temp2.xls, make sure you have saved the file from which you want values to be searched!

Having saved the file, now for the formula:

(i) For the first parameter- Type the formula =vlookup("tiger",

 vlookup-across-multiple-sheets_i

 

 

 

 

 

 

(ii) For the second parameter- press ctrl+pagedown and switch to the file where the entry is present, select the table i.e. which would get selected in the following manner:

vlookup-across-multiple-sheets_iii

 

 

 

 

 

 

 

 

(iii) Now complete the formula =VLOOKUP (”Tiger”,Sheet1!G3:H10,2,0). This would throw the value corresponding to Tiger in the second row which is Tim in the desired cell.

In case of multiple files, don't forget to save the files

Press AL+ tab to switch to the file where the source table is located,

Select the table and enter the column number whose value corresponding to the value entered in the formula is needed

In plain words, follow the above mentioned steps and you would get your desired result!

For more tips on excel watch out this space..

These posts might be of your interest

Posted by Anonymous at 5:02 AM  

0 comments:

Post a Comment