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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,489
Messages
5,529,168
Members
409,853
Latest member
Amy Gassett
Top