Excel 365 Lookup Across Multiple Columns and Return a Row Value
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.

- 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.
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.
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.
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.
Select the result cell and enter: =LET(row,TOCOL(IF(totalling,rowNo,NA()),3),tot,TOCOL(totalling,3),XLOOKUP(GL,tot,row))
Press Enter. Excel searches the flattened totalling values, skips blank cells, and returns the corresponding value from rowNo for the first match.
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.

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. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook that contains the lookup table.
- 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. Enter the dynamic lookup formula: Click the output cell and enter the TOCOL and XLOOKUP formula using your lookup cell and ranges.
- 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.

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.




