Data validation with LOOKUP condition not working for empty cell

[tfdd]

Hello,

I want to validate the cell F3 entry if the following condition is met: OR(AND(F3="";I10>0);LOOKUP(F3;D7:F11;3;0)>0)

Meaning that if the cell F3 is empty, it will check if I10>0, if not, it will search for its correspondance in the area from D7 to F11 and check if it is bigger than 0

If I only use this formula: AND(F3="";I10>0) and the cell F3 is empty it validates the entry, since I 10 is bigger than 0

But if I use the formula OR(AND(F3="";I10>0);LOOKUP(F3;D7:F11;3;0)>0)

It only works if the cell F3 is not empty, if it is empty, it doesn't work, since it doesn't validate the entry as it was supposed to.

I think it has to do with the fact that when the cell F3 is empty, it uses the formula LOOKUP and it shouldn't, since it´s not applicable in this condition and so it doesn't validate the entry based on an error in the formula.

How to solve this in order to produce the correct output?

Thanks.

 

[jasonb75]

Not sure if I'm following what you want correctly, try

AND(I10>0;IFNA(LOOKUP(F3;D7:F11;3;0)>0;FALSE))

 

[tfdd]

F3;D7:F11;3;0

That formula is not working in data validation rule.

The goal is to only allow data to be entered into cell F3 if the result of the following formula is >0

IF.ERROR(LOOKUP(F3;D7:F11;3;0);I10)

If cell F3 is empty, it will check if I10 >0, if not empty, it will check if the coresponding value from the area B4:C6 is >0.

I don't know how to create a rule for this, since when the F3 is empty, the lookup is also used and creates an error.

 

[tfdd]

Already found the solution to put in the data validation rule:

=ifna(lookup(f3;d7:f11;3;0)>0;i10>0)