#N/A Error w/LOOKUP Function

[sony654]

The below function is producing exactly the result I want when I have a
value entered in the LOOKUP range. But, if no values entered in the LOOKUP
range t returns the #N/A error. How do I edit this function result to
display "-", or blank if a value is not entered in the LOOKUP range? Thanks
for your help. Sony

=LOOKUP(BigNum,CHOOSE(MATCH(TRUE,COUNTIF(INDIRECT({"b122:af122","b116:af116","b110:af110","b104:af104","b98:af98","b92:af92","b86:af86","b80:af80","b74:af74","b68:af68","b62:af62","b56:af56"}),"<>")>0,0),$B$122:$AF$122,$B$116:$AF$116,$B$110:$AF$110,$B$104:$AF$104,$B$98:$AF$98,$B$92:$AF$92,$B$86:$AF$86,$B$80:$AF$80,$B$74:$AF$74,$B$68:$AF$68,$B$62:$AF$62,$B$56:$AF$56))

 

[T. Valko]

I would use another cell with this formula:

Assume this formula is in cell A1:

=COUNT($B$122:$AF$122,$B$116:$AF$116,$B$110:$AF$110,$B$104:$AF$104,
$B$98:$AF$98,$B$92:$AF$92,$B$86:$AF$86,$B$80:$AF$80,
$B$74:$AF$74,$B$68:$AF$68,$B$62:$AF$62,$B$56:$AF$56)

Then refer to that cell:

=IF(A1=0,"-",your_formula)

 

[muddan madhu]

if u are getting error #NA, then use =if(isna(formula),"-",formula)

 

[Peo Sjoblom]

Did you try that with this particular formula?


--


Regards,


Peo Sjoblom



if u are getting error #NA, then use =if(isna(formula),"-",formula)

 

[sony654]

Biff, Thanks for your solution to qualifying b56. You have been very
helpful, now a couple times. I would like to qualify b56:af56, not just b56.
Changed my mind. The formula below returns an error. Can you please advise
how to fix? Thanks


=if($b$56:$af$56=0,""),COUNT($B$122:$AF$122,$B$116:$AF$116,$B$110:$AF$110,$B$104:$AF$104,

$B$98:$AF$98,$B$92:$AF$92,$B$86:$AF$86,$B$80:$AF$80,
$B$74:$AF$74,$B$68:$AF$68,$B$62:$AF$62,$B$56:$AF$56))

 

[Peo Sjoblom]

So you want to check if all 31 cells from B56 to AF56 are 0


=IF(COUNTIF($B$56:$AF$56,0)=COLUMNS($B$56:$AF$56),"",COUNT($B$122:$AF$122,$B$116:$AF$116,$B$110:$AF$110,$B$104:$AF$104,$B$98:$AF$98,$B$92:$AF$92,$B$86:$AF$86,$B$80:$AF$80,$B$74:$AF$74,$B$68:$AF$68,$B$62:$AF$62,$B$56:$AF$56))


--


Regards,


Peo Sjoblom

 

[T. Valko]

Or, you could use the slightly shorter array formula** :

=IF(AND($B$56:$AF$56=0),"",COUNT(.....))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)