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.

 

Doug_Loten_0-1705334517382.png

This is example spreadsheet.

Thanks!

 

Doug

 

4 Replies

@Doug_Loten 

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

tramsaction.png

@Doug_Loten 

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))

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

Thanks, Oliver, this works as I wanted!