vlookup from multiple sheets

[Radhakant Panigrahi]

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

 

[ozgrid.com]

See http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[L. Howard Kittle]

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