Forum Discussion
Looking for alternate ways for XLOOKUP
While using Excel to maintain a cash flow spreadsheet, I use XLOOKUP to retrieve category and subcategory assignments for both Credit and Debit transactions. This works for 80% of the entries, however there are the odd ones, like a certain vendor who appends a store number to the vendor record and certain account transfer transactions that have either a prefix or suffix or both.
I'm looking for ideas on how to best handle these exceptions.
This is example spreadsheet.
Thanks!
Doug
Use
=INDEX(Lookup_Table[Category], MATCH(TRUE, ISNUMBER(SEARCH(Lookup_Table[Transaction], [@Transaction])), 0))
and
=INDEX(Lookup_Table[SubCategory], MATCH(TRUE, ISNUMBER(SEARCH(Lookup_Table[Transaction], [@Transaction])), 0))
HansVogelaar , thanks, that seems to work as I wanted!!
=IFNA(INDEX(Transaction[Category],XMATCH(TRUE,ISNUMBER(SEARCH(Transaction[Transaction],B2)))),"Add to lookup")
An alternative could be this formula in cell E2. The corresponding formula is in cell F2 and both formulas are filled down.
- Thanks, Oliver, this works as I wanted!
