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

HolyLemon

New Member
Joined
May 13, 2010
Messages
46
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

edvwvw

Active Member
Joined
Jan 30, 2007
Messages
278
Try this in sheet 2 B1 and drag down

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

edvwvw
 

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
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"))
 

edvwvw

Active Member
Joined
Jan 30, 2007
Messages
278

ADVERTISEMENT

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

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
You need to place what cells you require into my formula. It should work - I have tested it
 

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
Yes, I am sure both our formula's will now work as we had originally thought.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,444
Members
430,548
Latest member
hh_dh2001

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top