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

#### HolyLemon

##### New Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try this in sheet 2 B1 and drag down

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

edvwvw

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"))

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

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

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

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

anglais428

I see that you got to the reason first

edvwvw

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

Replies
13
Views
229
Replies
9
Views
74
Replies
1
Views
51
Replies
3
Views
397
Replies
6
Views
199

1,217,386
Messages
6,136,278
Members
450,001
Latest member
KWeekley08

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back