Lookup Formula

[Byron720]

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.

 

[Luke M]

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

 

[Bernard Liengme]

Assuming the lists are in column A in each worksheet
=IF(ISNA(MATCH(A1,Returnable!A:A,0)),"","Returnable")&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 Liengme]

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

 

[Byron720]

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.

 

[Byron720]

My bad. The formula works fine. Thank you

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.