Looking for alternate ways for XLOOKUP

[Doug_Loten]

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

[#2]

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

[#3]

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

[#4]

@HansVogelaar , thanks, that seems to work as I wanted!!

[#5]

Thanks, Oliver, this works as I wanted!