I am trying to look if a cell value in second sheet is not equal to blank and then check the value of other cell is lesser than 3 and further update the value to 1 or 0.
1st condition if g2 cell value present in second sheet is not equal to blank then check for f5 cell value. If the f5 cell value is less than 3 than it should display as 1 else 0
=sheet2!NOT(ISBLANK($G2)) + IF($F5>2, 0, 1)
Best How To :
First, your formula
=sheet2!NOT(ISBLANK($G2)) + IF($F5>2, 0, 1) is not using proper Excel syntax. The sheet reference goes with the cell reference, not outside the general formula. Also, your formula checks if
F5 is greater than 2, not less than 3.
There are 2 conditions : 1st to check the cell value in sheet2 is not blank.
NOT(ISBLANK(Sheet2!$G2)) checks this.
2nd cond to check if the cell value f5 in sheet2 is less than 3
The logical condition
$F5 < 3 checks this.
than put value as 1 else 0 in sheet1 cell.
IF(condition(s), 1, 0)
so both the conditions should get satisfy.
Now you want to check that both conditions are met, so you need an
AND() function in your
IF() function's logical test argument:
=IF(AND(), 1, 0)
Or to put it together:
IF(AND(NOT(ISBLANK(sheet2!$G2)), $F5 < 3), 1, 0)