To look up a value using two conditions, such as merchant and month, join the two fields into one key and search for that key. In Excel 365 or 2021: =XLOOKUP(F1&"|"&G1,$A$2:$A$500&"|"&$B$2:$B$500,$C$2:$C$500,"Not found"). Here F1 holds the merchant, G1 the month, and the vertical bar is a separator that keeps the pieces apart.
What layout do these formulas assume?
Merchants are in A, month-start dates in B (such as 01-04-2025, formatted as mmm-yy) and the amount for that merchant and month in C. Each merchant-month pair appears once.
| A: Merchant | B: Month | C: Amount |
|---|---|---|
| Airtel | Apr-25 | ₹599 |
| Airtel | May-25 | ₹599 |
| Electricity | Apr-25 | ₹1,840 |
| Electricity | May-25 | ₹2,150 |
Type Electricity in F1 and the date 01-05-2025 in G1. The formula returns ₹2,150. Because the month is stored as a date, joining turns it into its serial number on both sides, so the two keys agree. If one side were text such as "May-25" and the other a date, they would not match.
How do I do it with INDEX and MATCH?
- =INDEX($C$2:$C$500,MATCH(1,($A$2:$A$500=F1)*($B$2:$B$500=G1),0))
Each comparison gives TRUE or FALSE per row. Multiplying them gives 1 only where both are true, and MATCH finds the first 1. In Excel 365 and 2021 this works as a normal formula. In Excel 2019 and earlier it must be confirmed with Ctrl+Shift+Enter, which wraps it in curly braces; without that you usually get #N/A. No separator is needed here, since the tests are kept apart.
What if I have an old Excel and want something simple?
Add a helper column. In D2 type =A2&"|"&B2 and fill it down, so D now holds keys like Airtel|45748. Then:
- =INDEX($C$2:$C$500,MATCH(F1&"|"&G1,$D$2:$D$500,0))
This works in every version and is the easiest to debug, because you can see the keys. VLOOKUP can do the same if the key column is on the left of the answer, but INDEX and MATCH has no such limit.
| Method | Excel version | Strength | Watch out |
|---|---|---|---|
| XLOOKUP, joined key | 365 and 2021 | Short, no helper | Both sides must join the same way |
| INDEX/MATCH, two tests | Any (array entry in older) | No helper column | Needs Ctrl+Shift+Enter before 2021 |
| Helper key | Any | Visible, easy to check | One more column to keep filled |
Why use a separator at all?
Without it, merchant "A1" in month "11" and merchant "A" in month "111" would both join to "A111". It is unlikely with real data but costs nothing to prevent. Choose a character that never appears in your merchant names, like the bar, or a double tilde.
What if there are several rows for the same merchant and month?
Lookups return only the first match, which silently hides the rest. If a merchant can appear many times in a month, you want a total instead of a lookup. Use SUMIFS, which takes as many conditions as you like:
- =SUMIFS($C$2:$C$500,$A$2:$A$500,F1,$B$2:$B$500,G1)
If your dates are exact days and not month-starts, use two date conditions: on or after the first of the month, and before the first of the next month. A quick test for duplicates is =COUNTIFS($A$2:$A$500,F1,$B$2:$B$500,G1); anything above 1 says to use SUMIFS.
Where this stops being tidy
Two-key lookups assume clean merchant names, and statements rarely give you that. If most of your time goes into tidying names, SpendixAI can import the statement, normalise the merchant and show monthly spend by merchant, which you can download as CSV if you still want the data in a sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


