very long if statement

[gjadcock]

Is there any other way to enter this formula

=IF(D6="common","invalid",IF(D6="vert","invalid",IF
(D6="core","invalid",IF(D6="mtg","invalid",IF
(D6="vacant","invalid","bus_unit")))))

I have around 30 room types (common, vert, etc) some are invalid and some are valid, the list will keep growing and I am running out of levels to nest the IF statement.

Thanks

 

[jimboy]

how about;

=IF(D6<>"Valid","invalid","Valid")

 

[gjadcock]

Thanks, but I need to test the value in the cell if its common, vert etc then I need to set Invalid, else set to Valid

 

[Dave Patton]

Your if statement show "bus_unit" for blank D6 but your text implies various choices.

You should check help on VLookup or Lookup.

You can also consider

=IF(OR(D6={"common","vert","core","mtg","vacant"}),"invalid","bus_unit")

=LOOKUP(D6&"",{"","Common","Core","Mtg","Vacant";"Bus_unit","Invalid","Invalid","Invalid","Invalid"})

With a lookup table that I named rRooms

=VLOOKUP(D6,rRooms,2,0)

If you want the comment "bus_unit"if D6 is blank

=IF(D6="","bus_unit",VLOOKUP(D6,rRooms,2,0))
This message was edited by Dave Patton on 2002-09-18 09:11

 

[Scott R]

Instead of embedding your list of valid entries in the formula, put them in a column in the worksheet, i.e. if D6 doesn't match an entry from the list in A1 to A10 then "invalid", else "valid".

Enter this in B1 or whereever:
=IF(ISNA(MATCH(D6,A1:A10,0)),"invalid","valid")