Solved How to look up text in an array contained in a pivot table?

August 11, 2014 at 08:27:10
Specs: Windows 7

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!?


See More: How to look up text in an array contained in a pivot table?

Report •

August 11, 2014 at 09:39:10
✔ Best Answer
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 for Fr should 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.

Report •
Related Solutions

Ask Question