behavior of MATCH function

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906
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:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
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
77
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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