Find a column using VLOOKUP that says TRUE instead of FALSE on Google Sheets
This spreadsheet is for my budgets, I found it on reddit and I've been trying to make it my own. So far it's been going well but I need to categorise every transaction from my bank & obviously there are things I do periodically, like groceries, my phone bill, etc. so I wanted to add the categories for those transactions automatically and the rest I would add manually.
I added a sheet called REGEX, so I use REGEXMATCH to determine if a transactions goes under Groceries, Bills, Entertainment, etc. using regular expression.
So my idea is: in E of Bank #1 and Bank #2 expenses there is a formula that goes to REGEX, finds the range for Bank #1/#2 Expenses, searches for the description, finds whatever value says TRUE in the row of that description and gives it that category. If it doesn't find a value that's TRUE, then you leave it blank. A couple of examples:
- Transaction is: Purchase made at the pharmacy w/ Bank #1 --> Column E of Bank #1 expenses would return "Medical".
- Transaction is: Purchase made on amazon w/ Bank #2. I don't have a category for this on the REGEX tab because amazon could be anything --> Column E of Bank #2 expenses would return "" (BLANK).
URL, if you want to play with it, make a copy: Stack Overflow Budget
DISCLAIMERS:
- Most things on the REGEX tab are in Spanish, I live in Spain so my banks are Spanish, so the REGEX is in Spanish.
- This is an UNFINISHED sheet.
I've tried a couple of things: On the Bank #1 expenses sheet:
=VLOOKUP($C8,REGEX!K3:AC,COLUMN(Idon'tknowwheretogofromhere),0)
I feel like this could be done better with INDEX MATCH & I tried that, but honestly I don't have much experience using INDEX MATCH compared to VLOOKUP.
You may try this formula in Bank #2 Expenses column E
=byrow(C8:C,lambda(z,if(z="",,ifna(+filter(REGEX!AD2:AV2,sortn(filter(REGEX!AD:AV,REGEX!AD:AD=z),1)=TRUE)))))
