Forum Discussion

rendarcjb's avatar
rendarcjb
Copper Contributor
Oct 19, 2019

INDEX APPROXIMATE MATCH OR WILDCARD

I'm trying to have my spreadsheets scan credit card statement transactions and automatically categorize them from an index of tax categories.

 

I listed many keywords that should correspond to each tax category, and set the match to approximate, hoping it would be smart enough to find the keyword nested within the lengthy transaction description, but it seems to fail when my index list is large.

 

The screenshot shows what I mean.

 

Beyond fixing this error, it would also be amazing if I could add a SUMIF or SUMIFS function to calculate the total for each tax category after the index and approximate match functions have automatically categorized the expense. If the latter is not possible, I already have a successful SUMIF function that I could run separately on the INDEX MATCH results

 

4 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Try this:
    =IFNA(LOOKUP(2,1/(
    COUNTIF(C58,”*”&F$52:F$261&”*”)),
    G$52:G$261),
    “”)
    • rendarcjb's avatar
      rendarcjb
      Copper Contributor

      Twifoo  thank you for your help! However, I'm getting a parse error

       

      Maybe let's start with just having it automatically categorize and forget about the summation part.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        rendarcjb 

        Are you on Excel or Google Sheets? It looks like the latest and it can't parse the formula.

Resources