How to Use a Drop-Down Month to Select an Excel Lookup Column in WPS Spreadsheets
Question details
The user wants a month selected from an Excel drop-down list to dynamically choose the correct lookup column in an INDEX and MATCH formula.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a data table that has monthly columns, the user wants one drop-down cell to control which month column is returned by a lookup.
- Observed behavior
- Currently the lookup column is fixed or the formula does not update when the drop-down month changes; the goal is a formula that returns values from the selected month column.
Verify that the source data has a single header row containing the month names, and make sure the drop-down list values match those header text values exactly, including spaces or date formatting.
Use INDEX and MATCH with a Data Validation month drop-down
Best for most WPS Spreadsheets versions; returns a value from the row where the lookup item matches and the column where the drop-down month matches the header.
This method uses a validation drop-down cell as the column match value, so changing the month automatically switches the column in the INDEX/MATCH formula.
Click the cell where you want the month selector, such as D2, then go to the Data tab and click Data Validation.
In the Data Validation dialog, set Allow to List and select the monthly header cells containing Jan, Feb, Mar, etc., for example Data!$G$4:$T$4.
In the result cell, enter =INDEX(Data!$G$5:$T$144,MATCH($B5,Data!$A$5:$A$144,0),MATCH($D$2,Data!$G$4:$T$4,0)) and press Enter.
Change the drop-down month in D2 and confirm that the formula returns the value from the selected month column for the matching row.
Use XLOOKUP or dynamic array formulas in Microsoft 365 or compatible WPS versions
Useful when you need to return and sort the latest rows from the selected month; requires a version that supports dynamic array functions such as XLOOKUP, FILTER, SORT, and TAKE.
Use a Month Drop-Down to Switch Lookup Columns in WPS Spreadsheets
WPS Spreadsheets can build a dynamic month selector with Data Validation and an INDEX/MATCH formula, so one drop-down cell changes the column used for lookups without rewriting formulas.
- 1. Open your Excel file in WPS Spreadsheets: Launch WPS Office, click Open, and select the .xlsx workbook that contains the monthly data table.
- 2. Add the month drop-down: Select the cell for the month selector, go to the Data tab, click Data Validation, choose List, and select the month header range.
- 3. Build the dynamic lookup formula: In the result cell, enter =INDEX(Data!$G$5:$T$144,MATCH(lookup_value,Data!$A$5:$A$144,0),MATCH(month_cell,Data!$G$4:$T$4,0)) and press Enter.
- 4. Change the month and verify: Choose a different month from the drop-down and confirm that the returned value updates to the matching column.
- 5. Save in Excel format: Click Save As and choose .xlsx to keep full compatibility with Microsoft Excel.
Frequently Asked Questions
How do I make the drop-down month control the INDEX MATCH column automatically?
Put the selected month in a helper cell such as D2 with Data Validation. In the INDEX formula, use MATCH(D2,Data!$G$4:$T$4,0) as the column argument so changing D2 changes the lookup column.
Why does my INDEX MATCH return #N/A after I change the month?
The drop-down value probably does not exactly match the header cell. Check for leading or trailing spaces and date formatting, then test with =MATCH(D2,Data!$G$4:$T$4,0) in a separate cell to see whether an exact header match is found.
Can I use this method when month headers are real dates instead of text?
Yes. Apply a consistent date format to both the drop-down cell and the header cells. If the date values match, the MATCH function can find the correct header column.
Does WPS Spreadsheets support XLOOKUP or FILTER for dynamic month lookups?
Some WPS versions support XLOOKUP, FILTER, and SORT, but availability varies by platform. For the most consistent cross-version behavior, use INDEX and MATCH with a drop-down cell.
Can I retrieve the top 10 values from the selected month column?
In Microsoft 365, you can use =TAKE(SORT(FILTER(Data!$G$5:$T$144,D2=Data!$G$4:$T$4),,-1),10). In WPS, if TAKE is not available, use the INDEX/MATCH column approach and combine it with SORT or LARGE depending on your version.
