CODEWITHSUNDEEP
excelmatchwildcardsumifs
Primigenia
Primigenia

Reputation: 125

Treating wildcards at standard characters in an INDEX(MATCH(COUNTIF())) formula

I am using the formula:

=(INDEX($A$2:$A$300, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$300), 0))

This takes a list of values from column A and reproduces it in column B to show each value only once, i.e. if a value is duplicated, it gets skipped.

For the most part it is working exactly as I hoped, however, some of the values in column A contain the wildcard character '?'.

Is it possible to get this formula to treat the '?' as just a standard character? Ideally I would like to include this rule in the formula itself rather than having to do a 'find and replace' with '~?'. Is this at all possible?

Many thanks

Upvotes: 1

Views: 309

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Change your formula to:

=(INDEX($A$2:$A$300,MATCH(0,COUNTIF($B$1:B1,SUBSTITUTE($A$2:$A$300,"?","~?")),0)))

confirmed with ctrl+shift+enter

This replaces the ? in the criteria array with ~? within your formula.

Upvotes: 3

jkpieterse
jkpieterse

Reputation: 2986

You need to "escape" those characters. This is done the same as when searching in Excel, by prepending them with the tilde character: ~

So this searches for the question mark:

=MATCH("~?",A1:A10,0)

Upvotes: 1

Related Questions