How do I set value depending on other columns value, according to some kind of completion key?

Question

I'm using Google Sheets to get an overview on my banking transactions. I would like to put every transaction in a category, for example, grocery, transport,... I solved this by using this short script:

=IFS(REGEXMATCH(A1;$K$2); $L$2;REGEXMATCH(A1;$K$3);$L$3;REGEXMATCH(A1;$K$4);$L$4;true;"other")

In which column A is the one I want to check, with all the transactions, K has the shop names I'm checking on, for example "shopname1", and L is the category I would like to put transactions for this shop in. Now, this is working just fine, but this list of shops and categories is now just 3 rows long, as I'm testing it out, but as I will be using it, it will be quite long, which means that my IFS statement will also be very long. This isn't very modular eighter, for changes in the future, so I would like to make this better. I would like to know a way to for example let it check on a couple of lists, one for every category.

I hope this makes sence, and someone has an idea!

Answer 1

You can try this (in B2):

=ARRAYFORMULA(
  IF(
    A:A = "";;
      VLOOKUP(
        ROW(A:A);
        {
          FILTER(ROW(A:A); A:A <> "")\
          REGEXREPLACE(
            TRIM(
              TRANSPOSE(QUERY(
                IF(
                  NOT(REGEXMATCH(
                    TRANSPOSE(FILTER(A:A; A:A <> ""));
                    "(?i)" & FILTER(K2:K; K2:K <> "")
                  ));;
                    FILTER(L2:L; K2:K <> "") & ", "
                );;
                COUNTA(K2:K)
              )) & "other"
            );
            ", other$";
          )
        };
        2;
      )
  )
)

It will set other if there were no matches, otherwise it will set all matching categories separated by , .