Function Problems

How to Use XLOOKUP to Return the Value One Row Below a Match in Excel

Steve K Oct 1, 2026 870 views

Question details

The user needs a formula to look up a specific value in one column and retrieve the result from the row directly beneath the matched row in another column.

How to Return the Value One Row Below a Match Using XLOOKUP in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Looking up dynamic data where the desired output value is offset by one row below the matched search key.
Observed behavior: Standard lookup functions return the value from the exact same row as the match, but the user wants to fetch the value from the subsequent row.

Before you start

Verify your dataset ranges before starting. If you use shifted arrays, ensure both the lookup array and the return array have the exact same number of rows to avoid #VALUE! errors.

Solution 1Recommended

Use a Shifted Return Array with XLOOKUP

This is the most straightforward method when working with specific, finite ranges. By manually offsetting the return array by one row, XLOOKUP naturally returns the cell below the match.

XLOOKUP allows the return array to be completely distinct from the lookup array. As long as the dimensions match (e.g., both are 10 rows long), they do not need to start on the same row number.

Select the formula cell

Click on the cell where you want the final offset value to be displayed.

Enter the XLOOKUP function

Type =XLOOKUP( to begin writing your formula.

Define the lookup value and array

Select your search value (e.g., F1) and then select the exact range where this value can be found (e.g., A1:A10).

Define the shifted return array

Select the return range starting exactly one row below the lookup range's starting point (e.g., B2:B11). The final formula will look like =XLOOKUP(F1, A1:A10, B2:B11). Press Enter.

Dimension Matching: Ensure the sizes of A1:A10 and B2:B11 are identical (both span 10 rows). If one range is larger than the other, Excel will return an error.

Solution 2

Use INDEX and XMATCH for Entire Columns

This method is highly recommended if you are referencing entire columns (like A:A) and do not know the exact number of rows in your dataset.

Advanced Spreadsheet Capabilities

Master Lookup Functions with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for modern array formulas, including XLOOKUP, XMATCH, and INDEX. Easily manipulate large datasets, perform dynamic offset lookups, and ensure total compatibility with all your existing workbooks.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open your existing .xlsx file containing the data.
2. Select the target cell: Click the cell where you want to output the lookup result.
3. Apply the shifted lookup formula: Type the formula =XLOOKUP(F1, A1:A3, B2:B4) or the INDEX/XMATCH equivalent based on your data structure.
4. Calculate the result: Press Enter to instantly fetch the data located one row below your matched query.

Fully supports XLOOKUP, INDEX, and XMATCH functions out of the box.100% compatibility with Microsoft Excel (.xlsx, .xls) file formats and formulas.Lightweight, fast execution even when processing vast amounts of data.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use standard VLOOKUP to return the value one row below?

Standard VLOOKUP searches for a match and returns a value on the exact same row. It cannot naturally offset rows downwards. To achieve this, you must use INDEX and MATCH with a +1 row adjustment, or use the XLOOKUP shifted array method.

What happens if I apply the shifted XLOOKUP to an entire column reference?

If you try to shift an entire column (e.g., using A:A as lookup and B2:B1048577 as return), it will likely generate a #REF! or #VALUE! error because the shifted array exceeds the maximum row limit of the spreadsheet. Use the INDEX and XMATCH method for full column references.

How do I return a value two or three rows below the match?

Using the shifted array method, simply shift the return array down by the desired number of rows (e.g., for a two-row shift, use B3:B5 instead of B2:B4). For the INDEX and XMATCH method, change the +1 in the formula to +2 or +3 respectively.

Excel & Data Problems