behavior of MATCH function

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,208
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 create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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
 

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,150
Messages
5,835,685
Members
430,376
Latest member
eeehhhyyy

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
Top