Looking for alternate ways for XLOOKUP

Copper Contributor

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.





4 Replies


=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.




=INDEX(Lookup_Table[Category], MATCH(TRUE, ISNUMBER(SEARCH(Lookup_Table[Transaction], [@Transaction])), 0))


=INDEX(Lookup_Table[SubCategory], MATCH(TRUE, ISNUMBER(SEARCH(Lookup_Table[Transaction], [@Transaction])), 0))

@Hans Vogelaar , thanks, that seems to work as I wanted!!

Thanks, Oliver, this works as I wanted!