Create an Excel Lookup Report From a Name Drop-Down List
Question details
Create a report that automatically displays sales and other information when a user selects a person from a name drop-down list.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains source data for people and needs a report area driven by the selected name in cell E4.
- Observed behavior
- The report should return individual values with XLOOKUP or display every matching source row with FILTER.
Format the source range as an Excel table and give it a clear name, such as Data. Confirm that the Name column uses consistent spelling and contains no unintended spaces before creating lookup formulas.
Use XLOOKUP to return individual report fields for the selected name
Use this method when the report needs one value per field, such as Sales, Region, Manager, or Total Orders.
XLOOKUP searches the Data[Name] column for the name selected in E4 and returns the matching value from the specified column. Use a separate formula for each field in the report.
Select the source data, press Ctrl+T, select My table has headers, and click OK. On the Table Design tab, set the table name to Data.
Select cell E4, go to Data > Data Validation, choose List under Allow, and select the cells that contain the available names as the Source.
Click the report cell for Sales and enter =XLOOKUP($E$4,Data[Name],Data[Sales],"Not found"). Press Enter.
Copy the formula to other report cells, then replace Data[Sales] with the needed return column, such as Data[Region], Data[Manager], or Data[Orders].
Use FILTER to display every record for the selected name
Use this method when one person can have multiple transactions, weekly entries, or other matching rows.
Build a Name-Driven Lookup Report in WPS Spreadsheets
WPS Spreadsheets lets you create a drop-down selector and connect report fields or record lists to the selected name with XLOOKUP and FILTER formulas. Import an existing workbook or build the report with familiar spreadsheet tools.
- 1. Open the source workbook: Open the workbook in WPS Spreadsheets and confirm that the source columns include Name and the fields you want to report.
- 2. Create a drop-down list: Select the report selector cell, such as E4, then use Data > Data Validation > List to choose the range containing names.
- 3. Add an individual-field lookup: Select a report cell and enter an XLOOKUP formula, such as =XLOOKUP($E$4,Data[Name],Data[Sales],"Not found").
- 4. Show all matching records when needed: Select the first results cell and enter =FILTER(Data,Data[Name]=$E$4,"No matching records") to return every matching row.
Frequently Asked Questions
How do I create a name drop-down list for an Excel report?
Select the report selection cell, such as E4, then choose Data > Data Validation. Set Allow to List and select the range that contains the available names.
Why does XLOOKUP show "Not found" after I select a name?
Check that the selected name exactly matches a value in Data[Name]. Remove extra spaces, confirm the table name and column names are correct, and make sure the lookup cell is $E$4.
Can XLOOKUP return more than one row for the same person?
No. XLOOKUP returns one matching value for each formula. If the same person has multiple source rows, use =FILTER(Data,Data[Name]=$E$4,"No matching records") instead.
How do I make the lookup report include newly added weekly data?
Store the source range in an Excel table by selecting it and pressing Ctrl+T. Add new rows directly below the table so formulas using Data[Name] and other structured references include them automatically.
