_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