An Alternative to Nested IF’s

Warning: It takes longer to explain the problem than it does to explain the solution.

One of the common issues raised on the Smartsheet Community is problems dealing with NESTED IF’s.

NESTED IF are a series of IF statements that are chained (nested) together. These are allowed and occur because IF statements usually have a syntax like this:

IF (something to check,  check is true so do this, check is false so do that)

but sometimes for one for both possibilities, there are further refinements. In some programming languages, these becomes IF-THEN-ELSE IF statements. In Smartsheet, we nest them.

=IF([ColumnA]23=1,[ColumnC]23,IF([ColumnA]23=2,[ColumnD]23,IF(etc...)

Confused? Once you have struggled with them a few times (or more for people like me), they become second nature. But they aren’t easy at first, with a misplaced comma or end parentheses tossing off an error that leaves one scrambling for comfort food.

 An Example

Here’s an example.

Based on the value in [My Level] column, I want to get an appropriate value from the columns [Level 1] to [Level 5].

A common solution to this requirement is to used NESTED IF’s.

Note: The rose highlighted cells are conditional formatting showing which levels have been accomplished. The “next” will always be without highlight.

An Alternative

The solution is surprisingly easy. This is a partially because the value that is being used to determine which value I want is a number. I can use that number as the column I want to get from the table. I have a solution for non-numbers, but will save it for another post.

The solution uses the LOOKUP function.

A short digression on LOOKUP

One of the technical obstacles I had to overcome is that Smartsheet’s LOOKUP function corresponds to other  spreadsheet programs (MS Excel and OpenOffice are the ones I use) VLOOKUP function. LOOKUP expects a table with the criteria being searched located in the left most column. It may at first glance seem like an odd choice too. I’m not looking to match anything in the table, but I do know which column what I want is in.

The Solution

For Row 3, the formula is:

=LOOKUP([My Level]3, [My Level]3:[Level 5]3, [My Level]3 + 2)

That’s it.

Would you like me to explain? (I always hear Marisa Tomei when I hear that phrase)

I’m tricking LOOKUP. The [My Level] column contains both the value I am looking for

=LOOKUP([My Level]3, [My Level]3:[Level 5]3, [My Level]3 + 2)

and is the first column in the ‘table’ I am searching

=LOOKUP([My Level]3, [My Level]3:[Level 5]3, [My Level]3 + 2)

and is my clue to which column I want

=LOOKUP([My Level]3, [My Level]3:[Level 5]3, [My Level]3 + 2)

I’m guaranteed to get a match in the table (which is just a row)

Because the value I am looking for is 0 based and the columns I care to retrieve are columns 2-6 of the ‘table’, I add 2.

=LOOKUP([My Level]3, [My Level]3:[Level 5]3, [My Level]3 + 2)

For reference, the matching NESTED IF looks like this:

=IF([My Level]3 = 0, [Level 1]3, IF([My Level]3 = 1, [Level 2]3, IF([My Level]3 = 2, [Level 3]3, IF([My Level]3 = 3, [Level 4]3, IF([My Level]3 = 4, [Level 5]3, "Unknown")))))

or formatted for legibility:

=IF([My Level]3 = 0, [Level 1]3,

IF([My Level]3 = 1, [Level 2]3,

IF([My Level]3 = 2, [Level 3]3,

IF([My Level]3 = 3, [Level 4]3,

IF([My Level]3 = 4, [Level 5]3, “Unknown”)))))

Note that the LOOKUP function can be expanded to 6, 16, or even more columns – without changing the formula at all.

(Bonus Tip – add new columns between the first and the last columns instead of to the right of the last columns)

Final Thoughts

This is part of a series of posts on Smartsheet’s LOOKUP. Watch for more as I get the time and energy.

If you like this post, please “Like” it. Every time my post gets liked, a fairy gets her wings. Or I’m more likely to post another one. It is one of those two, I’m sure.

If you are new to Smartsheet and want to check it out, click here. If you have questions about this post, add them below. If you have questions about something else in Smartsheet, post on the Community. I occasionally stop by to answer them and so do a lot of other talented people.