Spreadsheet Practice

How to return the cell below a matching value in Excel

Formula (Excel and Google Sheets)

=INDEX(A2:A11,MATCH(D1,A2:A11,0)+1)

Find the label with MATCH and add one row: =INDEX(A2:A11,MATCH(D1,A2:A11,0)+1).

Result on the example
1250 in D2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

MATCH finds "Total" at position 5 of A2:A11. Adding 1 points at the next cell, and INDEX returns position 6, the 1,250 below the label. Any offset works the same way: +2 for two rows down, −1 for the row above.

=INDEX(A2:A11,MATCH(D1,A2:A11,0)+1)

Result1250

  1. A2:A1110 rows × 1 col: "Invoice number", "INV-2041", "Customer", "Contoso Ltd", …

    A range: 10 cells in one column.

  2. D1"Total"

    The value in D1.

  3. MATCH(D1,A2:A11,0)5

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueD1"Total"
    • lookup_arrayA2:A1110 rows × 1 col: "Invoice number", "INV-2041", "Customer", "Contoso Ltd", …
    • match_type0
  4. MATCH(D1,A2:A11,0)+16

    The + operator adds.

  5. INDEX(A2:A11,MATCH(D1,A2:A11,0)+1)1250

    INDEX: Value at a given row and column of a range.

    • arrayA2:A1110 rows × 1 col: "Invoice number", "INV-2041", "Customer", "Contoso Ltd", …
    • row_numMATCH(D1,A2:A11,0)+16

In Google Sheets

The same formula works in Google Sheets: =INDEX(A2:A11,MATCH(D1,A2:A11,0)+1). We ran it in Google Sheets mode on the same data and it returns 1250.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(D1,A2:A10,A3:A11)1250Search one range and return the same range shifted down a row: the cell beside each label in the return range is the one below it on the sheet.

Common mistakes

  • If a value could equal a label (a customer named "Status"), MATCH finds that cell first. Search only label rows if the layout allows.
  • A label in the last row has nothing below it, and INDEX returns #REF!.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Return the cell below a matching value

The export lists each label with its value in the cell below. In D2, return the value under the label in D1.

Questions

How do I return the cell to the right of a match instead?

Use a two-column range and column 2, or XLOOKUP with the next column: =XLOOKUP(D1,A2:A11,B2:B11).

How do I turn the whole export into two columns?

In Excel 2024 or Microsoft 365, =WRAPROWS(A2:A11,2) pairs each label with its value.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.