(example attached) This "MATCH" formula correctly returns results in 90% of cases, but not in others.

[RockandGrohl]

Hello all,

I have attached a file which demonstrates this. In my real document, I have about 3,000 rows. The formula in P is:

=COUNTIF(A2:L2,"*"&"tour:"&"*")>0

This works 100% of the time.

The formula in Q is:

=IFERROR(CELL("address",INDEX(A2:L2,MATCH("*"&"tour:"&"*",A2:L2,0))),"")

This works 90% of the time.



What it's doing is, in P, if in the range A2:L2 the phrase (with wildcards) "tour:" appears, it will return TRUE or FALSE
In Q, if in that same range "tour:" appears, it will give me the address of the cell that result appears in.


You can see on the example attached, it returns one cell, but not the other. I have no idea why this happens.


https://ufile.io/ptumm

 

[Fluff]

Possibly because the cell has more than 255 characters.
The Match function is returning #N/A

 

[XOR LX]

Hi,

MATCH with a wildcard will not work on strings of more than 255 characters.

Switch to a SEARCH construction, i.e. for row 3, array formula**:

=IFERROR(CELL("address",INDEX(3:3,MATCH(TRUE,ISNUMBER(SEARCH("tour",A3:L3)),0))),"")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[RockandGrohl]

Possibly because the cell has more than 255 characters.
The Match function is returning #N/A

Ah so that's the limitation. Excellent thanks.

 

[RockandGrohl]

Hi,

MATCH with a wildcard will not work on strings of more than 255 characters.

Switch to a SEARCH construction, i.e. for row 3, array formula**:

=IFERROR(CELL("address",INDEX(3:3,MATCH(TRUE,ISNUMBER(SEARCH("tour",A3:L3)),0))),"")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

I'm reluctant to use an array formula over 3,000 examples of this, but thanks, maybe I will do a left,255 order as I'm sure the "tour" part I need is in the first half.


Nevermind, it works very quickly. Muchos gracias senor!