How to use if cycle ... getting #INVALID OPERATION message

Nuribe

Hi,

I'm trying to use a formula:

if workcomplete is less then 100% then look for "process engineer" where Health matches "On Time", so formula would be the following:

 

=IF(([% Work Complete]4:[% Work Complete]245 < 1), LOOKUP(Health4:Health245, "Process Engineering", Health4:Health245, "On Time"))

 

I keep getting #INVALID OPERATION message, any ideas?

Thanks

 

Mike Wilday

It might be because your lookup option doesn't quite match the formatting necessary for performing the operation. Test your lookup formula by using it separately in a new column and see if you get the same error. It doesn't seem to be set up correctly. 

See here for more information on the lookup formula: https://help.smartsheet.com/function/lookup

J. Craig Williams

What exactly are you trying to do?

Based on this post:

https://community.smartsheet.com/discussion/countifs-various-values

you wanted to count them.

=COUNTIFS([% Work Complete]4:[% Work Complete]245, <1, Department4:Department245, "Process Engineering", Health4:Health245, "On Time")

Your new attempt using IF and LOOKUP does not make sense to me.

Craig