Function Problems

Return a Value Based on Which Cell Contains 1 in WPS Spreadsheets

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

Question details

Return a specific number based on which of four cells contains the value 1.

Return a Value Based on Which Cell Contains 1 in WPS Spreadsheets
Product
Spreadsheet software, including WPS Spreadsheets and Microsoft Excel
Device & OS
not provided
Scenario
Only one cell in D2:G2 contains 1, and the result depends on its position.
Observed behavior
The desired result is 3 for D2 or G2, 7.5 for E2, 6 for F2, and 0 if none contains 1.
Before you start

Check that D2:G2 are the cells you want to evaluate and that any 1 is entered as a numeric value. Choose an empty cell outside that range for the formula.

Solution 1Recommended

Use IF and OR to return the matching result

Use this formula when the result depends on which cell in D2:G2 contains 1.

OR checks D2 and G2 together because both return 3. The nested IF functions then check E2 and F2, returning 0 if none of the conditions is met.

1
Select an output cell

In WPS Spreadsheets or Excel, click an empty cell outside D2:G2, such as H2.

2
Enter the formula

Type =IF(OR(D2=1,G2=1),3,IF(E2=1,7.5,IF(F2=1,6,0))) in the selected cell or formula bar, then press Enter.

3
Check the result

Enter 1 in one of D2:G2 and confirm that H2 shows the corresponding result. Clear the 1 to check that the formula returns 0.

Use IF and OR to return the matching result
If more than one cell contains 1: The formula checks D2 and G2 first, then E2, then F2. It returns the result for the first matching condition.
WPS Spreadsheets formula

Calculate the Result in WPS Spreadsheets

Enter the IF and OR formula in WPS Spreadsheets to return the right value for the cell containing 1.

  1. 1. Open your worksheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Select the result cell: Click an empty cell outside D2:G2, such as H2.
  3. 3. Enter the formula: Type =IF(OR(D2=1,G2=1),3,IF(E2=1,7.5,IF(F2=1,6,0))) and press Enter.
  4. 4. Save the workbook: Use File > Save or Save As to save the worksheet in your chosen format.
Return 3, 7.5, 6, or 0 from one formulaOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet functions and a formula bar
microsoft office alternative - wps office

Frequently Asked Questions

What does the formula return if none of the four cells contains 1?

It returns 0. The final argument of the nested IF formula supplies that result when no condition matches.

What happens if more than one cell contains 1?

The first matching condition wins: D2 or G2 takes priority, followed by E2, then F2.

Can I use the formula in Excel?

Yes. Enter the same IF and OR formula in an empty Excel cell, such as H2.

How can I use the formula for other rows?

Select the formula cell and drag its fill handle down. The relative references, such as D2 and G2, will adjust for each row.