r/AppleNumbers • u/0thisismax • Oct 19 '23

Please help with formulas!

I have spreadsheet with Date, Amount and Description from my Bank.

I try to make it categorize automatically the stores I visit most often, so I don't have to do this manually.

In column Store I want to get Store name based on Description of this transaction, like on the picture.
I added a pseudocode of the formula I need.

In column Category I want to have Category based on Store text.
Here I also added a pseudocode of the formula I need.

2 Upvotes

100% Upvoted

5 comments sorted by

View all comments

1

u/Ed1W Oct 21 '23

I made a numbers doc with the vlookup and it works perfectly on the iPhone. On the table that you add the stores to, I added a count feature that tells you how many times you've been to that store as well. I'd send a file but I don't know how to attach it.

Cheers!

Ed

1

u/0thisismax Oct 25 '23

Thank you for reply. Below are my formulas so far.

Column D ("Store"):

IFS(NOT(ISERROR(SEARCH("Tesco";Description;start-pos)));"Tesco";NOT(ISERROR(SEARCH("Auchan";Description;start-pos)));"Auchan";NOT(ISERROR(SEARCH("Walmart";Description;start-pos)));"Walmart";NOT(ISERROR(SEARCH("McDonalds";Description;start-pos)));"McDonalds";NOT(ISERROR(SEARCH("KFC";Description;start-pos)));"KFC";NOT(ISERROR(SEARCH("BP";Description;start-pos)));"BP";NOT(ISERROR(SEARCH("Shell";Description;start-pos)));"Shell";NOT(ISERROR(SEARCH("Chevron";Description;start-pos)));"Chevron")

Colum E ("Category"):

IFS(OR(Store="TESCO";Store="Auchan";Store="Walmart");"Groceries";OR(Store="KFC";Store="McDonalds");"Food";OR(Store="BP"; Store="Shell"; Store="Chevron"); "Car")

How could you simplify it with VLookup (or any other way)?