Help preventing N/A errors

Hello,

The following array formula works perfectly:

Formula:

Please Login or Register  to view this content.

however, after the last row where the condition takes place, it just gives N/A errors from then on.
what can i do to the formula to give a blank cell instead of the N/A error? but everything else works the same.

also, "no bingo" is never displayed anyway. a FALSE is there instead. was wondering why this is. is there any need of it being in the formula above?


thank you.

Just encapsulate the whole expression with IFERROR, with a default output of "".

=IFERROR(IF(AND(ISNUMBER(MATCH(I2,{"Apple","Banana","Orange"},0)),MATCH("Happy",$I3:$I$240000,0)<MIN(IFERROR(MATCH({"Apple","Banana","Orange"},$I3:$I$240000,0),10^10))),IF(MATCH("Happy",$I3:$I$240000,0)>MIN(IFERROR(MATCH({"Expensive"},$AB3:$AB$240000,0),10^10)),"No Bingo","Yes Bingo")),"")

add an IFERROR as follows

=IFERROR(IF(AND(ISNUMBER(MATCH(I2,{"Apple","Banana","Orange"},0)),MATCH("Happy",$I3:$I$240000,0)<MIN(IFERROR(MATCH({"Apple","Banana","Orange"},$I3:$I$240000,0),10^10))),IF(MATCH("Happy",$I3:$I$240000,0)>MIN(IFERROR(MATCH({"Expensive"},$AB3:$AB$240000,0),10^10)),"No Bingo","Yes Bingo")),"")

if you never see the No Bingo and getting a false - then the IF statement is missing an element and giving a FALSE

i will decode as best i can and reply later

so in your TEST

you are doing this for the TEST
AND(ISNUMBER(MATCH(I2,{"Apple","Banana","Orange"},0)) ,MATCH("Happy",$I3:$I$240000,0)<MIN(IFERROR(MATCH({"Apple","Banana","Orange"},$I3:$I$240000,0),10^10))),

Then IF TRUE
you are doing this
IF(MATCH("Happy",$I3:$I$240000,0)>MIN(IFERROR(MATCH({"Expensive"},$AB3:$AB$240000,0),10^10)),"No Bingo","Yes Bingo"))

SO you have to have a TRUE for the first condition
and then a TRUE for the 2nd condition to get "No Bingo"

TRUE for 1st condition
FALSE for 2nd condition will give "yes Bingo"

FALSE for 1st condition - you dont have anything
So the IF produces a FALSE

SO in order to do that - we need to know the conditions and the results

otherwise
you probably need
=IFERROR(IF(AND(ISNUMBER(MATCH(I2,{"Apple","Banana","Orange"},0)),MATCH("Happy",$I3:$I$240000,0)<MIN(IFERROR(MATCH({"Apple","Banana","Orange"},$I3:$I$240000,0),10^10))),IF(MATCH("Happy",$I3:$I$240000,0)>MIN(IFERROR(MATCH({"Expensive"},$AB3:$AB$240000,0),10^10)),"No Bingo","Yes Bingo"),"no bingo"),"")

your welcome , thanks for the rep