I have no background in Google Sheets / Excel but am trying to create my own personal budget/expense tracker. I would like to create something I can use long term, and am interested in learning more about spreadsheets / coding to develop a comprehensive, yet practical spreadsheet. I’ve tried to use other free resources online, but am just wanting to develop my own. I’ve fiddled around with named ranges, creating a mapping table, data validation, and formulas to try and get what I want, but I keep encountering error messages. I’ve also tried ChatGPT and Bard to problem solve, but their solutions haven’t worked either. I‘m trying to use ARRAY & INDEX/MATCH to get my Budget sheet to automatically populate responses to Category, Frequency, and Type columns based on data inputs in the Transaction column. I’m attempting to make this a dynamic budget/expense tracker.text
Ex: If I input Gas under the Transaction column, the sheet would auto populate based on my mapping table to Car (Category), Variable (Frequency), and Type (Essential). Any help is greatly appreciated! Thank you! Error msg formulas: =ARRAYFORMULA(IF(C2:C<>"", IFERROR(INDEX(MappingTable RefCategory, MATCH(C2:C, MappingTable RefTrans, 0)), ""), "")) =ARRAYFORMULA(IF(C2:C<>"", IFERROR(INDEX('MappingTable'!RefCategory, MATCH(C2:C, 'MappingTable'!RefTrans, 0)), ""), "")) =ARRAYFORMULA(IF(C2:C<>"", IFERROR(INDEX(RefCategory, MATCH(C2:C, RefTrans, 0)), ""), "")) =ARRAYFORMULA(IF(C2:C<>"", IFERROR(INDEX(MappingTable[Category], MATCH(C2:C, MappingTable[Transaction], 0)), ""), "")) Closest failed formula (no error msg, but outputted housing for every category):
=ARRAYFORMULA(IF(C2:C<>"", IFERROR(INDEX(RefCategory, MATCH(C2:C, RefTrans, 0)), ""), ""))
You may try with the XLOOKUP function: