Return Yes if text in cell is found in column on seperate sheet

[HolyLemon]

On Sheet 1 I have a list of codes in column I.

On sheet 2 I have the index to the list of codes with their full text translations but I have many more codes in the index than appear in the list on sheet one. I need to insert a column that will allow me to filter out the useless codes.

On sheet 2 the codes at located in column A

I am looking for a formula I can put into sheet two cell b2 searching for the code listed in a2 that will return a "Yes" value if the code appears in the list on sheet 1 and a "No" value if it doesnt. Ideally it would be possible for me to then copy down the formula to the cells below so that I can then apply the filter to that column and remove the codes I dont need.

Any help with this is much appreciated!!

Thanks very much,
Harry

 

[edvwvw]

Try this in sheet 2 B1 and drag down

=IF(ISERROR(MATCH(A1,Sheet1!$A$1:$A$25,0)),"NO","YES")

edvwvw

 

[anglais428]

Not sure I follow 100% but for your second point you could use the following:

=IF(ISNA((VLOOKUP(Firstcell,lookup_range,1,FALSE)=firstcell)),"no",IF(Firstcell,lookup_range,1,FALSE)=firstcell,"yes","no"))

 

[HolyLemon]

Neither of these appear to be working. Not sure what to do!!!

 

[edvwvw]

Can you post a small sample.

The solution I suggest will put NO in sheet 2 column B if it finds the same code in sheet 1 column A .

I presume that you changed the range to suit the number of codes in sheet 1.

=IF(ISERROR(MATCH(A2,Sheet1!$A$2:$A$(end of codes),0)),"NO","YES")


edvwvw

 

[anglais428]

You need to place what cells you require into my formula. It should work - I have tested it

 

[HolyLemon]

http://www.box.net/shared/iidylxbbhu

This is the file, i have had to remove the columns for security reasons, any help would be very appreciated!!!!!

 

[anglais428]

first you need to cleanse your text data. In column J or wherever insert =TRIM(SUBSTITUTE(I2,CHAR(160),""))

On Sheet2

in column D insert =TRIM(SUBSTITUTE(A2,CHAR(160),""))

Then do the lookup based on these two new columns

 

[edvwvw]

anglais428

I see that you got to the reason first

edvwvw

 

[anglais428]

Yes, I am sure both our formula's will now work as we had originally thought.