What Does This INDEX MATCH Array Formula Do? Plain-English Explanation

Classic two-way INDEX/MATCH

The everyday 'left-lookup' that VLOOKUP can't do. INDEX is outermost; MATCH supplies its row number. This is the shape to learn first.

Paste:
=INDEX(B:B,MATCH(D1,A:A,0))

Breakdown:
INDEX() — Returns the value at a given position in a range
  range: cell range B:B
  row number: MATCH() — Returns the position of a value within a range
    lookup value: cell reference D1
    lookup range: cell range A:A
    match type: number 0

The array trick: multiplying two conditions

This is the formula that makes people give up. The breakdown shows the secret: MATCH looks for 1, and 1 only appears where BOTH comparisons are TRUE (TRUE×TRUE = 1). Seeing the two comparisons multiplied is the 'aha'.

Paste:
=INDEX(B:B,MATCH(1,(A:A=E2)*(C:C>0),0))

Breakdown:
INDEX() — Returns the value at a given position in a range
  range: cell range B:B
  row number: MATCH() — Returns the position of a value within a range
    lookup value: number 1
    lookup range: multiplication (*)
      left:  equality test (=)
        left:  cell range A:A
        right: cell reference E2
      right: greater-than test (>)
        left:  cell range C:C
        right: number 0
    match type: number 0

SUMPRODUCT, the friendlier array cousin

Beginners often meet SUMPRODUCT before array INDEX/MATCH. It's not on the description list, so it shows as a generic function — but the conditional math inside is broken out so you can read it.

Paste:
=SUMPRODUCT((A:A="East")*B:B)

Breakdown:
SUMPRODUCT() — Excel function
  arg 1: multiplication (*)
    left:  equality test (=)
      left:  cell range A:A
      right: text "East"
    right: cell range B:B

INDEX/MATCH wrapped in IFERROR

Tutorials often add IFERROR so a missing match shows blank instead of #N/A. The tool puts IFERROR at the top and nests the whole INDEX/MATCH as its first argument.

Paste:
=IFERROR(INDEX(B:B,MATCH(D1,A:A,0)),"")

Breakdown:
IFERROR() — Returns a value if a formula produces an error; otherwise returns the formula result
  expression: INDEX() — Returns the value at a given position in a range
    range: cell range B:B
    row number: MATCH() — Returns the position of a value within a range
      lookup value: cell reference D1
      lookup range: cell range A:A
      match type: number 0
  fallback if error: text ""

Why a brace array constant doesn't expand

If a tutorial uses an inline array like {1,2,3}, the tool can't expand it — the { is unrecognised. Everything up to it parses; the brace part shows raw. You can usually replace it with a cell range to learn the rest.

Paste:
=SUM({1,2,3})

Breakdown:
SUM() — Adds all numeric values in a range
  arg 1: {1,2,3})

(The {1,2,3} array isn't parsed. To study the SUM structure,
put the numbers in cells and paste =SUM(A1:A3) instead.)

The Explainer is gated to Pro and above. On Free it returns Formula Explainer requires Pro tier. with no partial output. There is no smaller free preview — the whole tool is behind the gate. Upgrade to Pro (or higher) to use it.

Legacy array formulas display in Excel wrapped in { } (Ctrl+Shift+Enter / CSE). Those outer braces are Excel's display, not part of the formula — and the parser treats { as unknown, so leaving them in dumps the formula as a raw line. Paste only the inner formula text (without the surrounding { }) and it parses normally.

An inline array constant like {1,2,3} or {"a";"b"} starts with {, an unknown character, so it and the rest of the argument appear as a raw line rather than as parsed values. The surrounding function is still named; only the literal array is left unexpanded.

Any function that is not one of the 65 named ones — a valid-but-unlisted Excel function like SUMPRODUCT, OFFSET, or INDIRECT, a Google Sheets-only function like QUERY or ARRAYFORMULA, or a custom workbook UDF like MyMacro — renders as NAME() — Excel function (uppercased) with generic arg N labels. It is never mislabelled User-defined function and never throws; you still see the full nesting structure and every argument, just without a prose description of that one function.

The Explainer is static analysis: it never evaluates the formula and never reads cell values, so it cannot tell you what =VLOOKUP(...) returns for your data — only what each part does and how the parts nest. To see a value, run the formula in Excel; to understand it, use this. For tracing which cells feed a result with real values, use the dependency map.

The tool explains that MATCH returns a position and INDEX uses it — but it can't tell you that the position is, say, row 17, because it never reads your data. It is structure, not computation. Type the formula into Excel to see the actual matched row.

The breakdown comes from a deterministic recursive-descent parser, not a language model, so an identical formula always produces an identical tree. There is no temperature, no sampling, and no chance of an AI confidently inventing a function or mis-describing an argument — the descriptions are fixed text keyed to each function name.

Excel table syntax is not fully tokenised. Table1[Amount] keeps Table1 as a plain cell reference but drops the [Amount] column part. A reference that starts with a bracket, like [@Price], starts with an unrecognised character, so it and the rest of the formula dump as one raw line. Replace structured references with the equivalent A1 ranges before pasting if you need them in the tree.

If the formula box is empty or only whitespace, the tool returns No formula provided. rather than an empty tree. Paste the real formula text. A leading = is optional — it is added automatically if you leave it off.