If followed by If(AND not working

[born2dive00]

=IF(D3="",(L26),(D3))
=IF(AND(D3="",(L26=0)),(J17))

Please help I can get these 2 statements to work independently but like petulant children wont work together.

I am trying to get the formula to work like this.
If D3="" look at L26. If D3="" AND L26=0, look at J17 else do nothing.

Please let me know.
I have tried =IF(D3="",(L26),(IF(AND(D3="",(L26=0)),(J17),""))) but it always comes back as 0 regardless if there is a value in L26 or J17

Thanks
Brad

[p45cal]

=if(d3="",if(l26=0,j17,l26),d3)
?

[born2dive00]

Cheers P45 cal that works! Ive been fighting with that for over 2 hours.

[born2dive00]

Hey P45 cal ran into a problem with your formula,

It has to return a value if not zero

so if A1=0 look at A2, if a2 has a value return that value, if a1 and A2 are blank look at A3 and return that value, if A1, A2, A3 look at A4 and return that value or if all zero do nothing.

[Pepe Le Mokko]

Perhaps

Please Login or Register  to view this content.

[p45cal]

try:
=INDEX(A1:A22,MATCH(TRUE,$A$1:$A$22<>"",0))
or:
=INDEX(A1:A22,MATCH(FALSE,ISBLANK($A$1:$A$22),0))

Depending on your version of Excel you may have to commit one of these formulae to the sheet using Ctrl+Shift+Enter rather than the more usual plain Enter.

You can also wrap the whole fornula in IFERROR if all are blank:
=IFERROR(INDEX(A1:A22,MATCH(TRUE,$A$1:$A$22<>"",0)),"")

Not sure if the cells are blank or all contain numbers, so it may be:
=IFERROR(INDEX(A1:A22,MATCH(TRUE,$A$1:$A$22<>0,0)),"")
It really all depends on what kinds of data are in column A.

[p45cal]

If your ranges are not contiguous as in your original message, you can do something like:
=CHOOSE(MATCH(FALSE,CHOOSE({1,2,3},D3,L26,J17)=0,0),D3,L26,J17)
and put IFERROR around that if you want.

ps. Do all the testing without IFERROR, making sure you get the right answers and as a final step add the IFERROR.