WPS Spreadsheet XLOOKUP Formula to Return a Value by Month
Question details
The user needs an XLOOKUP formula that selects a month, searches the corresponding month column, and returns the matching value from an associated row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The worksheet contains month headers in B1:G1, lookup data in B2:G10, related return values in A2:A10, a selected month in I2, and a lookup value in J2.
- Observed behavior
- The goal is to return the matching value in K2 based on both the selected month and the lookup value, then fill the formula down.
Confirm that the month names in I2 and B1:G1 use the same text or date format, and verify that the lookup values in J2 match entries in the selected month column. Check that the return range A2:A10 aligns row-for-row with B2:G10.
Use XLOOKUP with MATCH and INDEX to return the monthly value
Use this formula when the month is selected separately from the lookup value and the matching result must come from column A.
MATCH identifies the selected month in the header row, INDEX returns the corresponding data column, and XLOOKUP searches that column before returning the associated value from A2:A10.
Open the worksheet in WPS Spreadsheet and select cell K2, or the first cell where the monthly lookup result should appear.
In the formula bar or directly in K2, enter =XLOOKUP(J2,INDEX($B$2:$G$10,0,MATCH(I2,$B$1:$G$1,0)),$A$2:$A$10,"-") and press Enter.
Select K2 and drag the fill handle downward, or use Home > Fill > Down, to apply the formula to the remaining rows.
If no match is found, the formula returns a hyphen because of the "-" argument. Replace "-" with another message or an empty string if needed.
Build a Monthly XLOOKUP in WPS Spreadsheet
WPS Spreadsheet lets you combine XLOOKUP, MATCH, and INDEX to select a month dynamically and return the corresponding value. Enter the formula in K2, verify the result, and fill it down for the rest of the table.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the month-based lookup table.
- 2. Review the table layout: Confirm the month headers are in B1:G1, the lookup data is in B2:G10, the return values are in A2:A10, and the criteria cells are I2 and J2.
- 3. Enter the formula: Select K2 and enter =XLOOKUP(J2,INDEX($B$2:$G$10,0,MATCH(I2,$B$1:$G$1,0)),$A$2:$A$10,"-") in the formula bar.
- 4. Fill results down: Press Enter, select K2, and drag the fill handle down to populate the remaining result cells.
Frequently Asked Questions
What does the XLOOKUP formula return in K2?
It searches for the value in J2 within the data column associated with the month in I2, then returns the corresponding value from A2:A10. If no match exists, it returns a hyphen.
Why are MATCH and INDEX used together in this formula?
MATCH finds the selected month in B1:G1, while INDEX uses that position to return the corresponding column from B2:G10 for XLOOKUP to search.
How can I apply the monthly lookup to other rows?
Enter the formula in K2, press Enter, then drag K2's fill handle downward or use Home > Fill > Down. The absolute ranges remain fixed while I2 and J2 adjust by row.
What should I check if the formula returns a hyphen?
Make sure the month in I2 exactly matches a header in B1:G1 and that the value in J2 exists in the selected month column. Check for extra spaces, inconsistent date formats, or mismatched text and numbers.
