Lookup Formula

B

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.
 
L

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.
 
B

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
 
B

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
 
B

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.
 
B

Byron720

My bad. The formula works fine. Thank you

Byron720 said:
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.
Click to expand...
 
Top