Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup from multiple sheets
i have data in 10 different sheets (Sheet 2 to Sheet11)with identical columns.
i need to use the vlookup function in Sheet1 taking the range from column E to H from each sheet. Is there any tool whereby i can make te vookup function once for all sheets and need not to use it for 10 times. regards |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup from multiple sheets
See http://www.ozgrid.com/VBA/VlookupAllSheets.htm
-- Regards Dave Hawley www.ozgrid.com "Radhakant Panigrahi" wrote in message ... i have data in 10 different sheets (Sheet 2 to Sheet11)with identical columns. i need to use the vlookup function in Sheet1 taking the range from column E to H from each sheet. Is there any tool whereby i can make te vookup function once for all sheets and need not to use it for 10 times. regards |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup from multiple sheets
Try this which I got from Peo Sjoblom in 2006. Somewhere on the worksheet list your ten worksheets by name. Select the ten cells and in the name box name them MySheets. They will now be included in the formula. Enter the formula where you want the answer to be displayed using Ctrl + Shift +Enter. You will get { } around the formula. This called Array-Enter. If you change the formula later you will need to array-enter again. Enter the lookup value in A2 on the formula sheet. A2:A200 is the lookup table (table_array) on each of the MySheets list, adjust in the formula to suit the data on the sheets, but all must be the same on each sheet. =VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:B200"),2,0) So, it looks on each sheet for whatever is in A2 of the formula sheet and returns column B of the sheet where it is found. HTH Regards, Howard "Radhakant Panigrahi" wrote in message ... i have data in 10 different sheets (Sheet 2 to Sheet11)with identical columns. i need to use the vlookup function in Sheet1 taking the range from column E to H from each sheet. Is there any tool whereby i can make te vookup function once for all sheets and need not to use it for 10 times. regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in multiple sheets | Excel Worksheet Functions | |||
Vlookup with Multiple criteria and multiple sheets | Excel Worksheet Functions | |||
Using VLOOKUP in multiple sheets using | Excel Discussion (Misc queries) | |||
VLOOKUP across multiple sheets | Excel Discussion (Misc queries) | |||
Vlookup over multiple sheets | Excel Worksheet Functions |