The Idea: A Lookup Table Instead of Nested IFs
Once a bank statement is in a spreadsheet, a common next step is to label each row with a category such as Dining or Groceries. Rather than writing a long chain of IF tests, you can keep a small two-column table on the side (merchant text in one column, category in the next) and have a formula look each transaction up in it. When a merchant is new, you add one row to the table and every matching transaction updates.
XLOOKUP is a good fit because Microsoft documents it as returning a value from a return array based on a match in a lookup array, and the return array can sit on either side of the lookup array. That means the category column does not have to be to the right of the merchant column.
XLOOKUP Syntax and Defaults
Microsoft documents the syntax as XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first three arguments are required.
Two defaults matter here. First, XLOOKUP produces an exact match by default (match_mode 0). Second, if no valid match is found and you have not supplied if_not_found, the result is #N/A. When you do supply if_not_found, Microsoft says XLOOKUP returns that text instead of an error.
Wildcard matching is available only when match_mode is set to 2, where the characters *, ? and ~ have special meaning. It is not active by default, so a merchant such as "Sample Cafe" must match a table entry exactly unless you change the mode.
Worked Example with Synthetic Data
Suppose the merchant text for each transaction is in column B (rows 2 to 5), and the lookup table is in F2:G4, with merchants in F and categories in G:
Lookup table (F2:G4)
Sample Cafe Dining
Sample Market Groceries
Sample Payroll Income
Transactions (B2:B5)
Sample Cafe
Sample Market
Sample Gym
Sample PayrollIn C2, next to the first transaction, enter:
=XLOOKUP(B2, $F$2:$F$4, $G$2:$G$4, "Uncategorized")Copy it down. Row 2 returns Dining, row 3 returns Groceries, row 5 returns Income, and row 4 (Sample Gym, which is not in the table) returns Uncategorized because that is the if_not_found text. The dollar signs lock the table range so it does not shift as the formula is copied.
Filtering the category column for Uncategorized then gives you a quick to-do list of merchants to add to the table. That is the practical benefit of if_not_found: gaps are visible text you can filter on, rather than a column of #N/A errors that can make later totals fail.
Statement descriptions often include extra text such as dates, reference numbers or location codes. Exact matching only works if the text in your merchant column matches the table entry exactly, so you may need a cleaned-up merchant column first.
Using the Categories Afterwards
With a category on every row you can total by category using SUMIFS or a PivotTable. For the synthetic rows above, if Sample Cafe is -4.50 and Sample Market is -62.30, a SUMIFS on the Dining category returns -4.50 and on Groceries returns -62.30.
Keep the lookup table on its own area of the sheet, and consider keeping a note of any rule you apply, for example how you treat refunds or transfers. XLOOKUP only returns what is in your table, so the quality of the categories depends entirely on how carefully the table is maintained.
Limits to Keep in Mind
Microsoft's page describes the match modes and the default behaviour, but it does not describe how to clean statement text, and this article does not claim a single lookup table will fit every statement layout. Expect to adjust the table as new merchants appear.
If your transactions are still locked in a PDF, PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
If no valid match is found and you have not supplied the optional if_not_found argument, Microsoft documents that XLOOKUP returns #N/A. If you supply text such as "Uncategorized", it returns that text instead.
Microsoft documents that XLOOKUP produces an exact match by default and that wildcards (*, ? and ~) apply only when match_mode is set to 2. The page does not state anything about case sensitivity, so test your own data.
No. Microsoft states the return array can be on either side of the lookup array.
Primary Evidence & Official References
- XLOOKUP function — support.microsoft.com (Verified 2026-10-29). Official source documentation →