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" wrote:

Hi

Assuming that your codes will always exits in one sheet or the other.
First create 2 named ranges InsertNameDefine
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
--
Regards
Roger Govier

"vlook fomula" wrote in message
...
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