Hi, I have a sheet containing approx 1500 rows of data, I want a Yes/No to be displayed in Column B when the text in Column A can be found in another sheet of data containing approx 3000 rows of data (which is currently in the form of a PIVOT table)

Please see below for the current formula I have (im randomnly using row 1301 to try and get to grips with this formula)

Currenty I have =IF(ISERROR(SEARCH(B1301,Pivot!A71,1)),"NO","YES")You will notice that Pivot!A71 is contained in the forumla, this is where part of the text contained in 1301 resides, and excel shows up "YES" to signify that the text IS contained in that cell. However, If I then try and expand the range of the search, to Pivot!A5:A3726 - excel just returns "NO".

Can anybody help please!?

Gary

I don't do a lot with Pivot Tables, so maybe this won't work... I assume you are searching for a partial string in the Pivot Table, correct?

e.g. The Pivot table might contain

Fred, for which a search forFrshould return a Yes, correct?Did you try VLOOKUP with a wildcard?

=IF(ISNA(VLOOKUP(B1301 & "*", Pivot!A5:A3726, 1, 0)),"NO","YES")

If you are searching for an exact, full-string match, just eliminate the

& "*".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History