Function Problems

How to Use XLOOKUP to Return a Value Based on Another Column

Partner Editor Oct 10, 2026 872 views

Question details

The user needs a formula to search a specific column (e.g., Column E) for a target text value and return the corresponding value from an adjacent column (e.g., Column D) on the same row.

How to Use XLOOKUP to Return a Value from a Different Column

Product: Spreadsheet
Device & OS: not provided
Scenario: Data retrieval and cross-referencing across different columns in a dataset.
Observed behavior: A dynamic formula is required to automate the lookup process without manually searching for matching rows.

Before you start

Ensure that the columns containing your search data and your return data are properly aligned, and verify that you are using a spreadsheet version that supports the XLOOKUP function.

Solution 1Recommended

Use the XLOOKUP Function (Recommended)

XLOOKUP is the most efficient and straightforward function to search for a value in one column and return a result from another, regardless of whether the return column is to the left or right.

The syntax for XLOOKUP is =XLOOKUP(lookup_value, lookup_array, return_array). It completely replaces older functions like VLOOKUP by offering superior flexibility, such as looking to the left (e.g., searching Column E and returning Column D).

Select the target cell

Click on the empty cell where you want the corresponding value from Column D to be displayed.

Enter the XLOOKUP formula with a cell reference

Type the formula =XLOOKUP(F2, $E$1:$E$5, $D$1:$D$5). In this formula, F2 is the cell containing the criteria you want to search for, $E$1:$E$5 is the range to search in, and $D$1:$D$5 is the range containing the value to return.

Search for a fixed text value directly

If you want to search for a specific text string like "T" instead of a cell reference, alter the formula to include quotation marks: =XLOOKUP("T", $E$1:$E$5, $D$1:$D$5). Press Enter to apply the formula.

Absolute References: Using dollar signs ($) in your ranges creates absolute references. This ensures your search and return ranges remain locked and do not shift if you copy or drag the formula to other cells.

Solution 2

Use an IF Function for Simple Row-by-Row Checks

If you only need to evaluate cells in a single row against a specific condition rather than searching a whole array, a basic IF function works perfectly.

WPS Spreadsheet Solution

Easily Perform Advanced Data Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports modern functions like XLOOKUP, VLOOKUP, and INDEX MATCH. It makes cross-referencing and analyzing large datasets incredibly easy and intuitive without needing complex workarounds.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your data.
2. Insert the XLOOKUP function: Click on the cell for your result. Navigate to the 'Formulas' tab and click 'Insert Function' to search for XLOOKUP, or type =XLOOKUP( directly into the formula bar.
3. Define your arguments: Select your lookup value, highlight the lookup array, highlight the return array, and press Enter to instantly fetch the corresponding data.

Fully supports modern Excel functions, including XLOOKUP.100% compatible with Microsoft Excel file formats (.xlsx).Free, lightweight, and fast for handling large datasets.Built-in formula hints and automatic syntax error checking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return an #N/A error?

The #N/A error means the exact lookup value was not found in the search range. Check for typos or hidden trailing spaces in your data. You can also utilize the optional [if_not_found] argument in XLOOKUP to return a custom message (like "Missing") instead of an error.

Can XLOOKUP look to the left, unlike VLOOKUP?

Yes. A major limitation of VLOOKUP is that the return column must be to the right of the search column. XLOOKUP removes this restriction entirely, allowing you to easily search in Column E and return a value from Column D to its left.

What if I want to return multiple columns with a single XLOOKUP formula?

XLOOKUP natively supports dynamic arrays. If you want to return corresponding values from both Column C and Column D, simply expand your return array in the formula to span both columns (e.g., $C$1:$D$5). The formula will automatically spill the results into adjacent cells.

Excel & Data Problems