Can I use a List in a IF Function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to be able to have a IF function that says =IF(B77=Tile,Grout,"N/A") .

Tile - being a choice from a drop down list.
Grout - being a list of colors if the choose Tile.
"N/A" - if they do not choose Tile from the list.

Is this possible?
 
Hi

I'm not sure about "N/A" - is it Excel's returned error, or simply an user
entry. But in general, the next formula will do what you do want
=IF(SUMPRODUCT(--(B77={"Tile";"Grout";"Whatever"}))>0;"Is in list";"Is
missing from list")


Arvi Laanemets
 
=IF(SUMPRODUCT(--(A4={"Tile";"Grout";"N/A"})),"Present","Not Present")

this formula will not report the error #N/A! as present.

KL
 
Hi again

It looks like I stumbled on some Excel odditie here. After I sent my answer,
I noticed that probably the formula I provided may be simplified. I tried
=IF(SUMPRODUCT((B77={"Tile","Grout","Whatever"})),"Is in list","Is missing
from list")
and discovered that it wasn't working - the check was always FALSE ?
BUT, then I tried
=IF(SUMPRODUCT(--(B77={"Tile","Grout","Whatever"})),"Is in list","Is missing
from list")
and the formula worked !!!???

PS. In my previous answer, probably function delimiters must be changed
(commas instead semicolons).


Arvi Laanemets
 
Arvi,

It is not an odditie. The thing is that B77={"Tile","Grout","Whatever"}
returns TRUE/FALSE which can't be digested by SUMPRODUCT unless it is
explicitly coerced to 1/0, which is why u are using "--"

Regards,
KL
 
Back
Top