CODEWITHSUNDEEP
google-sheets
Diemex
Diemex

Reputation: 841

Try to match string in column and print matching column name

I am trying to build an expense dashboard in google sheets for my personal use.

I have data that I will pull from my receipts like so:

First sheet: "Expenses Feb 18"

+------------+--------+--------+
|    Item    | Amount |  Type  |
+------------+--------+--------+
| Tomatoes   | 2.39   | veggie |
| Joghurt    | 1.45   | dairy  |
| mozzarella | 1.99   | dairy  |
| macadamia  | 4.59   | nuts   |
+------------+--------+--------+

Second table: "Categories"

+------------+----------+-----------+---------------+
|   dairy    |  veggie  |   nuts    | uncategorised |
+------------+----------+-----------+---------------+
| joghurt    | tomatoes | macadamia | a             |
| mozzarella | cucumber | pecan     | b             |
| feta       |          |           | c             |
|            |          |           | d-z           |
|            |          |           | 0-9           |
|            |          |           | -             |
|            |          |           | _             |
+------------+----------+-----------+---------------+

I want to automatically fill out the type column based on the item name.

So far I have a regex that is able to match an item. It will print the matched string. But what I need is the column name (header). And it has to be able to loop through the columns. This only works for a single column.

=REGEXEXTRACT(C11, JOIN("|", INDIRECT("Categories!A1:A"&COUNTA(Categories!A:A))))

Upvotes: 1

Views: 62

Answers (1)

TheMaster
TheMaster

Reputation: 50445

The second table is not a desirable way to enter data. Data should be entered preferably with more rows than columns ( not in a pivoted manner).

=ARRAYFORMULA(CONCATENATE(IF(A16=$C$24:$E$25,C$23:E$23,)))
  • A16 : 🍅
  • C24:E25: Category table
  • C23:E23: Category header.

Upvotes: 2

Related Questions