vlookup for multiple sheets

[iffi]

i m just confused to use vlookup or similiar formulae for 30 sheets to extract data. which formula i need to use for this purpose ? plz provide me a formula for this task.
lookup value is in column A and table range is from A to M in all sheets with same format. Thanks

 

[VoG]

Try VLOOKAllSheets http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[MrVillareal]

Hi friend

You can try this simple illustration.
I have 3 sheets with data table for each sheet.
I named each data table as a, b, and c.
And I extract them in the other sheet. And here it is

Excel Workbook
	A	B	C
3	LookupValue	LookupTable	Amount
4	January	a	100
5	February	b	300
6	March	d	500
7	April	a	400
8	May	b	600
9	June	d	800
abd

Excel 2010

Cell Formulas
Range	Formula
C4	=VLOOKUP(A4,INDIRECT(B4),2,FALSE)
C5	=VLOOKUP(A5,INDIRECT(B5),2,FALSE)
C6	=VLOOKUP(A6,INDIRECT(B6),2,FALSE)
C7	=VLOOKUP(A7,INDIRECT(B7),2,FALSE)
C8	=VLOOKUP(A8,INDIRECT(B8),2,FALSE)
C9	=VLOOKUP(A9,INDIRECT(B9),2,FALSE)

Excel Workbook
Name	Refers To
a	=a!$A$1:$B$6
b	=b!$A$1:$B$6
d	=d!$A$1:$B$6
Workbook Defined Names

Hope it helps
HOpe your feedback

 

[iffi]

Sorry for late reply, i was out
Thanks Vog, i will try that
Thanks MrVillareal, but i m little confused in ur illustration, in ur example, Lookup value of A4 is looking up into only one sheet that is named as a, in which there is a data table then Lookup value of A5 looking up into only one sheet also that is named as b, but this example doesn't solve my problem, i want the Lookup value to look up into 30 sheets, if it doesn't find the lookup value in sheet1 then it should move to 2nd sheet to look up that lookup value and extract data or maybe i m wrong to understand ur example if so could u plz explain me where i m wrong ? Thanks

 

[MrVillareal]

Hi

Try this alternative presentation. I dont know if I get it right but are you trying to sum all values in one sheet for 30 sheets?

I name each sheet such as "sa","sb","sc" and named the table according to each sheet name.

Excel Workbook
	A	B	C	D	E
1		sa	sb	sc	Total
2	January	100	200	300	600
3	February	101	201	0	302
4	March	102	202	440	744
5	April	103	203	580	886
6	May	104	204	0	308
7	June	105	205	720	1030
8	July	106	206	860	1172
sabc

Excel 2010

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,INDIRECT(B$1),2,FALSE)
C2	=VLOOKUP($A2,INDIRECT(C$1),2,FALSE)
D2	=VLOOKUP($A2,INDIRECT(D$1),2,FALSE)
E2	=SUM(B2:D2)

 

[iffi]

no i m not trying to sum, i m trying to extract the data using vlookup, again in ur presentation, the lookup value is only finding the value across one sheet only, it is not doing for multiple sheets