Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
_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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros for Protect/Unprotect all sheets in a workbook | Excel Discussion (Misc queries) | |||
How to total itmes if they fall between a date range | Excel Worksheet Functions | |||
How to total items if they fall between a date range | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) |