behavior of MATCH function

[mikerickson]

I have a sheet where column P has text like "Two Highways ~folk vinyl" (no quote marks).

the formula =MATCH(P2, P:P, 0) returns an #N/A error.

But removing the ~ has an odd effect:

with

Two Highways ~folk vinyl (in P2)
Two Highways folk vinyl (in P3)

=MATCH("Two Highways ~folk vinyl" , P:P, 0) returns 3
=MATCH("Two Highways folk vinyl" , P:P, 0) returns 3.

It appears that Excel doesn't "see" the tilda when looking at the lookup_value argument of Match.
But, it does "see" the tilda in the cell when looking for an MATCH.
So =MATCH("Two Highways ~folk vinyl" , P:P, 0) returns the same as MATCH("Two Highways folk vinyl" , P:P, 0).

(My goal is to identify the first cell of any duplicates in column P, with MATCH(P1, P:P, 0) = ROW(P1). There are other methods.)

How can one cause MATCH(P1, P:P, 0) to return 1, even if the text in P1 has a tilda?

 

[duggie33]

Hi Mikericksn,

I am not positive but I think it is because the tilde typically indicates the next character is a literal when doing a find|replace. I guess you would put two consecutive tildes to find them. This is not the same as match but may work on the same principle. A possible workaround is to find|replace all tides before running the match.

I tried the find function and search function on a string with a tilde at position 11. Search returned 1 and find returned 11...weird.

Hope that helps,

Doug

 

[mikerickson]

Yes, =MATCH(SUBSTITUTE(P1,"~","~~"), P:P, 0) is working.

Thank you.

 

[duggie33]

No problem, it's nice to help a little considering all the times I have been helped directly or by searching posts...

Take care,

Doug