Look up for all sheets
 

Hi,
How can use Vlookup formula for searching in Workbook in Al Sheets.

Will You help me.

Thanks.


--
sgm020
------------------------------------------------------------------------
sgm020's Profile: http://www.excelforum.com/member.php...o&userid=26226
View this thread: http://www.excelforum.com/showthread...hreadid=395244

You can't. VLOOKUP can search only on one sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"sgm020"
wrote in message
...

Hi,
How can use Vlookup formula for searching in Workbook in Al
Sheets.

Will You help me.

Thanks.


--
sgm020
------------------------------------------------------------------------
sgm020's Profile:
http://www.excelforum.com/member.php...o&userid=26226
View this thread:
http://www.excelforum.com/showthread...hreadid=395244

_ASSUMPTIONS:_

1) You have 5 lookup tables

2) Each table is located in a different sheet

3) On each sheet, A1:B100 contains your lookup table


_FORMULA:_

1) If Column B of your lookup tables contains numerical values...

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A5&"'!A1:A100"), B1,INDIRECT("'"&A1:A5&"'!B1:B100")))

2) If Column B of your lookup tables contains text (or numerical)
values...

=VLOOKUP(B1,INDIRECT("'"&INDEX(A1:A5,MATCH(TRUE,CO UNTIF(INDIRECT("'"&A1:A5&"'!A1:A100"),B1)0,0))&"' !A1:B100"),2,0)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

For both formulas, A1:A5 contains a list of sheet names, and B1
contains the lookup value.

Hope this helps!

sgm020 Wrote:
Hi,
How can use Vlookup formula for searching in Workbook in Al Sheets.

Will You help me.

Thanks.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=395244

Thanks, everybody.


--
sgm020
------------------------------------------------------------------------
sgm020's Profile: http://www.excelforum.com/member.php...o&userid=26226
View this thread: http://www.excelforum.com/showthread...hreadid=395244