Excel嵌套IF/AND函数查询问题（续）

数据老陈

2026-5-25

Excel Formula Solutions for Your Spreadsheet Logic

Hey James, let’s work through this spreadsheet issue properly—sounds like you thought it was fixed with Richard’s help, but it didn’t stick. Let’s break down each of your requirements with clear formulas and explanations:

When E cell equals "correct": You said you want to manually set F to match D. To make this easier to remember, add conditional formatting to column F: select column F, create a new rule using the formula =$E1="correct", and set a highlight color (like yellow) to flag that F needs updating to match D. If you actually want this to auto-fill instead of manual entry, use this formula in F1 and drag it down:
```
=IF(E1="correct", D1, "")
```
Just note this will overwrite any manual inputs unless you protect the cells later.
When E is "yes" and F is 111 (auto-fill G): Put this formula in G1 and drag down to apply to all rows:
```
=IF(AND(E1="yes", F1=111), C1, "")
```
This will pull the value from C into G only when both conditions are met; otherwise, G stays blank.
When E is "no" and F is not 111 (return 0): Use this formula in the relevant cell (adjust the column reference if needed) and drag down:
```
=IF(AND(E1="no", F1<>111), 0, "")
```
This returns 0 only when both conditions are true; the cell stays blank for all other cases.
When E is "yes" and F is 112 (H cell logic): You cut off this requirement, but here’s a flexible template based on your previous patterns. If you want H to pull a specific cell’s value (e.g., A1), use:
```
=IF(AND(E1="yes", F1=112), A1, "")
```
Swap out A1 for whatever value or cell reference you need H to display.