How to Perform a Horizontal Lookup and Group Results in Columns
Question details
The user needs to match a specific value against a header row and return the corresponding column's data, split into groups of ten rows per column.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Reorganizing and extracting large vertically-stacked lookup results into a more readable, multi-column format.
- Observed behavior
- Standard lookups return a single long column of data. The goal is to dynamically wrap the returned array into multiple columns of a specified row limit.
Ensure your data range contains headers that match your lookup value exactly, and verify if your spreadsheet version supports modern dynamic array functions like WRAPCOLS and XLOOKUP.
Use WRAPCOLS and XLOOKUP Functions (Recommended)
For modern spreadsheet software, this is the most straightforward method to return matched values and dynamically wrap them into columns of a specific size.
The XLOOKUP function locates your target column, and the WRAPCOLS function takes that one-dimensional array and wraps it into a two-dimensional grid based on the row limit you specify.
Click on the cell where you want the grouped columns to start (for example, cell P15).
Type the formula =WRAPCOLS(XLOOKUP(P12, B1:J1, B2:J33), 10, ""). In this formula, P12 is the lookup value, B1:J1 is the header row, and B2:J33 contains the data to be returned.
Press Enter. The formula will automatically spill the extracted data into groups of 10 rows per column. The empty quotation marks at the end ensure that empty cells are left blank instead of returning an error.
Use a Combination of INDEX, MATCH, MOD, ROW, and COLUMN
If you are using an older spreadsheet version that does not support dynamic arrays, use this robust legacy formula to calculate the exact position of the data.
Group Lookup Results Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like XLOOKUP and WRAPCOLS, allowing you to easily look up and format data without resorting to complex legacy formulas.
- 1. Open your data file: Launch WPS Spreadsheet and open your workbook.
- 2. Select the target cell: Click on the cell where you want the split columns to appear.
- 3. Enter the formula: Type your =WRAPCOLS(XLOOKUP(...)) formula and press Enter.
- 4. View the results: Watch the data seamlessly spill into your specified row groupings automatically.
Frequently Asked Questions
Why is my WRAPCOLS formula returning a #NAME? error?
This error occurs if your spreadsheet software version does not support modern dynamic array functions like WRAPCOLS. You will need to use the INDEX and MATCH alternative or upgrade to a modern suite like WPS Office.
Can I group the lookup results into rows instead of columns?
Yes, you can use the WRAPROWS function instead of WRAPCOLS. It works similarly but wraps the data horizontally into rows of a specified size before moving to the next row.
What does the number '10' represent in the WRAPCOLS formula?
The '10' is the wrap_count argument. It specifies the maximum number of values (or rows) to display in a single column before the function starts a new column.
