Function Problems

How to Look Up a Horizontal Excel Table and Return a Vertical Result

Algirdas Jasaitis Sep 25, 2026 869 views

Question details

The user needs to find a specific guest name within a horizontal 2D seating table and return the corresponding table number to a vertical attendance sheet.

How to Look Up a Horizontal Table and Return a Vertical Result in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Managing event seating charts and tracking attendance using dynamic-array lookup functions.
Observed behavior: The goal is to correctly identify the column containing a match in a 2D range and extract the horizontal header (table number) into a vertical list.

Before you start

Ensure your spreadsheet software is up to date and supports modern dynamic-array functions like XLOOKUP, BYCOL, and LAMBDA.

Solution 1Recommended

Use XLOOKUP, BYCOL, and LAMBDA Functions

This method combines dynamic arrays to search a 2D range and return the corresponding column header, perfect for finding a value in a horizontal table.

Searching for a value across multiple rows and columns requires checking the entire grid. This formula compares every cell in your target range against the lookup value. It then uses BYCOL and LAMBDA to sum the matches in each column. Finally, XLOOKUP finds the column with a match (where the sum is 1) and returns the corresponding table number from the header row.

Select the target cell

Click on the cell in your vertical attendance sheet where you want the table number to appear.

Input the dynamic-array formula

Enter the formula: =XLOOKUP(1, BYCOL(--(E5:K16=target_name), LAMBDA(column, SUM(column))), E4:K4)

Adjust the 2D search range

Replace 'E5:K16' with the actual cell range of your horizontal seating table where the guest names are located.

Specify the lookup value

Replace 'target_name' with the cell reference containing the specific guest name you are looking up (for example, A2).

Set the return array

Replace 'E4:K4' with the range containing your table numbers (the horizontal headers of your seating table).

Apply and fill down

Press Enter to apply the formula. You can then drag the fill handle down to apply this lookup to the rest of your vertical list.

Use XLOOKUP, BYCOL, and LAMBDA Functions

Understanding the Double Negative (--): The '--' operator in the formula converts the TRUE/FALSE results generated by the comparison into 1s and 0s. This allows the SUM function within LAMBDA to count the matches accurately.

Advanced Spreadsheet Functions

Handle Complex Lookups Effortlessly in WPS Office

WPS Spreadsheet fully supports modern dynamic-array functions like XLOOKUP, BYCOL, and LAMBDA. You can seamlessly perform 2D table lookups and manage complex data operations without requiring complicated VBA scripts.

1. Open your seating chart: Launch WPS Spreadsheet and open the workbook containing your horizontal table and vertical list.
2. Enter the formula: Select the destination cell and input the XLOOKUP and BYCOL formula exactly as you would in Excel.
3. Get instant results: Press Enter. WPS Spreadsheet will instantly calculate the dynamic array and fetch your table number.

100% format and formula compatibility with Microsoft ExcelNatively supports advanced functions like XLOOKUP and dynamic arraysLightweight, fast performance even when processing large datasets

microsoft office alternative - wps office

Frequently Asked Questions

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

An #N/A error usually occurs if the target name doesn't exist in the data range (e.g., E5:K16). Double-check for typos, extra spaces, or misspellings in both your lookup value and the source table.

Can I use INDEX and MATCH instead of XLOOKUP for this?

While INDEX and MATCH are powerful, searching across multiple columns and rows (a 2D array) to return a single header is highly complex with them. Using XLOOKUP combined with BYCOL and LAMBDA is the most efficient and beginner-friendly approach for this specific scenario.

What does BYCOL do in this formula?

The BYCOL function applies a custom LAMBDA calculation (in this case, summing the values) to each column in an array individually. It helps identify exactly which column contains the matching name.

Excel & Data Problems