CODEWITHSUNDEEP
excelexcel-formula
Christopher Profil
Christopher Profil

Reputation: 11

Excel: Nested if statement is too long

I wrote this IF statement (IF=ALS), but it's too long.

Any idea what might be a solution?

=ALS(K2="Soul Shakers";"urban, tropical";ALS(K2="Dunya";"iets anders”;ALS(K2=“Rakka”;”urban, tropical, allround”;ALS(K2=“Ravenous”;”house, techno”;ALS(K2=“DJ Yolotanker”;”allround”;ALS(K2=“Black Mamba”;”urban, tropical”;ALS(K2=“Nico Morano”;”house, techno, underground”;ALS(K2=“Cedex & Higher Underground”;”drum & bass”;ALS(K2=“Leesa”;”house, techno, underground”;ALS(K2=“Station Earth”;”drum & bass”;ALS(K2=“MC Mota”;”drum & bass”;ALS(K2=“Mr_Fuzz”;”urban, tropical, future”;ALS(K2=“Polar Youth”;”urban, future”;ALS(K2=“Skyve”;”allround, urban, tropical, house, drum & bass”;ALS(K2=“Woodie Smalls”;”urban”;ALS(K2=“Jonas Lion;”house, techno, urban, tropical, underground”))))))))))))))))

Upvotes: 0

Views: 264

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

The problem with your formula is that you have entered it incorrectly. And the "real" error message would provide that clue. The real error message was probably something like there cannot be strings > 255 characters within the formula, and NOT that the nested IF statement is too long.

  • That was the clue that some of your double quotes " were really left double quotes “ or right double quotes ”.
  • Also, you are missing a double quote after your last ALS test condition K2="Jonas Lion,

That said, a lookup table, as proposed by @zipa, is a more efficient method than a deeply nested IF

Upvotes: 0

GalAbra
GalAbra

Reputation: 5148

The SWITCH function does exactly what you're looking for!

=SWITCH(K2, "Soul Shakers", "urban, tropical", "Dunya", "iets anders”, ...)
  • The first argument states what value you're comparing
  • Every couple of values (for example "Soul Shakers", "urban, tropical") states the possible value for the input and its corresponding output.
  • Optional: if the final argument is just one value (and not two), it represents the default output.

Upvotes: 0

zipa
zipa

Reputation: 27869

Create a lookup table like this one:

"Soul Shakers" "urban, tropical"
"Dunya"        "iets anders”
"Rakka"        "urban, tropical, allround"
...
"Jonas Lion"   "house, techno, urban, tropical, underground"

Now you can use the VLOOKUP() like this:

=VLOOKUP(K2;your_table_range;2;FALSE)

If I am correct you are using Dutch so VLOOKUP() will be VERT.ZOEKEN().

Upvotes: 3

Related Questions