Return a Complete Data Range for a Selected Character in Excel
Question details
Return all available fields for a character selected in a worksheet cell when the source information is currently stored in separate ranges.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user selects or enters a character name and wants the related row or complete data block to appear automatically.
- Observed behavior
- Separate, non-contiguous source ranges make it difficult to use one lookup formula to return all related information.
Make sure each source block uses the same column order, such as Character, Class, Level, and Status. Put the selected character name in a dedicated input cell, such as H2, before entering a lookup formula.
Combine the character records and return matching data with FILTER
Use this method when each character record can be placed in one continuous database range. It is the most flexible option when a character may have more than one matching record.
A single continuous source range lets a dynamic-array formula return every matching row and all related columns automatically. Keep character names in the first column of the source data for a clear lookup condition.
Move or copy the separate character blocks into one continuous range, for example A1:E100, with one header row in A1:E1. Use the same fields and column order for every record.
Click cell H2 and type a character name, or create a drop-down list through Data > Data Validation so users can select a name.
Click the first output cell, such as H5, and enter =FILTER($A$2:$E$100,$A$2:$A$100=$H$2,"No matching character"). Press Enter to spill all matching character data into adjacent cells.
Replace A2:E100 with the full data range and A2:A100 with the character-name column. Leave the cells below and to the right of the formula empty so the results can expand.
Use XLOOKUP to return one complete matching record
Use this method when every character name appears only once and you need one matching row returned across several columns.
Create named ranges for separate character data blocks
Use named ranges when the source blocks must remain separate and each selected character has its own defined data area.
Return Selected Character Data with Dynamic Formulas in WPS Spreadsheets
WPS Spreadsheets can organize character records into a single data range and use dynamic lookup formulas to return the details associated with a selected name. Create a selection cell, then use FILTER for all matching rows or XLOOKUP for one matching record.
- 1. Organize the source records: In WPS Spreadsheets, place all character records in one continuous range with consistent headers.
- 2. Add a character selection cell: Select an input cell, then choose Data > Data Validation to create a list of character names.
- 3. Return all matching records: Select the output cell and enter a FILTER formula using the character-name column as the condition.
- 4. Use XLOOKUP for a single record: When each character is unique, enter an XLOOKUP formula that returns the related columns from the selected record.
Frequently Asked Questions
How do I return an entire row based on a selected name in Excel?
If names are unique, use XLOOKUP with the selected name as the lookup value, the name column as the lookup array, and the full row or desired columns as the return array. For example: =XLOOKUP(H2,A2:A100,A2:E100,"No match").
Why does FILTER return a spill error?
A spill error occurs when cells in the formula's output area are not empty or the formula is inside an Excel table. Clear the blocked cells or move the FILTER formula to an open range outside the table.
Can FILTER return multiple records for the same character?
Yes. FILTER returns every row where the character-name column equals the selected value. This makes it more suitable than XLOOKUP when duplicate character names or multiple entries are expected.
Can I look up data from separate non-contiguous ranges?
You can define each block as a named range and use INDIRECT to refer to the selected range, but this is harder to maintain. Combining the blocks into one consistent database range is usually more reliable for FILTER and XLOOKUP.
