Function Problems

How to Use Excel Formulas to Return a Value Based on Entered Month

WPS Editor Sep 28, 2026 870 views

Question details

The user needs to retrieve a specific numerical or text value associated with a given month name using an Excel lookup formula.

How to Return a Value Based on the Entered Month Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Looking up and returning corresponding data based on a specific month entered in an input cell.
Observed behavior: The user wants a formula that matches an inputted month name with its corresponding value from a predefined data table, such as returning 600 when 'June' is entered.

Before you start

Ensure your data is organized in a clear tabular format where one column contains the month names and the adjacent column contains the corresponding values you want to retrieve.

Solution 1Recommended

Use INDEX and MATCH Functions (Recommended)

The combination of INDEX and MATCH is the most flexible and robust way to look up values in spreadsheets based on a specific criterion like a month name.

INDEX and MATCH work together to find a relative position of an item in an array and then retrieve a value at that specific position. This method doesn't require the lookup column to be on the far left of your table.

Prepare your lookup table

Create your data table. For example, enter your month names in cells A1:A12 (January to December) and the corresponding values in cells B1:B12.

Set up your input cell

Decide which cell will be used to type the month name. For this example, use cell C2.

Enter the formula

Select the cell where you want the result to appear (e.g., D2) and type the following formula: =INDEX(B1:B12,MATCH(C2,A1:A12,0)).

Execute the function

Press Enter. The cell will now display the value associated with the month typed in cell C2.

Use INDEX and MATCH Functions (Recommended)

Exact Match Requirement: The '0' at the end of the MATCH function ensures the formula looks for an exact match. Ensure there are no trailing or leading spaces in your text.

Solution 2

Use VLOOKUP Function

If your month names are strictly in the first column of your data range, VLOOKUP is a simpler alternative for beginners to retrieve the adjacent values.

Advanced Spreadsheet Tool

Easily Execute Lookup Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup formulas like INDEX, MATCH, and VLOOKUP. You can easily manage your data, perform complex calculations, and automate month-based data retrieval without any hassle.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook or create a new blank spreadsheet.
2. Input Data: Create your reference table by typing the months in column A and the corresponding numerical values in column B.
3. Apply Formula: Click on the destination cell and type =INDEX(B1:B12,MATCH(C2,A1:A12,0)).
4. Get Results: Press the Enter key to instantly retrieve the corresponding value based on your inputted month.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in formula suggestions and syntax highlighting to prevent syntax errors.Lightweight software that processes complex lookup arrays and large data sets quickly.Free to use with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #N/A error when looking up a month?

The #N/A error typically occurs when the entered month does not exactly match the text in the lookup table. Check for typos or hidden spaces (trailing or leading spaces) in both the input cell and the data table. Using the TRIM function can help clean up text entries.

Are INDEX and MATCH formulas case-sensitive?

No, standard lookup formulas like INDEX/MATCH and VLOOKUP are case-insensitive by default. Entering 'June', 'june', or 'JUNE' will all yield the same result.

Can I use XLOOKUP instead of INDEX and MATCH?

Yes. If you are using a modern version of spreadsheet software that supports the XLOOKUP function, you can use the simpler formula =XLOOKUP(C2, A1:A12, B1:B12) to achieve the exact same result.

What if I enter a month number instead of the month name?

If your input cell contains a month number (e.g., 6 for June), your lookup table must also contain numbers (1 through 12) in the reference column instead of text names. Alternatively, you can use the CHOOSE function, such as =CHOOSE(C2, 100, 200, 300, 400, 500, 600...).

Excel & Data Problems