WPS Spreadsheets: Return Payroll Rates with a Two-Criteria Lookup
Question details
The user needs to return the correct payroll rate using both an employee trade and a day or night shift.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A payroll table contains standard, overtime, and double-time rates arranged by trade and shift, and the user wants one formula to retrieve the matching rate.
- Observed behavior
- The desired rate depends on two criteria, making a single-criteria lookup or a long nested IF formula unsuitable.
Confirm that the trade names and shift labels in the lookup cells exactly match the corresponding row and column headers in the payroll table. Identify the rate range and verify that the standard, overtime, and double-time columns are consistently labeled.
Use INDEX and MATCH for a two-criteria payroll lookup
Use this method when the rate table is organized with trades in rows and two header rows that identify the rate type and shift.
INDEX returns the rate from the data area, while the first MATCH finds the employee trade and the second MATCH finds the column where both the rate type and shift match. This avoids a long series of nested IF functions.
In the example, A17 contains the trade, B16 contains the rate type such as standard, overtime, or double-time, and A18 contains the day or night shift.
In WPS Spreadsheets, click the cell where you want the payroll rate to appear.
Enter =INDEX($G$3:$L$24,MATCH($A$17,$F$3:$F$24,0),MATCH(1,($G$1:$L$1=B$16)*($G$2:$L$2=$A$18),0)) and adjust the references to match your table.
Press Enter in current WPS versions that support array calculations. If the formula does not calculate correctly in an older version, press Ctrl+Shift+Enter to confirm it as an array formula.
Change the trade, rate type, or shift input and verify that the result changes to the corresponding payroll rate.
Use XLOOKUP with a combined lookup key
Use this alternative when your WPS Spreadsheets version supports XLOOKUP and you prefer a formula based on one combined key.
Build Two-Criteria Payroll Lookups in WPS Spreadsheets
WPS Spreadsheets lets you retrieve payroll rates by combining INDEX and MATCH criteria for employee trade, rate type, and shift. Its familiar formula interface supports structured lookup tables and smooth editing of Excel-compatible workbooks.
- 1. Open your workbook: Launch WPS Office, choose Spreadsheets, and open the payroll workbook containing the rate table.
- 2. Prepare the lookup inputs: Confirm the trade, rate type, and shift values in the input cells and ensure they match the table headers.
- 3. Enter the lookup formula: Select the result cell and enter the INDEX and MATCH formula with references to your trade column, rate headers, shift headers, and rate range.
- 4. Validate the returned rate: Change each input condition one at a time and check that WPS Spreadsheets returns the correct payroll rate.
Frequently Asked Questions
Can I return standard, overtime, and double-time rates with the same formula?
Yes. Place the selected rate type in the rate-type input cell, such as B16, and the two-criteria formula will return the matching standard, overtime, or double-time rate when the header labels match.
Why does the INDEX and MATCH formula return an error?
Check that the trade and shift values exactly match the table labels, that the lookup and return ranges have compatible dimensions, and that the rate-type header is in the expected row. Remove extra spaces if necessary.
Do I need to use Ctrl+Shift+Enter in WPS Spreadsheets?
Current versions may calculate the paired-condition formula after you press Enter. If your version does not evaluate the array expression correctly, confirm it with Ctrl+Shift+Enter.
Can I use XLOOKUP instead of INDEX and MATCH?
Yes, if your WPS version supports XLOOKUP. The most reliable setup is to create a combined helper key from trade, rate type, and shift, then use XLOOKUP to return the corresponding rate.
