Help me for exact Formula

[vlook fomula]

I have data on sheet1 & sheet2 and I want to apply formula on sheet3 to link
the Qty. while I have try with Vlookup and Sumif but got success only as
Vlookup(sheet1…..)+Vlookup(sheet2…..)

Sheet1
Code Imported Material Qty
101 A 5
300 B 10
400 C 7

Sheet2
Code Local Material Qty
102 X 3
104 Y 8
203 Z 2

Sheet3
Code Material used Qty
101 A 5
102 X 3
104 Y 8
203 Z 2
300 B 10
400 C 7

Thanks
Zafar

 

[Roger Govier]

Hi

Assuming that your codes will always exits in one sheet or the other.
First create 2 named ranges Insert>Name>Define
Name tbl1
Refers to =Sheet1$A$1:INDEX(Sheet1$C:$C,COUNTA(Sheet1!$A:$A))
Name tbl2
Refers to =Sheet2$A$1:INDEX(Sheet2$C:$C,COUNTA(Sheet2!$A:$A))

On Sheet3 in cell C2
=IF(COUNTIF(Sheet1!A:A,Sheet3!A2),
VLOOKUP(Sheet3!A2,tbl1,3,0),VLOOKUP(Sheet3!A2,tbl2,3,0))
Copy down as far as required

 

[Roger Govier]

Hi

Assuming that your codes will always exits in one sheet or the other.
First create 2 named ranges Insert>Name>Define
Name tbl1
Refers to =Sheet1$A$1:INDEX(Sheet1$C:$C,COUNTA(Sheet1!$A:$A))
Name tbl2
Refers to =Sheet2$A$1:INDEX(Sheet2$C:$C,COUNTA(Sheet2!$A:$A))

On Sheet3 in cell C2
=IF(COUNTIF(Sheet1!A:A,Sheet3!A2),
VLOOKUP(Sheet3!A2,tbl1,3,0),VLOOKUP(Sheet3!A2,tbl2,3,0))
Copy down as far as required

 

[vlook fomula]

Hi,
Thanks problem is resolved only in this condition if i have data on two
sheets and need result on sheet3, but now i have data more than two sheets
and i need link on a final sheet and in this condition i could not found
result

 

[vlook fomula]

Hi,
Thanks problem is resolved only in this condition if i have data on two
sheets and need result on sheet3, but now i have data more than two sheets
and i need link on a final sheet and in this condition i could not found
result

 

[Roger Govier]

Hi

Then I think you would need to do the following.
Title your summary sheet as Summary

It might be easier to maintain, and avoid the possibility of the formula
growing too long for any one cell, if you placed these lookups in separate
cells to the right of where you want your answer - for example in cell N1 of
sheet Summary enter
="Sheet"&COLUMN(A1)&"!A:C"

Copy across through cells O1, P1, Q1 for as many sheets as you require to
lookup.
in cell N2 enter
=IF(COUNTIF(INDIRECT(N$1),$A2),VLOOKUP($A2,INDIRECT(N$1),3,0),0)

Copy across for as many columns as required, then down for as many rows as
required

Then in cell C2 of Summary, enter
=SUM(N2:Z2)
and copy down as far as required

 

[Roger Govier]

Hi

Then I think you would need to do the following.
Title your summary sheet as Summary

It might be easier to maintain, and avoid the possibility of the formula
growing too long for any one cell, if you placed these lookups in separate
cells to the right of where you want your answer - for example in cell N1 of
sheet Summary enter
="Sheet"&COLUMN(A1)&"!A:C"

Copy across through cells O1, P1, Q1 for as many sheets as you require to
lookup.
in cell N2 enter
=IF(COUNTIF(INDIRECT(N$1),$A2),VLOOKUP($A2,INDIRECT(N$1),3,0),0)

Copy across for as many columns as required, then down for as many rows as
required

Then in cell C2 of Summary, enter
=SUM(N2:Z2)
and copy down as far as required