Search code examples
excelmatchwildcardsumifs

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

Solution

  • 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.