WPS Spreadsheet Formula to Match Three Values and Return a Number
Question details
The user needs to match three values against columns A, B, and C and return the related number from column D.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A combined lookup value is entered in F2 using the three criteria separated by spaces, and the matching result should appear in G2.
- Observed behavior
- The goal is for G2 to calculate and return the number associated with the three matching values, then repeat the calculation for additional rows.
Confirm that columns A:D contain the lookup data and that column D contains numeric results. Make sure F2 combines the three lookup values in the same order and uses single spaces between them.
Use SUMPRODUCT to match three values and return the related number
Use this formula when the three lookup criteria are combined into one space-separated value in column F.
SUMPRODUCT compares the combined values from columns A, B, and C with the lookup text in F2. It then returns the corresponding value from column D, even when the result is not located in the first matching row.
Open the worksheet in WPS Spreadsheet and select cell G2, or the cell where you want the returned number to appear.
Enter =SUMPRODUCT(($A$2:$A$10&" "&$B$2:$B$10&" "&$C$2:$C$10=F2)*$D$2:$D$10) in the formula bar, then press Enter.
Verify that F2 contains the three values in the same order as columns A, B, and C, with one space between each value.
Select G2 and drag the fill handle downward, or use Home > Fill > Down, to calculate results for the remaining lookup values in column F.
Create a helper column for the three-value key
Use a helper column when you want a visible combined key that is easier to inspect and reuse in additional formulas.
Build Three-Criteria Lookup Formulas in WPS Spreadsheet
WPS Spreadsheet lets you combine multiple criteria, use SUMPRODUCT to calculate the matching result, and fill formulas down through your data with a familiar spreadsheet interface.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
- 2. Prepare the lookup values: Place the three criteria in columns A, B, and C, the result numbers in column D, and the combined lookup text in F2.
- 3. Enter the formula: Select G2 and enter =SUMPRODUCT(($A$2:$A$10&" "&$B$2:$B$10&" "&$C$2:$C$10=F2)*$D$2:$D$10).
- 4. Fill the results: Press Enter, then drag G2's fill handle down to apply the calculation to other lookup values.
Frequently Asked Questions
Where should I enter the three-criteria lookup formula?
Enter the formula in G2, or in the first cell of the result column. Then fill it down for the remaining rows.
How should the lookup value in F2 be formatted?
F2 should contain the corresponding values from columns A, B, and C in the same order, separated by single spaces, such as North Product 2024.
Can this formula return a value from another column?
Yes. Replace $D$2:$D$10 with the range containing the numbers or values you want to return, while keeping the three criteria ranges aligned to the same rows.
What happens if no three-column match is found?
SUMPRODUCT returns 0 when no row meets all three criteria. Check the spelling, spacing, data types, and lookup range if the result is unexpected.
