behavior of MATCH function

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,352
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?
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,096
Messages
6,105,942
Members
447,985
Latest member
gigi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top