How to Look Up Values by Name and Month in WPS Office Spreadsheets
Question details
The user needs a formula to return an amount from one worksheet when both the name and month match criteria entered on another worksheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has one worksheet with names, months, and amounts, and another worksheet where they enter a name and a month to retrieve the matching amount.
- Observed behavior
- The desired result is a dynamic lookup that returns the amount only when both the name and the month conditions match.
Open both worksheets and verify that the Name and Month columns use consistent spelling, capitalization, and no extra spaces; otherwise the two-criteria formula may return an empty result.
Use XLOOKUP to Match Name and Month in One Formula
Best for newer WPS Office and Excel versions because it handles two criteria without a helper column.
XLOOKUP multiplies two logical arrays to create a single match condition, so the formula returns the amount only where both criteria are true.
Open WPS Office Spreadsheets and click the cell on Sheet 2 where you want the amount to appear.
Type =XLOOKUP(1,('Sheet 1'!$A$2:$A$100=A2)*('Sheet 1'!$C$2:$C$100=B2),'Sheet 1'!$B$2:$B$100,"") and press Enter.
Change Sheet 1, A2, B2, and the range rows to match your actual workbook layout; keep both lookup ranges the same size.
Use INDEX and MATCH as an Older-Version Alternative
Use this if XLOOKUP is not available in your WPS Office or Excel version.
Create a Helper Column Combining Name and Month
Useful for very old versions or when you prefer a simpler lookup formula.
Return the Matching Amount by Name and Month in WPS Office Spreadsheets
Use WPS Office Spreadsheets to perform a two-criteria lookup with XLOOKUP or INDEX and MATCH, and return the correct amount from another worksheet without switching to Microsoft Excel.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains your two worksheets.
- 2. Select the lookup result cell: Go to Sheet 2 and click the cell where the amount should appear.
- 3. Enter the two-criteria lookup formula: Type =XLOOKUP(1,('Sheet 1'!$A$2:$A$100=A2)*('Sheet 1'!$C$2:$C$100=B2),'Sheet 1'!$B$2:$B$100,"") and press Enter.
- 4. Verify the returned amount: Check that the formula returns the correct amount when the name in A2 and month in B2 match a row in Sheet 1.
Frequently Asked Questions
Can I use XLOOKUP in WPS Office Spreadsheets for a two-criteria lookup?
Yes, recent WPS Office versions support XLOOKUP. Use =XLOOKUP(1,(name_range=name_criteria)*(month_range=month_criteria),amount_range,"") and adjust the sheet references.
What if my WPS Office version does not have XLOOKUP?
Use INDEX and MATCH with two criteria: =INDEX(amount_range,MATCH(1,(name_range=name_criteria)*(month_range=month_criteria),0)). In older versions, confirm it with Ctrl+Shift+Enter.
Why does my two-criteria formula return #N/A or an empty result?
Check that the Name and Month criteria match exactly, including capitalization and extra spaces, and verify that all lookup ranges have the same row count.
Can the lookup pull data from a worksheet with spaces in its name?
Yes. Wrap the sheet name in single quotes, for example 'Sheet 1'!$A$2:$A$100, as shown in the formulas.
Should I use absolute references in the lookup formula?
Yes, lock the lookup ranges with dollar signs such as $A$2:$A$100 so they do not shift when you copy the formula to other cells.
