Index and Match Two-Column Lookup in Excel

To look up a value based on two columns in Excel, you combine the INDEX and MATCH functions into an array formula that matches both criteria simultaneously. This article explains exactly how to build that formula, why it works, and how to adapt it to your data.

Why INDEX and MATCH Instead of VLOOKUP?

VLOOKUP is the most well-known lookup function, but it has a serious limitation: it can only search one column to the left of the return value. If your lookup value is in the second or third column, VLOOKUP fails unless you rearrange your data. INDEX and MATCH, on the other hand, can look left or right, and they handle two-column lookups naturally.

For a two-column lookup, you need to find a row where both criteria are true. VLOOKUP cannot do this without adding a helper column that concatenates the two criteria. INDEX and MATCH can do it directly with an array formula, which is cleaner and less error-prone.

How the INDEX and MATCH Combo Works

The core idea is simple: MATCH finds the row number, and INDEX returns the value in that row. For a two-column lookup, MATCH must find the row where both criteria match. You do this by multiplying two comparison arrays together, which creates a single array of 1s and 0s—1 only when both criteria are true.

The formula looks like this (assuming your data is in columns A, B, and C, with criteria in cells E1 and F1):

=INDEX(C:C, MATCH(1, (A:A=E1)*(B:B=F1), 0))

This is an array formula, so in older Excel versions you must press Ctrl+Shift+Enter instead of just Enter. In Excel 365 and Excel 2021, it works with a normal Enter because of dynamic arrays.

Step-by-Step: Building the Formula

Let’s break down the formula piece by piece.

1. Identify your data ranges: Suppose column A contains Product, column B contains Region, and column C contains Sales. You want to find Sales for a specific Product in a specific Region.
2. Set up criteria cells: Put the product name in cell E1 and the region in cell F1.
3. Create the match condition: The expression (A:A=E1) returns an array of TRUE/FALSE. Multiplying by (B:B=F1) converts TRUE/FALSE to 1/0 and gives 1 only where both are TRUE.
4. Find the row: MATCH(1, (A:A=E1)*(B:B=F1), 0) returns the position of the first 1 in that array.
5. Return the value: INDEX(C:C, ...) returns the value from column C at that row.

If you prefer not to use whole columns (which can slow down large files), use a limited range like A2:A1000.

Worked Example with Real Data

Imagine you have the following table:

Product	Region	Sales
Widget	North	150
Widget	South	200
Gadget	North	175
Gadget	South	225

You want the Sales for “Gadget” in “South”. Put “Gadget” in E1 and “South” in F1. The formula:

=INDEX(C2:C5, MATCH(1, (A2:A5=E1)*(B2:B5=F1), 0))

Here’s what happens:

• (A2:A5=E1) → {FALSE;FALSE;TRUE;TRUE}
• (B2:B5=F1) → {FALSE;TRUE;FALSE;TRUE}
• Multiplying gives {0;0;0;1}
• MATCH finds the 1 in position 4
• INDEX returns C4, which is 225.

The result is 225.

Common Mistakes and How to Avoid Them

Several pitfalls trip up users when building this formula:

• Forgetting to press Ctrl+Shift+Enter in older Excel versions. If you don’t, you get a #VALUE! error.
• Mismatched range sizes: All ranges in the MATCH part must have the same number of rows. If A2:A100 and B2:B50, you get an error.
• Using whole columns with older Excel: This can cause performance issues. Use a specific range that covers your data.
• Text vs. numbers: If your criteria are numbers but stored as text, the match fails. Ensure data types are consistent.
• Exact match vs. approximate: The 0 at the end of MATCH forces an exact match. If you omit it, you get an approximate match, which is rarely what you want.

Alternatives: Helper Columns and XLOOKUP

If you don’t want to use an array formula, you can add a helper column that concatenates the two criteria. For example, in column D you put =A2&"|"&B2 and then use a simple VLOOKUP or INDEX/MATCH on that column. This is easier to understand but adds a column to your data.

If you’re using Excel 365 or Excel 2021, you can use the modern XLOOKUP function with a similar approach:

=XLOOKUP(1, (A:A=E1)*(B:B=F1), C:C)

XLOOKUP handles arrays natively and doesn’t require Ctrl+Shift+Enter. It’s cleaner and faster. However, many users still work with older versions, so INDEX/MATCH remains essential.

Frequently Asked Questions

Do I need to press Ctrl+Shift+Enter?

In Excel 365 and Excel 2021, no—dynamic arrays handle it automatically. In Excel 2019 and earlier, yes, you must press Ctrl+Shift+Enter, otherwise you get a #VALUE! error.

Can I use this formula for more than two criteria?

Yes. Just add more multiplication terms. For three criteria, use (A:A=E1)*(B:B=F1)*(C:C=G1) and adjust the INDEX range accordingly. The same logic applies.

What if there are duplicate rows with the same criteria?

MATCH returns the first match. If you need the last match, use MATCH(2, ...) or use MAX with an array formula. For a sum of all matches, use SUMIFS instead.

Why does my formula return #N/A?

This usually means no row satisfies both criteria. Check for typos, extra spaces, or data type mismatches. Also verify that your ranges are correctly aligned.

Can I use this with a table that has headers?

Yes, just start your ranges at the first data row, not the header row. For example, use A2:A100, not A1:A100.

Is there a way to avoid array formulas entirely?

Yes, use a helper column that concatenates the criteria. Or, if you have Excel 365, use XLOOKUP or FILTER. These are more intuitive for many users.

Related

• Has No Sound in Threads? Fixes for Missing Audio
• Counter Strike 2 Crashing? Fixes That Work
• Hard Disk Defect Management: What It Does & Doesn’t