Multi Vlookup

[earthworm]

Guys i am facing problem with vlookup

i have the data in 3 sheets

i want to sum the data of three sheets if the value matches with the 3rd

sheet what happens is if the value from sheet3 matches with value of sheet 1

then i add another vlookup so that both values if located by vlookup can be

added

like this

(vlookup)+(vlookup) but if the 2 sheet dosent have the value which is located in sheet 3 , then instead of showing sheet 1 value it shows #n/a
what i want is if the sheet 2 dosent have the value it skips the formula and give answer . i have 30 sheets so data might not be available in 30 sheets together might be possible in one sheet is there in another sheet its not there,

PLEASE GUIDE !!!!!!!!!!!

[NBVC]

You say you have 3 sheets... then you say you have 30 sheets... which is correct?

And how exactly are the sheets named? Is there a pattern to them or not?

also maybe post an example of a couple of the sheets and what you want to accomplish.

[earthworm]

I M TESTING DATA IN 3 SHEETS

BUT ON LARGER Scale i will be dealing with 30+ sheets

sheet name is not a problem , any sheet

i will name sheets as 1 2 3 4 5 6 7 8 etc

[NBVC]

Say, for example, you had 1 sheet.

What would your Vlookup look like?

[earthworm]

Dude its simple logic

example is already shared in the excel attached .

Please advice

[NBVC]

Well... excuse me.. I hadn't seen that you updated your original post instead of adding the attachment in a new post to follow sequence.....

... anyways, see attached.

Formula in Q9 of Sheet 1:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A10"),P9,INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!B1:B10")))

This looks at A1:A10 of each sheet 1, 2 and 3 and looks for the value that is in P9 and sums corresponding numbers from B1:B10 on each sheetl

Adjust ranges to suit and change 1:3 in the formula to encapsulate all sheets (i.e. 1:30)

[earthworm]

Well... excuse me.. I hadn't seen that you updated your original post instead of adding the attachment in a new post to follow sequence.....

... anyways, see attached.

Formula in Q9 of Sheet 1:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A10"),P9,INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!B1:B10")))

This looks at A1:A10 of each sheet 1, 2 and 3 and looks for the value that is in P9 and sums corresponding numbers from B1:B10 on each sheetl

Adjust ranges to suit and change 1:3 in the formula to encapsulate all sheets (i.e. 1:30)

can you please explain the concept of this formula

like the detail of this formula and mail me at

muhammad.faraz-1@standardchartered.com


Please explain the logic what does this formula mean

like whats indirect and all



Please tell me the formula which also work in excel 2003