For the same Project_Id if the first cell (in ‘Success’) is 1, than make all ‘output’ cells for that matching project_id a 1….
And for the same Project_Id if the first cell (in ‘Success’) is 0, than make all ‘output’ cells for that matching project_id a 0….
I had previous used:
IF((COUNTIFS($A:$1:A1,A1,$B$1:B1,0)),0,1)
However this is not correct as it does the following: if there are ANY 0’s in the range, than make it 0.
i have also tried this: =IF(MIN(IF($A$2:$A$9=A2,$B$2:$B$9))=1,1,0)
however it does not work properly particularly in cases where it should give a 1... not a 0...
Would appreciate some help!!
| column A | column B | column C |
|---|---|---|
| Project_Id | Success | output |
| 1 | 1 | 1 |
| 1 | 0 | 1 |
| 1 | 0 | 1 |
| 2 | 0 | 0 |
| 2 | 1 | 0 |
| 2 | 1 | 0 |
| 2 | 1 | 0 |
| 2 | 1 | 0 |
| 2 | 1 | 0 |
CodePudding user response：
XLOOKUP() can give you desired output. Try-
=MAP(A2:A10,LAMBDA(x,XLOOKUP(x,A2:A10,B2:B10,"",0,1)))
Single XLOOKUP() will also work, like-
=XLOOKUP(A2:A10,A2:A10,B2:B10,"",0,1)
To make input argument dynamic can use A2:INDEX(A2:A50000,COUNTA(A2:A50000)). Try-
=XLOOKUP(A2:INDEX(A2:A50000,COUNTA(A2:A50000)),A2:A10,B2:B10,"",0,1)
I have used MAP() for dynamic spill results.
CodePudding user response：
Since all values would be available, alternatively use VLOOKUP():
Formula in C2:
=VLOOKUP(A2:A10,A2:B10,2,0)