How to pickup data from Multiple Worksheet using Data

[Murali Nair]

I have a typical common problem, my data eg. Sales Registers which when exported to Excel gets copied in 2 or 3 worksheet as the data is huge monthly a lac or more.

Using vlookpup based on certain criteria i need to pick up data from this exported files. But as the exported data is in 3 worksheet i have to manually use Vlookup 1 sheet at a time and then combine them in 1 file.

Is there any way of using vlookup for multiple worksheet.


Thanks for allowing me to post. I searched ur forum but did not find any solution for this.

Kindly help out.

 

[Sajan]

Hi Murali Nair,

Not sure I am understanding your question completely. Are you trying to lookup a value in multiple, similarly setup worksheets? If so, you may want to consider using SUMPRODUCT() instead of the VLOOKUP function.


For example, SUMPRODUCT((Sheet1!A1:A20=ValueToLookFor)*(Sheet1!B1:B20)) will return the sum of values in column B on Sheet1 where Column A matches the "ValueToLookFor".


You can then add the SUMPRODUCT results from multiple sheets.


For example, SUMPRODUCT((Sheet1!.....)) + SUMPRODUCT((Sheet2!...)) + SUMPRODUCT((Sheet3!...)) will return the sum of values (or the single value, if Column A is unique across all sheets).


If I misunderstood your question, please post a sample workbook (perhaps with no more than 5 rows per sheet) to illustrate your question.


Cheers,

Sajan.