mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
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?
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: