Function Problems

Excel 365 Lookup Across Multiple Columns and Return a Row Value

Steve KSteve K Nov 12, 2026 870 views

Question details

Find a lookup value that can appear in any column of a multi-column Excel range and return the row number or corresponding value from the same row.

Excel 365 Lookup Across Multiple Columns and Return a Row Value
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
The lookup range contains several columns, while the desired result is associated with the row where the first matching value is found.
Observed behavior
A standard single-column lookup does not search every column of the specified range and return the related row result.
Before you start

Make sure your lookup range, named here as totalling, and the row-result range, named here as rowNo, cover the same set of rows. This formula requires Excel 365 dynamic-array functions, including TOCOL and XLOOKUP.

Solution 1Recommended

Flatten the lookup range with TOCOL and use XLOOKUP

Use this method in Excel 365 when a value may occur in any column of a rectangular range and you need the associated row number.

TOCOL converts the multi-column lookup range into one vertical list. The matching row-number array is flattened in the same order, allowing XLOOKUP to return the row associated with the found value.

1
Define the source ranges

Use totalling for the multi-column range to search, rowNo for the row numbers or row identifiers to return, and GL for the cell containing the lookup value. Ensure rowNo has one entry for each row in totalling.

2
Enter the multi-column lookup formula

Select the result cell and enter: =LET(row,TOCOL(IF(totalling,rowNo,NA()),3),tot,TOCOL(totalling,3),XLOOKUP(GL,tot,row))

3
Confirm the returned row result

Press Enter. Excel searches the flattened totalling values, skips blank cells, and returns the corresponding value from rowNo for the first match.

4
Handle values from another column if needed

If you need a value from a specific column rather than a row number, first create a return array that repeats that column's row values across the same number of columns as totalling, then use it in place of rowNo in the formula.

Flatten the lookup range with TOCOL and use XLOOKUP
First-match behavior: XLOOKUP returns the first matching occurrence in the flattened range. If the same lookup value appears more than once, the result corresponds to the first match by row and then by column.
Work with advanced lookup formulas in WPS Spreadsheets

Search Multiple Columns with Dynamic Lookup Formulas in WPS Spreadsheets

WPS Spreadsheets provides a familiar worksheet environment for building multi-column lookup formulas. Use named ranges or direct cell references, flatten the search data with dynamic-array functions, and return the matching row result with XLOOKUP.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook that contains the lookup table.
  2. 2. Set up the lookup and return ranges: Select the multi-column search range and the matching row-result range, then create names from the Formulas tab if you want to use names such as totalling and rowNo.
  3. 3. Enter the dynamic lookup formula: Click the output cell and enter the TOCOL and XLOOKUP formula using your lookup cell and ranges.
  4. 4. Check the result: Press Enter and verify that the returned row value matches the first occurrence of the lookup value across the selected columns.
Build multi-column lookup formulas for row-based resultsUse XLOOKUP and dynamic-array workflows in a familiar spreadsheet interfaceOpen and edit Microsoft Excel .xlsx workbooks with format compatibilityImport existing Excel formulas and continue working without rebuilding the worksheet
QA img-9

Frequently Asked Questions

Can XLOOKUP search more than one column directly?

XLOOKUP normally searches a one-dimensional lookup array. To search a rectangular range, use TOCOL to convert that range into a single column, then use the flattened array as XLOOKUP's lookup_array.

Why does the formula use TOCOL with 3 as the second argument?

In TOCOL, the value 3 tells Excel to ignore both blanks and errors. This prevents empty cells and the temporary NA() values used to align row results from being included in the lookup list.

What does rowNo represent in this formula?

rowNo is a vertical range or array containing the result to return for each source row, such as worksheet row numbers, record IDs, or labels. It must have the same number of rows as totalling.

What happens when the lookup value appears in several cells?

XLOOKUP returns the first match in the flattened TOCOL output. With a normal column-wise flattening order, Excel checks values from the first row across the columns before moving to the next row.

Can I return a value from another column instead of a row number?

Yes. Build a return array that aligns with the flattened search array, repeating the desired row value for every column in the search range, and use that aligned array as XLOOKUP's return_array.