How to Select a Monthly Summary Column with XLOOKUP in Excel
Question details
The user needs a formula that uses a month entered in a cell to dynamically select the correct monthly cost column while applying additional row criteria.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user has monthly costs stored in separate columns and wants to pull the matching value by changing only the month cell and criteria.
- Observed behavior
- The formula should return the monthly summary value automatically instead of requiring manual column reference changes.
Confirm that the month cell entry matches the month headers exactly and that the data range includes both the month header row and all monthly cost columns. Also make sure your spreadsheet version supports XLOOKUP before creating the formula.
Use a Nested XLOOKUP to Dynamically Select a Monthly Summary Column
Best when you need one matching value and want the month cell to drive the returned column automatically.
The inner XLOOKUP uses the month cell to return the entire data column for that month. The outer XLOOKUP then applies the row criteria to that returned column, so the formula updates whenever the month or criteria change.
Click the cell where you want the monthly summary result to appear, then click the formula bar to start editing.
Type =XLOOKUP(F2&F3,dept&code,XLOOKUP(F1,months,data)) and replace F1 with the month cell, months with the month header range, data with the monthly data columns, and F2 and F3 with your criteria cells and ranges.
Press Enter to calculate the result, then change the month cell or criteria cells to verify that the returned value updates automatically.
Use FILTER with XLOOKUP to Return All Matching Rows for a Selected Month
Use this when you may have multiple matching rows or want to return an entire filtered set instead of only the first match.
Use WPS Spreadsheets to Build a Dynamic Monthly Summary Lookup
WPS Spreadsheets supports the same nested XLOOKUP and FILTER formulas, letting you automatically select a monthly cost column by a cell value while keeping full compatibility with Excel workbooks.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the Excel workbook that contains your monthly cost columns and criteria.
- 2. Select the result cell: Click the cell where the monthly summary should appear, then click the formula bar.
- 3. Enter the dynamic lookup formula: Type =XLOOKUP(F2&F3,dept&code,XLOOKUP(F1,months,data)) and replace the month cell, header range, data range, and criteria references with your worksheet ranges.
- 4. Press Enter and verify: Press Enter, then update the month cell or criteria to confirm that the returned value changes automatically.
Frequently Asked Questions
How does the inner XLOOKUP select the correct monthly column?
The inner XLOOKUP looks up the month cell value in the month header range and returns the entire matching column from the data range. That returned column is then used as the return array for the outer XLOOKUP.
Can I add more than two row criteria to this formula?
Yes. Concatenate additional criteria cells and ranges, such as F2&F3&F4 and dept&code&area. If the conditions become complex, a FILTER formula with multiplication may be clearer.
Why does my nested XLOOKUP return #N/A?
Check that the month cell exactly matches a header in the month range, that criteria cells match values in the lookup ranges, and that all referenced ranges have the same dimensions.
Does this work when the month cell contains a date?
It works only if the month cell and month headers use the same data type and display format. If headers are text such as January, use text in the month cell; if they are dates, match the date values.
How can I return multiple rows for a selected month?
Use FILTER with XLOOKUP, such as =FILTER(XLOOKUP(F1,months,data),(dept=F2)*(code=F3)), to return all rows that satisfy the criteria for the selected month.
