Adding Category value from look-up table

Question

Level 1

4 points

Adding Category value from look-up table

Hi,

I am having difficulties to build a formula in numbers that is checking if a certain expression from the look-up table (Cat_table) is in a cell of the main (Transactions) table and if so, adds the category value next to it (see expected results table). Date and Description cells are imported and I would like to categorize them. In Excel I used to do it with that formula but I could not make it work in Apple Numbers: =INDEX(Cat_table!$B$2:$B$5;MIN(IF(ISNUMBER(SEARCH(Cat_table!$A$2:$A$A5;B2));(ROW(Cat_table!$A$2:$A$5)-ROW(Cat_table!$A$1)))))

Any help is highly appreciated!

MacBook Pro 14″, macOS 14.6

Posted on Aug 25, 2024 3:33 AM

Reply

Answer 1

SGIII

Level 8

36,134 points

Aug 25, 2024 6:49 AM in response to Rolf9

Borrowing shamelessly from Badunit's brilliant ideas in this thread ...

Mac Numbers Categorize based on partial m… - Apple Community

... here is one way to assign categories to longer strings found in, say, descriptions:

In B2:

Mac Numbers Categorize based on partial m… - Apple Community

In A8 of Table 2 (with that last row designated as a Footer Row so the formula can refer to the cells above in the same row):

=TEXTJOIN("|",TRUE,A2:A7)

SG

Reply

Answer 2

Rolf9 Author

Level 1

4 points

Aug 26, 2024 1:30 AM in response to SGIII

Thanks very much for the fast replay - it works on the sample but the real tables are much bigger and I have about 250 terms I am searching for (shop names, restaurant names that are provided with the transaction). These get then consolidated in a second step into categories like food, gas, insurance etc.

=TEXTJOIN("|",TRUE,A2:A250) results in about 3000 characters in a cell. With a subset of the categories, it works but not all search terms concatenated.

Any idea how to overcome that?

Reply

Answer 3

Rolf9 Author

Level 1

4 points

Aug 26, 2024 11:27 PM in response to Rolf9

I did some further investigations on the size and I found the issue! In Excel I used a wildcard (*) at the beginning and at the end of the category term to catch all companies that contain that name (*company_name*) in it.

With the proposed solution in Numbers, all results after an initial '*' in the category term go to "not found". If the '*' is within the term, there is no problem. So I will have to rethink my category terms with '*' and it will work.

Thanks for your support!

Reply