Fix the Excel XLOOKUP Formula Syntax Error for a Date Month Lookup
Question details
Correct an XLOOKUP formula that uses the MONTH function to find a matching month number and return a value from a list.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants a table formula to remain blank when the Date field is empty and otherwise look up a result based on the month of that date.
- Observed behavior
- The formula has invalid syntax because the MONTH function and XLOOKUP arguments are placed incorrectly or a closing parenthesis is missing.
Confirm that the Date column contains valid Excel dates and that Lists!C2:C13 contains month numbers such as 1 through 12. Check whether your Excel regional settings use commas or semicolons to separate formula arguments.
Enter the corrected XLOOKUP formula with MONTH as the lookup value
Use this solution when the formula should return a value for the month in [@Date] and show a blank result when the date cell is empty.
MONTH must receive only the date value and return a month number. That month number becomes the first argument of XLOOKUP, followed by the lookup range and return range.
Click the cell in the Excel table column where you want the month-based result to appear.
In the formula bar, enter =IF([@Date]="","",XLOOKUP(MONTH([@Date]),Lists!$C$2:$C$13,Lists!$D$2:$D$13)) and press Enter.
Open the Lists worksheet and verify that Lists!$C$2:$C$13 contains the lookup month numbers and Lists!$D$2:$D$13 contains the corresponding values to return.

Use semicolons if Excel does not accept comma-separated arguments
Use this solution when Excel flags the formula immediately after you paste it, especially in regions where semicolons are the required list separators.
Check for unsupported XLOOKUP or invalid date values
Use this solution if the formula syntax is accepted but Excel displays #NAME?, #VALUE!, or an unexpected result.
Build a Month-Based XLOOKUP Formula in WPS Spreadsheets
WPS Spreadsheets lets you create and edit month-based lookup formulas with familiar worksheet tabs, formula entry, and Excel-compatible table references. Enter the corrected IF, MONTH, and XLOOKUP formula to return the matching value while keeping rows blank when no date is entered.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the Date column and Lists worksheet.
- 2. Select the result column cell: Click the first cell in the table column where the month lookup result should be displayed.
- 3. Enter the corrected formula: Type =IF([@Date]="","",XLOOKUP(MONTH([@Date]),Lists!$C$2:$C$13,Lists!$D$2:$D$13)) in the formula bar and press Enter.
- 4. Fill the formula through the table: Use the fill handle on the selected cell or let the table calculated-column behavior apply the formula to the remaining rows.

Frequently Asked Questions
Why does MONTH need to be inside XLOOKUP in this formula?
MONTH([@Date]) converts the table row's date into a number from 1 to 12. XLOOKUP uses that number as its lookup_value to search Lists!$C$2:$C$13.
What does [@Date] mean in an Excel formula?
[@Date] is a structured table reference. It means the value in the Date column for the current table row. It works when the formula is entered inside an Excel table.
Why does my XLOOKUP formula show #NAME?
#NAME? can mean that your Excel version does not support XLOOKUP, that XLOOKUP was misspelled, or that the formula uses an unsupported localized function name. Test XLOOKUP in a blank cell and verify your Excel version.
How can I return blank when the date cell is empty?
Wrap the lookup in an IF check: =IF([@Date]="","",XLOOKUP(...)). The first empty string returns a blank result whenever the current row's Date cell has no value.
Should the lookup column contain month names or numbers?
For the shown formula, Lists!C2:C13 must contain numeric month values from 1 to 12 because MONTH([@Date]) returns a number. If your list contains names such as January, use TEXT([@Date],"mmmm") as the lookup value instead.




