How to categorise numbers based on list of patterns with Excel formula (no VBA)?

Question

I have a list of account numbers (table 1) and a list of categories with some rules (table 2) of how each category should be applied to a specific account number. I would like to go through the list of account numbers and assign a category to it based on the category rule. The expected result is in table 3.

If there is a better solution to how to structure the data, in order to make any calculations easier, this would be a welcome suggestion too. The only restrictions are: the dynamic list of account numbers, the formatting of the patterns and no VBA please, only standard formulas (or Power Query). However I suck at PQ

A simple =SEARCH(D3;LEFT(A3;4)) can match the first two patterns, but not reliably, as intended. The solution needs to be dynamic as well as the list of account numbers can change.

I can also make a TEXTSPLIT(D5;";") to MATCH each pattern. But then what? I just cant seem to figure out the final solution to get the correct category to each account number.

Answer 1

Just throwing in another angle; if you are well versed with regular expressions just rewrite the rules and apply REGEXTEST():

Formula in F3:

=HSTACK(A3:A11,MAP(A3:A11,LAMBDA(s,XLOOKUP(TRUE,REGEXTEST(s,D3:D6),C3:C6,"-"))))

Answer 2

I can propose the next solution which enlists all categories which the account number falls in:

[G3]=LET(as,A3:A11,cats,C3:D13,cs,TAKE(cats,,1),ps,TAKE(cats,,-1)&"",
  HSTACK(as,MAP(as,LAMBDA(x,TEXTJOIN("; ",TRUE,IFNA(REPT(cs,XMATCH(ps,x&"",2)),"")))))
)

But you should change the categories/patterns table as depicted above. The pattern should use Excel wildcards only (? and *).

Surely, it's possible to create the solution with multiple patterns in a row (as per the question) but it will cause the more complex formula.

The sample with the account number which falls in several categories.