Function Problems

Return Excel Values Based on a Month Selected in a Drop-Down

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

Return a value associated with the month selected in cell B9 using a maintainable lookup formula.

Return Excel Values Based on a Month Selected in a Drop-Down
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user selects a month such as Jan or Feb from a drop-down and wants to retrieve its corresponding worksheet value.
Observed behavior
The result cell should show the value for the selected month without requiring a growing nested IF or INDIRECT formula.
Before you start

Check that B9 contains the month drop-down and that its entries match the month labels in your lookup table. Choose an empty result cell and identify the source cell for each month.

Solution 1Recommended

Use XLOOKUP with Month Headers and Values

Use this method when your Excel version supports XLOOKUP and the month labels and values are arranged across columns.

1
Create the lookup row

Enter month labels such as Jan and Feb in C1:N1, matching the entries in the B9 drop-down.

2
Add the corresponding values

Enter each value directly below its month in C2:N2. To retrieve a value from another worksheet, enter a reference such as =Jan!B2 under Jan and =Feb!B2 under Feb.

3
Enter the lookup formula

Select an empty result cell and enter =XLOOKUP(B9,$C$1:$N$1,$C$2:$N$2,"Not found").

4
Test the drop-down

Select Jan and then Feb in B9. Confirm that the result cell displays the value below each matching month header.

Use XLOOKUP with Month Headers and Values
Keep the ranges aligned: The month headers and return values must cover the same number of columns. XLOOKUP returns "Not found" if B9 does not match a header.
Work with month lookups in WPS

Return Month-Based Values in WPS Spreadsheet

Use a month drop-down and a VLOOKUP table in WPS Spreadsheet to return the value associated with the selected month.

  1. 1. Open the workbook: Open WPS Spreadsheet and choose Open to select the workbook containing the month drop-down in B9.
  2. 2. Create the lookup table: Enter month labels in D2:D13 and corresponding values or worksheet references in E2:E13.
  3. 3. Add the formula: Select an empty result cell and enter =IFERROR(VLOOKUP(B9,$D$2:$E$13,2,FALSE),"Not found").
  4. 4. Test and save: Select different months in B9 to check the results, then use File > Save to save the workbook.
Look up values from a selected month without nested IF formulasOpen and edit Microsoft Excel XLSX and XLS filesMaintain month labels and linked values in one tableWork with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Can the values come from different worksheets?

Yes. In the lookup table, link each value cell to its source, such as =Jan!B2 for the Jan worksheet and =Feb!B2 for the Feb worksheet. The lookup formula then returns the appropriate linked value.

Why does the formula show "Not found"?

Check that the selection in B9 matches a month label in the lookup range and that there are no extra spaces. Also verify that the formula points to the correct table.

What can I use if my Excel version does not support XLOOKUP?

Arrange the months in the first column of a vertical table and their values in the second, then use =VLOOKUP(B9,$D$2:$E$13,2,FALSE).

How do I add another month to the lookup?

Add the month label and its value to the table, update the B9 drop-down list if necessary, and extend the lookup formula's ranges to include the new entries.