Search code examples
excelexcel-formula

How to MATCH text with a wildcard in the target matrix

I want to assign a transaction type to a list of credit card transactions. The type depends on the description of each transaction, which is a text value. I use INDEX MATCH to lookup the type in a description index table which maps from transaction descriptions to types.

Here is the problem: I want to avoid a long list of very similar entries in the description index table. I already have more than ten entries that contain the substring "amazon". Therefore, I thought about using wildcards in the MATCH so that all transactions that contain the substring "amazon" (or similar) are mapped to the same type. Unfortunately, MATCH supports wildcards only in the search value, not in the target matrix. Therefore it seems to be impossible to maintain a description index table that uses wildcard matching patterns.

Can this be done?

Consider the following sample tables. I want to match Description against Pattern to find the Type.

Transaction Table

| Description       | Amount |
|-------------------|--------|
|Amazon Merchant XY | 100.00 |
|Amazon online //>  |  89.99 |
|Amazon.com         |  32.64 |

Lookup Table

| Pattern   | Type         |
|-----------|--------------|
|*amazon*   |Shoping       |
|*itunes*   |Entertainment |
Solution

  • Here is a solution for you:

    =INDEX($F$2:$F$3,MAX(ROW($A$1:$A$2)*IF(ISERROR(FIND($E$2:$E$3,A2)),0,1)))

    This needs to be entered with Ctrl+Shift+Enter rather than just Enter.

    It works with the following setup: enter image description here

    Explanation

    IF(ISERROR(FIND($E$2:$E$3,A2)),0,1)

    Loops through your lookup table and creates an array of ones and zeros depending on whether the lookup value was found in the string A2 in the case of A2 the array would be {1,0}

    Multiplying this string by ROW($A$1:$A$2) effectively gives you the row position that you can use with the INDEX function: {1,0}*{1,2} = {1,0} not that in the case of A4 we would have something like this: {0,1}*{1,2} = {0,2}

    So taking the MAX of this gives you the number 1 or 2 depending on which string was found and you can use that as normal with a INDEX function. Note that using the MAX function means that if you have more than one match it will take the lowest match in the list.