Forum Discussion

rosswelsh96's avatar
rosswelsh96
Copper Contributor
Dec 02, 2022

IFS #VALUE error

I have two dropdown lists that when selected should produce a specifc value. The first half of the IFS formula works but the second half give the #VALUE error. Can anyone advise on what I have done wrong with this formula. 

 

=IFS(SEARCH("HIGH",AB28)*AND(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),150,SEARCH("MEDIUM",AB28)*AND(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),100)

  • rosswelsh96 

    =IF(ISNUMBER(SEARCH("HIGH",AB28))*ISNUMBER(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),150,IF(ISNUMBER(SEARCH("MEDIUM",AB28))*ISNUMBER(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),100))

    The IF formula returns the expected result.

    With IFS it would be:

     

    =IFS(ISNUMBER(SEARCH("HIGH",AB28))*ISNUMBER(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),

    150,

    ISNUMBER(SEARCH("MEDIUM",AB28))*ISNUMBER(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),

    100)
     

Share

Resources