I have sheet1 and sheet2 both sheets have same data but there are some new
material # in sheet1.

Problem is when apply Vlookup formula in sheet2 then quantities of new
material numbers do not comes because these new materials numbers do not
exist in sheet2.

Is there any method? To trace new or diff. materials numbers those are
exist in sheet1

Regards

Zafar

Not sure whether I have understood your query correctly.

Try the below formula which will lookat Sheet1 and return whether the
material # exists in Sheet1 or not...Reoplace the exist comment with your
VLOOKUP() formula....

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"New material number","Exist. Use a formula")

--
Jacob (MVP - Excel)


"vlook fomula" wrote:

I have sheet1 and sheet2 both sheets have same data but there are some new
material # in sheet1.

Problem is when apply Vlookup formula in sheet2 then quantities of new
material numbers do not comes because these new materials numbers do not
exist in sheet2.

Is there any method? To trace new or diff. materials numbers those are
exist in sheet1

Regards

Zafar

thanks Jacob for reply.......(my english is weak sorry for that)

Actually sheet1 having new data and sheet2 have old data, when i am
applying vlookup to take qty in sheet2 from sheet1 then some quantites do not
coming and these are quantites of new materials. so i always back to sheet1
to search new materials numbers and then enter in sheet 2.

is there any easy way to search how many new materials are exist in sheet1

regards

"Jacob Skaria" wrote:

Not sure whether I have understood your query correctly.

Try the below formula which will lookat Sheet1 and return whether the
material # exists in Sheet1 or not...Reoplace the exist comment with your
VLOOKUP() formula....

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"New material number","Exist. Use a formula")

--
Jacob (MVP - Excel)


"vlook fomula" wrote:

I have sheet1 and sheet2 both sheets have same data but there are some new
material # in sheet1.

Problem is when apply Vlookup formula in sheet2 then quantities of new
material numbers do not comes because these new materials numbers do not
exist in sheet2.

Is there any method? To trace new or diff. materials numbers those are
exist in sheet1

Regards

Zafar

Suppose you have material numbers in Col A in both sheets...In Sheet1 ColB
cell B1 apply the below formula...which will return "NEw" if the material
number do not exist in Sheet2......Once done you can filter down the items
with"NEw" status

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"New","")


--
Jacob (MVP - Excel)


"vlook fomula" wrote:

thanks Jacob for reply.......(my english is weak sorry for that)

Actually sheet1 having new data and sheet2 have old data, when i am
applying vlookup to take qty in sheet2 from sheet1 then some quantites do not
coming and these are quantites of new materials. so i always back to sheet1
to search new materials numbers and then enter in sheet 2.

is there any easy way to search how many new materials are exist in sheet1

regards

"Jacob Skaria" wrote:

Not sure whether I have understood your query correctly.

Try the below formula which will lookat Sheet1 and return whether the
material # exists in Sheet1 or not...Reoplace the exist comment with your
VLOOKUP() formula....

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"New material number","Exist. Use a formula")

--
Jacob (MVP - Excel)


"vlook fomula" wrote:

I have sheet1 and sheet2 both sheets have same data but there are some new
material # in sheet1.

Problem is when apply Vlookup formula in sheet2 then quantities of new
material numbers do not comes because these new materials numbers do not
exist in sheet2.

Is there any method? To trace new or diff. materials numbers those are
exist in sheet1

Regards

Zafar

Thanks & God bles u problem resolved

"vlook fomula" wrote:

I have sheet1 and sheet2 both sheets have same data but there are some new
material # in sheet1.

Problem is when apply Vlookup formula in sheet2 then quantities of new
material numbers do not comes because these new materials numbers do not
exist in sheet2.

Is there any method? To trace new or diff. materials numbers those are
exist in sheet1

Regards

Zafar