# How to look up a value for the month of a date in Excel

Turn the date into the first day of its month and look that up: `=XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7)`.

**Excel:** `=XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Month | Budget |  | Date | 2026-03-17 |
| 2026-01-01 | 42000 |  | Monthly budget |  |
| 2026-02-01 | 38500 |  |  |  |
| 2026-03-01 | 45250 |  |  |  |
| 2026-04-01 | 47000 |  |  |  |
| 2026-05-01 | 51500 |  |  |  |
| 2026-06-01 | 49800 |  |  |  |

Formula in E2: `=XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7)`

Result: $45,250

## 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.

## Other ways

- `=XLOOKUP(E1,A2:A7,B2:B7,,-1)`: 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)`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-value-for-month