Search code examples
excelexcel-formulaexcel-match

MATCH function does not work with words that have wildcards (*)

I am trying to match HA24BB-3-1LL with HA24B*-3-1** in Excel. Another example is matching HA24FB-3-1LL with HA24F*-3-1**.

However, when I performed the regular match function, these could not be matched. 

   col A             col B
1  HA24BB-3-1LL      HA24F*-3-1**
2  HA24FB-3-1LL      HA24B*-3-1**

What I tried: 

=MATCH(A1,B:B,0)

It should return 2 but it returns #N/A.

May I know why?

I thought Excel match function works with wildcard. Is there a way to enable it?

Solution

  • You can match with wildcards, but the wildcards have to be in your lookup value (first position in the formula). If they are in the lookup array (second position in the formula) they are not wildcards, just literal *s in the cell values.

    So you can find matches to strings like HA24B*-3-1** in your first column by using the formula: =MATCH(B1,A:A,0), but not the other way around, as your formula is set up.

    Also, if you are looking for things that match HA24B[one character]-3-1[two characters] your search string should instead be HA24B?-3-1??. The * will match a string of any length, so it is redundant to put two of them at the end of your search string, and using them will also find you matches to strings like HA24Babcdedfghijklmnopqrstuvwxyz-3-1abcdefghijklmnopqrstuvwxyz. Which may be what you want, and if it is leave it as is (minus the second * at the end). The ? matches a single character, which I am assuming is what you are looking for since you used ** in your question.