Determine FIRST ROW in Range based on Criteria

[JayB0730]

Hello,

I am trying to come up with a formula that returns the specific ROW in a range where either Column A = "FALSE" or ISBLANK AND Column B = ISBLANK.
This new formula would appear in C1.

GOAL - This formula will help me determine the first row in the range that needs attention. If there are subsequent rows, the formula would automatically update after said row is fixed.

TIA

-J

 

[arthurbr]

When you say ISBLANK do you mean an empty cell or/and cells containing the null text string ("")?

 

[BrianGGG]

Assuming that it's really a blank string, I think the following could work:

	A	B	C	D
x	Answer:
x

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]36[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: right"]38[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D36[/TH]
[TD="align: left"]=AGGREGATE(15,6,ROW(A36:A41)/IF((((A36:A41=FALSE)+(ISBLANK(A36:A41)))*(ISBLANK(B36:B41)))>0,1,0),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

In the formula:
Boolean logic (plus and multiply) is used to determine the rows that meet the criteria
The rest are errors due to divide by zero
The AGGREGATE function is used to pick the SMALLest number that is not an error

 

[AlphaFrog]

Assuming ISBLANK means null string "", try this.

Confirm with Ctrl+Shift+Enter
=MATCH(1,1/(((A1:A1000=FALSE)+(A1:A1000=""))*(B1:B1000="")),0)

 

[BrianGGG]

I like AlphaFrog's better than mine...much more compact. The only thing mine has going for it is that it's not an array formula...

 

[JayB0730]

Assuming ISBLANK means null string "", try this.

Confirm with Ctrl+Shift+Enter
=MATCH(1,1/(((A1:A1000=FALSE)+(A1:A1000=""))*(B1:B1000="")),0)

Worked like a charm! And yes it was "" not ISBLANK, my apologies. Thank you!