Forum Discussion

CantFigureTheFormula's avatar
CantFigureTheFormula
Copper Contributor
Nov 07, 2019

Weird! "*" doesn't work sometimes?

With the help of this page community, I created the following formula which let me compare individual items in a column against a large list of data elements in another column where more often than not, there were multiple data elements in each cell separated by spaces and commas.

 

=IF(ISNA(MATCH("*"&A1&"*",Calculations!A:A,0)),"Not Found","Matched")

 

It works like a charm most of the time, but when you get to referencing cells with a large amount of data (e.g. ~470+ characters) it simply won't find the item even though it's clearly visible in the cell.  I checked cell formatting, etc., but it looks apples to apples to me.  Is there a known limit or a workaround I could exploit?

 

Feeling pretty amateur that this is stymying me.  Anybody care to help?

excel
Formulas and Functions
office 365

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 08, 2019

    CantFigureTheFormula 

    That's 256 characters limit. You may try instead

    =IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A1,Calculations!A:A)),0),0)), "Not Found","Matched")

    Performance is not as good as for the first formula, it's better to limit the range for Calculations sheet.

Resources