Fixing Excel INDEX MATCH #VALUE! Error When Array Formula Spans Multiple Columns

You build an INDEX MATCH that works perfectly on one column, then extend it to pull data from a range of columns and Excel instantly throws #VALUE!. Nothing in the formula looks wrong — the ranges seem right, the syntax is correct — but the error persists. The problem is almost always a dimension mismatch between the array INDEX is trying to return and the array MATCH is producing to navigate it.

What You'll Learn

• Why multi-column array ranges cause INDEX MATCH to return #VALUE!
• How to diagnose which part of the formula is producing a mismatched array
• Four concrete fixes, from quick alignment tweaks to XLOOKUP replacements
• The specific CSE (Ctrl+Shift+Enter) rules that apply to multi-output formulas
• Common pitfalls that make the error come back even after you think it's fixed

Prerequisites

You should be comfortable writing basic INDEX MATCH formulas and know the difference between a regular formula and a CSE array formula (entered with Ctrl+Shift+Enter). The fixes below apply to Excel 2016, 2019, and Microsoft 365. Where behavior differs between legacy Excel and the dynamic-array engine in 365, that's called out explicitly.

What Is Actually Causing the #VALUE! Error

Excel raises #VALUE! when a formula receives a value of the wrong type or shape. With INDEX MATCH, the trigger is almost always one of two things: the row_num or col_num argument inside INDEX evaluates to an array of values instead of a single number, or the return array inside INDEX has a shape that Excel cannot reconcile with the cell (or range of cells) receiving the result.

MATCH always returns a single number when it's doing a straightforward lookup. The trouble starts when you wrap MATCH in an IF or nest it inside a multi-column INDEX call, because those constructs can produce arrays of numbers instead of one scalar. If INDEX gets an array in the position where it expects a single row or column number, and you haven't told Excel to handle that array correctly, you get #VALUE!.

How INDEX MATCH Uses Arrays Internally

It helps to think of INDEX as a two-axis navigator. INDEX(array, row_num, col_num) moves to a specific intersection and returns whatever value lives there. When array is a single column, col_num defaults to 1 and you never have to think about it. Extend array to multiple columns and Excel now needs a valid col_num to resolve the second axis.

If MATCH is responsible for generating col_num — for example, MATCH(header, header_row, 0) — and it returns an array instead of a single value, INDEX cannot collapse that to a single cell unless you've entered the formula as a CSE array or the dynamic-array engine can resolve the spill. Get that wrong and #VALUE! is the result.

The same logic applies to the row axis. An IF-based conditional MATCH like MATCH(1, (A2:A100=criteria1)*(B2:B100=criteria2), 0) builds a boolean array internally. That works when entered correctly, but pair it with a multi-column return range and you've stacked two array-producing constructs without giving Excel a clear resolution path.

The Most Common Trigger: Mismatched Array Dimensions

Consider this formula, entered in a single cell:

=INDEX(B2:D100, MATCH(G2, A2:A100, 0), MATCH(H1, B1:D1, 0))

This looks reasonable. The first MATCH finds the row; the second MATCH finds the column. In most cases this works fine — but it breaks the moment either MATCH argument receives a range that returns multiple values. For example, if H1 is accidentally a multi-cell reference, or if you've copy-pasted a CSE array formula from elsewhere and the brace syntax is now wrapping the wrong part of the expression, MATCH returns an array and INDEX chokes.

A subtler version: you want to return an entire matched row across all three columns of B:D, so you leave col_num blank or set it to 0 with the intent of getting all columns. In legacy Excel (pre-365), returning multiple columns from a single-cell entry is not supported without CSE and a multi-cell selection. Enter it in one cell without CSE and you get #VALUE!.

Fix 1: Align Your MATCH Range to a Single Row or Column

This is the fastest fix for the most common case. Make sure the lookup array you pass to MATCH is strictly one-dimensional — either a single column range like A2:A100 or a single row range like B1:D1. Passing a two-dimensional range like B1:D10 to MATCH produces #VALUE! immediately because MATCH does not operate on 2D ranges.

Check your MATCH arguments one at a time by selecting the MATCH portion in the formula bar and pressing F9 to evaluate it inline. If you see a nested array like {1,2,3;4,5,6} (semicolons separate rows, commas separate columns), your lookup range is 2D and needs to be reduced to a single row or column.

-- Wrong: 2D lookup range
=INDEX(B2:D100, MATCH(G2, A2:D2, 0), 2)

-- Correct: single column for row lookup
=INDEX(B2:D100, MATCH(G2, A2:A100, 0), 2)

Once MATCH is operating on a 1D range, it returns a scalar and INDEX can resolve the intersection without error.

Fix 2: Enter Multi-Output Formulas as a Proper CSE Array

If you want INDEX MATCH to return values across multiple columns simultaneously — one result per column — you must select the output range first, then enter the formula with Ctrl+Shift+Enter (CSE). This tells legacy Excel that the formula is expected to produce an array and that each cell in the selection should receive one element.

1. Select the range of cells that should receive the results, for example I2:K2 for three output columns.
2. Type the formula: =INDEX(B2:D100, MATCH(G2, A2:A100, 0), 0). The col_num of 0 tells INDEX to return all columns.
3. Press Ctrl+Shift+Enter instead of just Enter.
4. Excel wraps the formula in curly braces: {=INDEX(B2:D100, MATCH(G2, A2:A100, 0), 0)}.

If you enter this in a single cell without CSE in legacy Excel, you'll get the first column's value or #VALUE! depending on version. The CSE entry is what signals Excel to distribute the array result across the selected cells.

