I have a file with two sheets: Returnable and Not Returnable. I need a
formula on a third sheet that tells me if a part is returnable or not based
on the lists.

If the part you're looking up is in A2, and your lists are in column B of
each sheet, something like
=IF(ISNUMBER(MATCH(A2,'Returnable'!B2:B100,0)),"Re turnable",IF(ISNUMBER(MATCH(A2,'Not Returnable'!B2:B100,0)),"Not Returnable","Part not found anywhere")
would work for you.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Byron720" wrote:

I have a file with two sheets: Returnable and Not Returnable. I need a
formula on a third sheet that tells me if a part is returnable or not based
on the lists.

Assuming the lists are in column A in each worksheet
=IF(ISNA(MATCH(A1,Returnable!A:A,0)),"","Returnabl e")&IF(ISNA(MATCH(A1,'Non
Returnable'!A:A,0)),"","Non Returnable")
where A1 hold the part number on the lookup sheet
Note that is the item does not occur in either list, the cell appears blank
Best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Byron720" wrote in message
...
I have a file with two sheets: Returnable and Not Returnable. I need a
formula on a third sheet that tells me if a part is returnable or not
based
on the lists.

Alternative (might be better)
=IF(COUNTIF(Returnable!A:A,Sheet1!A1),"Returnable" ,IF(COUNTIF('Non
Returnable'!A:A,Sheet1!A1),"Non Returnable", "Who Knows?"))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Byron720" wrote in message
...
I have a file with two sheets: Returnable and Not Returnable. I need a
formula on a third sheet that tells me if a part is returnable or not
based
on the lists.

This formula might work but I always get the "Part Not Found Anywhere". I
changed the ranges to A:A in both cases and still. I also checked the format
of the part #'s in both tables.

"Luke M" wrote:

If the part you're looking up is in A2, and your lists are in column B of
each sheet, something like
=IF(ISNUMBER(MATCH(A2,'Returnable'!B2:B100,0)),"Re turnable",IF(ISNUMBER(MATCH(A2,'Not Returnable'!B2:B100,0)),"Not Returnable","Part not found anywhere")
would work for you.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Byron720" wrote:

I have a file with two sheets: Returnable and Not Returnable. I need a
formula on a third sheet that tells me if a part is returnable or not based
on the lists.

My bad. The formula works fine. Thank you

"Byron720" wrote:

This formula might work but I always get the "Part Not Found Anywhere". I
changed the ranges to A:A in both cases and still. I also checked the format
of the part #'s in both tables.

"Luke M" wrote:

If the part you're looking up is in A2, and your lists are in column B of
each sheet, something like
=IF(ISNUMBER(MATCH(A2,'Returnable'!B2:B100,0)),"Re turnable",IF(ISNUMBER(MATCH(A2,'Not Returnable'!B2:B100,0)),"Not Returnable","Part not found anywhere")
would work for you.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Byron720" wrote:

I have a file with two sheets: Returnable and Not Returnable. I need a
formula on a third sheet that tells me if a part is returnable or not based
on the lists.