Spreadsheets

Look up a value on two conditions in Excel: merchant and month

Find the amount for one merchant in one month in Excel with XLOOKUP on a joined key, INDEX and MATCH with two tests, or a helper column for older versions.

Editorial illustration for Look up a value on two conditions in Excel: merchant and month

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: MerchantB: MonthC: Amount
AirtelApr-25₹599
AirtelMay-25₹599
ElectricityApr-25₹1,840
ElectricityMay-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.

MethodExcel versionStrengthWatch out
XLOOKUP, joined key365 and 2021Short, no helperBoth sides must join the same way
INDEX/MATCH, two testsAny (array entry in older)No helper columnNeeds Ctrl+Shift+Enter before 2021
Helper keyAnyVisible, easy to checkOne 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.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports