Look up for all sheets

[sgm020]

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

Will You help me.

Thanks

 

[Chip Pearson]

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


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

"sgm020" <[email protected]>
wrote in message
news:[email protected]...

 

[Domenic]

_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,COUNTIF(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 B
contains the lookup value.

Hope this helps!

 

[sgm020]

Thanks, everybody