Use INDEX MATCH and SEARCH for Partial Transaction Matches in Excel
Question details
Find a category or subcategory when a transaction description contains, rather than exactly equals, a lookup term.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is categorizing transactions with vendor names or transfer descriptions that include prefixes, suffixes, store numbers, or other extra text.
- Observed behavior
- An exact XLOOKUP does not return a match when the lookup text is only part of the transaction description.
Create a lookup table with a Transaction column containing the text to find and Category and SubCategory columns containing the values to return. Place more specific lookup terms above broader terms, because the formula returns the first matching row.
Use INDEX, MATCH, and SEARCH to return the first partial transaction match
Use this method when the transaction description includes a lookup phrase plus additional characters, such as a store number or payment reference.
SEARCH checks whether each lookup term appears anywhere within the current transaction description. MATCH identifies the first TRUE result, and INDEX returns the category from that matching row.
In the Lookup_Table, add columns named Transaction, Category, and SubCategory. Sort the Transaction terms from most specific to most general, for example, place "Amazon Marketplace" above "Amazon".
In the Category cell for the transaction row, enter =IFNA(INDEX(Lookup_Table[Category],MATCH(TRUE,ISNUMBER(SEARCH(Lookup_Table[Transaction],[@Transaction])),0)),"Add to lookup"). This searches each Lookup_Table[Transaction] value within the current row's [@Transaction] description.
In the SubCategory cell, enter =IFNA(INDEX(Lookup_Table[SubCategory],MATCH(TRUE,ISNUMBER(SEARCH(Lookup_Table[Transaction],[@Transaction])),0)),"Add to lookup").
Press Enter to confirm the formula. In older spreadsheet versions that do not evaluate the array automatically, confirm it with Ctrl+Shift+Enter, then use the fill handle to copy the formula down.
Use XMATCH instead of MATCH where it is available
Use this alternative if your Excel version supports XMATCH and you prefer its newer lookup function.
Categorize Partial Transaction Matches with WPS Spreadsheet
WPS Spreadsheet can use INDEX, MATCH, SEARCH, and IFNA formulas to identify a lookup phrase inside a longer transaction description and return the appropriate category or subcategory.
- 1. Open the transaction workbook: Open your .xlsx or .xls file in WPS Spreadsheet and select the worksheet containing transactions and the lookup table.
- 2. Add lookup columns: Create Transaction, Category, and SubCategory columns in the lookup range, then place the most specific search phrases at the top.
- 3. Insert the category formula: Select the Category result cell and enter the INDEX, MATCH, SEARCH, and IFNA formula using your lookup-table columns.
- 4. Add the subcategory formula: Select the SubCategory result cell, use the same formula pattern with the SubCategory return column, and press Enter.
- 5. Fill results down: Drag the fill handle down to apply both formulas to the remaining transaction rows.
Frequently Asked Questions
Why does XLOOKUP fail to categorize my transaction descriptions?
A standard XLOOKUP looks for an exact value unless you use wildcard logic. When the transaction contains extra text, use SEARCH inside INDEX and MATCH to test whether a lookup term appears within the full description.
How do I return a subcategory from the same partial-text match?
Use the same MATCH(TRUE,ISNUMBER(SEARCH(...)),0) portion and change the INDEX return range to Lookup_Table[SubCategory]. This keeps the category and subcategory tied to the same lookup row.
What happens if two lookup terms match one transaction?
INDEX and MATCH return the first matching lookup row. Order the lookup table with longer or more specific terms first, such as placing "Uber Eats" above "Uber".
How can I show a message when no transaction term is found?
Wrap the formula in IFNA, such as =IFNA(your_formula,"Add to lookup"). The message identifies transactions that need a new lookup term.
Does SEARCH distinguish between uppercase and lowercase text?
No. SEARCH is not case-sensitive, so it can match vendor names regardless of capitalization. Use FIND instead if a case-sensitive match is required.
