How to Look Up Room Numbers for Names Marked with X in Excel
Question details
The user needs a way to extract or look up room numbers associated with specific names from a 2D grid, where names are listed in rows, rooms in columns, and active intersections are marked with an 'X'.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing room assignments, schedules, or rosters using a cross-tabulated matrix and converting those assignments into a readable list of matches.
- Observed behavior
- The data is trapped in a 2D grid layout, making it difficult to generate a flat list of room assignments without manual extraction or complex referencing.
Before applying formulas or Power Query, verify that your table has distinct column headers (room numbers) and row headers (names), with no blank columns breaking the data range.
Use Power Query to Unpivot Data
Power Query provides the most reliable and repeatable method to convert a 2D matrix into a flat list, allowing you to easily filter and match names with rooms.
Unpivoting your data table transforms the columns (rooms) into rows, making standard data filtering incredibly easy.
Select your entire data range including headers, then navigate to the Data tab and click 'From Table/Range'.
In the Power Query Editor window, click on the column header that contains the names to highlight it.
Right-click the highlighted name column header and select 'Unpivot Other Columns'. This creates two new columns: 'Attribute' (containing room numbers) and 'Value' (containing the markers).
Click the filter dropdown arrow on the new 'Value' column, uncheck everything except 'X', and click OK.
Click 'Close & Load' on the Home tab to output the matched list of names and room numbers to a new worksheet.
Extract Matches Using Dynamic Array Formulas
If you prefer a formula-based approach that updates instantly without refreshing, you can combine modern dynamic array functions like TOCOL, FILTER, and TEXTJOIN.
Easily Handle Complex Data Lookups with WPS Spreadsheet
WPS Spreadsheet provides robust support for modern dynamic array formulas and advanced data filtering, making it incredibly simple to analyze matrices and room assignments.
- 1. Open your grid: Launch WPS Spreadsheet and open the file containing your names and room numbers.
- 2. Apply dynamic arrays: Select an empty cell and enter your FILTER or TEXTJOIN formulas directly to extract the active room assignments.
- 3. Instantly calculate: Press Enter to instantly populate your data; the results will update automatically if you change an 'X' marker in the grid.
- 4. Save in XLSX format: Save your completed roster in standard .xlsx format to share easily with Microsoft Excel users.
Frequently Asked Questions
Why is the dynamic array formula returning a #NAME? error?
This error usually occurs if you are using an older version of Excel that does not support modern dynamic array functions like TOCOL or LAMBDA. If you cannot upgrade, consider using the Power Query method instead.
Can I use this method if my marker is a checkmark instead of an X?
Yes. Simply change the "X" in your dynamic formula to match the exact symbol or word used in your grid, or adjust the filter criteria in the Power Query 'Value' column accordingly.
Will the extracted list update automatically if I move an X to a different room?
If you use the formula-based solution, the results will recalculate and update instantly. If you use the Power Query solution, you must right-click anywhere in the output table and select 'Refresh' to see the latest changes.
