Match Excel Master Sheet Data to a Selected Day
Question details
Return rows from a Master worksheet that match a day chosen from a drop-down list.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user selects a day, such as Sunday through Friday, and wants to display the corresponding Master worksheet data on another sheet.
- Observed behavior
- The selected day should determine which matching Master worksheet rows are displayed.
Check that the Master sheet has day names in column A and the data to return in columns B through G. The examples assume the day drop-down is in cell A1 on the results sheet and data occupies rows 2 through 1000.
Return all matching rows with FILTER
Use FILTER in a supported Excel version to display every Master row matching the selected day.
On the results sheet, choose a day from the drop-down in A1. Make sure its spelling matches the day names on the Master sheet.
Select an empty cell such as A3 and enter =FILTER(Master!B2:G1000,Master!A2:A1000=$A$1,"No matches"). Adjust the ranges to cover your data.
Press Enter. The matching rows spill into adjacent cells; clear any existing content in that area if Excel shows a #SPILL! error.

Return the first matching row with INDEX and MATCH
Use INDEX and MATCH when FILTER is unavailable and you only need the first matching row.
Match a Selected Day to Master Sheet Data in WPS Spreadsheet
Use INDEX and MATCH in WPS Spreadsheet to look up the selected day and return the first corresponding row from a Master sheet.
- 1. Open the workbook: In WPS Spreadsheet, open your XLSX file and select the worksheet containing the day drop-down.
- 2. Choose a day: Select a day in cell A1 that matches an entry in column A of the Master sheet.
- 3. Enter the formula: In cell A3, enter =IFERROR(INDEX(Master!B$2:B$1000,MATCH($A$1,Master!$A$2:$A$1000,0)),"No match") and press Enter.
- 4. Return the remaining columns: Drag the fill handle from A3 through F3 to display the first matching row's values from Master columns B through G.

Frequently Asked Questions
How do I create the day drop-down in Excel?
Select A1 on the results sheet, then choose Data > Data Validation. Select List and enter the day names as a comma-separated source, or select a range containing them.
How can I return every row for the selected day?
In a version of Excel that supports FILTER, enter =FILTER(Master!B2:G1000,Master!A2:A1000=$A$1,"No matches") in an empty results cell.
Why does the formula show no match?
Check that A1 contains the same day name as Master column A, without extra spaces, and that the formula ranges include the relevant rows.
Does the formula automatically use the current sheet tab name?
No. The examples use the day selected in A1. If each day has its own sheet, place or select that sheet's day name in A1.




