How to look up a value for the month of a date in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7)Turn the date into the first day of its month and look that up: =XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7).
- Result on the example
- $45,250 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
The months in column A are real dates, March 1, 2026 and so on, shown as month names. EOMONTH(E1,-1) is the last day of the previous month, so adding 1 gives the first of the date's own month: March 17 becomes March 1, which matches exactly and returns $45,250.
=XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7)Result45250
E13/17/2026The value in E1.
-1-1Makes the value negative.
EOMONTH(E1,-1)2/28/2026 (46081)EOMONTH: Last day of the month, months away.- start_date
E13/17/2026 - months
-1-1
- start_date
EOMONTH(E1,-1)+146082The + operator adds.
A2:A76 rows × 1 col: Jan 2026, Feb 2026, Mar 2026, Apr 2026, …A range: 6 cells in one column.
B2:B76 rows × 1 col: $42,000, $38,500, $45,250, $47,000, …A range: 6 cells in one column.
XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7)45250XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
EOMONTH(E1,-1)+146082 - lookup_array
A2:A76 rows × 1 col: Jan 2026, Feb 2026, Mar 2026, Apr 2026, … - return_array
B2:B76 rows × 1 col: $42,000, $38,500, $45,250, $47,000, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7). We ran it in Google Sheets mode on the same data and it returns $45,250.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(E1,A2:A7,B2:B7,,-1) | $45,250 | An approximate match on the month starts gives the same result without converting the date, as long as there is a row for every month. |
| =XLOOKUP(TEXT(E1,"yyyy-mm"),TEXT(A2:A7,"yyyy-mm"),B2:B7) | $45,250 | Compares year and month as text, which also works if the table holds dates other than the 1st. |
Common mistakes
- If the month column holds text like "Mar" instead of dates, look up
TEXT(E1,"mmm"), and remember it can't tell 2025 from 2026. - An approximate match on a table with a missing month returns the previous month's value instead of an error.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a value for the month of a date
E2, return the budget for the month that contains the date in E1.Questions
How do I get the previous month's value?
Go back one more month: =XLOOKUP(EOMONTH(E1,-2)+1,A2:A7,B2:B7).
How do I total the actual spending for that month to compare?
Use SUMIFS with the month's start and end: =SUMIFS(amounts,dates,">="&EOMONTH(E1,-1)+1,dates,"<="&EOMONTH(E1,0)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.