Do not type the curly braces yourself. Typing them has no effect; only the keyboard shortcut creates a real array formula. If you see the braces but the error persists, delete the formula, re-select the output range, retype the formula, and press Ctrl+Shift+Enter again.

Fix 3: Use IF to Build a Conditional Array That Matches INDEX Size

A common advanced pattern is using INDEX MATCH with a multi-condition lookup, like this:

{=INDEX(C2:C100, MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0))}

This works cleanly because the boolean multiplication produces a 1D array and MATCH returns a single row number. The error appears when you expand the return range to multiple columns without adjusting the formula structure:

-- Broken: return range spans columns but col_num is unresolved
{=INDEX(C2:E100, MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0))}

INDEX gets a multi-column array and no col_num, so it doesn't know which column to return in a single-cell context. Fix it by either specifying a hard-coded column number or adding a second MATCH for the column axis:

-- Fixed: explicit col_num via a second MATCH
{=INDEX(C2:E100, MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0), MATCH(I1, C1:E1, 0))}

Entered as CSE in a single cell, this returns the value at the intersection of the matched row and the matched column. If you need all three columns, select three cells and use col_num = 0 as described in Fix 2.

This pattern shares some DNA with the multi-condition approaches covered in the article on INDEX MATCH returning #N/A when lookup ranges have merged cells — worth reading if you're also dealing with formatting quirks in your source data.

Fix 4: Replace the Whole Pattern With XLOOKUP (Excel 365 / 2021+)

If you're on Microsoft 365 or Excel 2021, XLOOKUP handles multi-column returns natively without CSE, without dimension management, and without the #VALUE! trap. The equivalent of a multi-column INDEX MATCH is straightforward:

=XLOOKUP(G2, A2:A100, B2:D100)

This returns the entire matching row from columns B through D, spilling into adjacent cells automatically. You don't select the output range in advance; the dynamic-array engine handles the spill. If the cell to the right is occupied, you get a #SPILL! error instead, which is much easier to debug than #VALUE!.

For a two-axis lookup (matching both row and column), nest two XLOOKUPs:

=XLOOKUP(G2, A2:A100, XLOOKUP(H1, B1:D1, B2:D100))

The inner XLOOKUP selects the right column range; the outer XLOOKUP finds the right row within that range. No arrays to manage, no CSE required, and dimension mismatches become impossible because XLOOKUP is built to understand 2D return scenarios.

If you're already leaning on SUMPRODUCT for multi-condition calculations and running into similar dimension issues, the debugging logic covered in fixing SUMPRODUCT #VALUE! errors across multiple sheets applies the same principle: isolate which sub-expression is producing the wrong shape, then fix the shape or enter the formula in a way that accommodates arrays.

Common Pitfalls to Avoid

Editing a CSE formula without re-entering as CSE

When you edit a CSE array formula and press Enter instead of Ctrl+Shift+Enter, Excel silently drops the array context. The formula still shows without curly braces and often returns #VALUE! or an incorrect scalar. Always finish editing CSE formulas with the full three-key press.

Using MATCH with a mixed-type lookup value

If the value you're looking up is a number but the lookup array contains numbers stored as text (or vice versa), MATCH returns #N/A, and that #N/A bubbles up through INDEX as #VALUE! in some configurations. Use -- (double unary) or VALUE() to force consistent types before the lookup.

=INDEX(B2:D100, MATCH(VALUE(G2), VALUE(A2:A100), 0), 2)

This pattern also shows up in VLOOKUP returning wrong values with duplicate keys, where type coercion causes the match to land on the wrong row entirely.

Referencing an entire column in a multi-column INDEX

Using A:A instead of A2:A100 might seem convenient, but pairing whole-column references with multi-column INDEX arrays creates enormous virtual arrays that can trigger #VALUE! on older Excel versions and slow down recalculation significantly on any version. Always bound your ranges explicitly.

Nested INDEX MATCH inside another array formula

Nesting a CSE-style INDEX MATCH inside SUMPRODUCT or another array function doubles the complexity. Each level needs its dimensions to line up. If you're doing this and hitting #VALUE!, extract the inner INDEX MATCH to a helper column first, confirm it works, then re-nest it. Debugging layer by layer is far faster than debugging the combined expression.

A related trap appears when using AVERAGEIFS with named ranges — the same kind of silent dimension conflict that the article on AVERAGEIFS returning #DIV/0! with named range criteria walks through in detail.

Copy-pasting a CSE formula into a range that differs in size

If you copy a CSE formula that was entered for a 3-column output into a 5-column range, the original array context doesn't stretch automatically. You'll get stale results or errors in the extra cells. Delete the old formula, select the new full output range, and re-enter the formula from scratch with Ctrl+Shift+Enter.

Wrapping Up

The #VALUE! error in multi-column INDEX MATCH almost always traces back to one thing: the shape of an array produced somewhere inside the formula doesn't match the shape Excel expects for that position. Here are the concrete steps to resolve it:

• Diagnose first: select each MATCH sub-expression in the formula bar and press F9 to see what shape it returns. A 2D result in a MATCH argument is the most common culprit.
• Flatten your MATCH ranges to single rows or single columns — never 2D ranges.
• Use CSE entry (Ctrl+Shift+Enter) whenever your formula is designed to return multiple values, and always re-enter with CSE after editing.
• Add an explicit col_num via a second MATCH when your INDEX return range spans multiple columns but you only need one value per cell.
• Switch to XLOOKUP if you're on Excel 365 or 2021 — it eliminates this entire class of dimension errors and handles multi-column returns without CSE.