Multiple IF Statements and Conditional Lookups

Amy Evans2

Hello,

In this example below, I have one sheet.

Question1: I'm not sure if it is best to move Rows 10-14 to another sheet or to a different part of the sheet to utilize column heading names.

Question 2 IF Statements:

Jan Cost Column: What is the Cost (Cost A or Cost B) for each team member based on their status indicator (A or B) and the Jan Points value that fall between Min/Max points?

For example, John Smith has "A" status and has 4 points. John's cost is $100 for Jan Cost column. David should be $250.

Thanks,

Amy

Amy Evans2

Here is what I'd like to show in another view:

IF Team Member is a contractor..

AND [Jan Pts] is between Min Max Points (1 - 5), then show Cost-A $100...or between Min/Max Points (6 - 13), then show Cost A $200..or between Min/Max Points (14-20), then show Cost A $300...or between Min/Max points 21-55, then show Cost A $400

ELSE

[Jan Pts] is between Min Max Points (1 - 5), then show Cost-B $150...or between Min/Max Points (6 - 13), then show Cost A $250..or between Min/Max Points (14-20), then show Cost A $350...or between Min/Max points 21-55, then show Cost A $450

Maybe there is a more elegant way to structure this? Or, is this the right syntax approach?

Thanks,

Amy

Amy Evans2

Here's my attempt for Team Members who are "A" indicators. I know the columns are a bit confusing since my reference table is starting on Row 10.

=IF(([Jan Pts]@row < [Jan Pts]$12), [Feb Pts]$11, IF([Jan Pts]@row < [Jan Pts]$13, [Feb Pts]$12, IF([Jan Pts]@row < [Jan Pts]$14, [Feb Pts]$13, IF([Jan Pts]@row > [Jan Cost]$13, [Feb Pts]$14, 0))))

Questions:

-How do I address a "0" in Jan Pts for John Smith (Instead of "4") will show a $0 Jan Cost?

Thank you,

Amy

Genevieve P.

Hi Amy,

You're definitely on the right track! I like that you're only looking to see if the number is less than the max number, this means your formula doesn't have to be as long because the order of operations can eliminate a number of references needed, so that's great.

You have the instruction correct (there are two extra parentheses that can be removed), but you're missing out the first instruction to see if it's A or B. Before we jump into that expression though, let's add in that statement about 0, since that's true no matter if it's A or B.

Try This:

=IF([Jan Pts]@row = 0, 0, IF([Jan Pts]@row < [Jan Pts]$12, IF(Status@row = "A", [Feb Pts]$11, [Feb Cost]$11), IF([Jan Pts]@row < [Jan Pts]$13, IF(Status@row = "A", [Feb Pts]$12, [Feb Cost]$12), IF([Jan Pts]@row < [Jan Pts]$14, IF(Status@row = "A", [Feb Pts]$13, [Feb Cost]$13), IF([Jan Pts]@row > [Jan Cost]$13, IF(Status@row = "A", [Feb Pts]$14, [Feb Cost]$14), 0)))))

You'll notice that my "true" statement is in itself an IF statement:

IF(Status@row = "A", [Feb Pts]$13, [Feb Cost]$13)

So if the status is A, return from the Feb Pts column. If it's not A, return from the Feb Cost column. This is a closed off IF statement because it's the answer then to your initial IF statement, which is looking for the number between two numbers.

Let me know if this works!

Cheers,

Genevieve

Paul Newcome

@Genevieve P Love the solution!!

Here is a second option. This is one I have used in the past for something similar...

=INDEX($[Feb Pts]$11:$[Feb Cost]$14, MATCH(MIN(COLLECT($[Jan Cost]$11:$[Jan Cost]$14, $[Jan Cost]$11:$[Jan Cost]$14, >= [Jan Pts]@row)), $[Jan Cost]$11:$[Jan Cost]$14, 0), IF($Status@row = "A", 1, 2))

Breakdown:

We use the INDEX function to pull from $[Feb Pts]$11:$[Feb Cost]$14.

To determine the row number, we use a MIN/COLLECT to pull the lowest number that is greater than or equal to our number in [Jan Pts]@row from the "Max Point" range.

Using a MATCH function to locate this number within that same "Max Points" range will give us which row we need to pull from.

Then we use an IF statement to say that if the Status@row is "A" then pull from the first column, otherwise the second column.

The IF solution is easier for trouble shooting because everything is spelled out. If this is true, then do this. Breaking it down into sections makes it pretty clear what is going to happen if  [Jan Pts]@row < [Jan Pts]$12.

The only challenge with that solution would be if you decided to expand your table to include additional rows and/or columns.

Adding rows would just be repeating the pattern of

IF([Jan Pts]@row < [Jan Pts]$12, IF(Status@row = "A", [Feb Pts]$11, [Feb Cost]$11)

Adding columns would mean adjusting that second IF into a nested IF in and of itself and then repeating that for each "row section".

Using the INDEX solution is not quite as easy to trouble shoot if you start pulling the wrong numbers, but it does scale a little easier.

To add new rows to your table, just adjust the row numbers to cover the additions.

To add new columns it would be the same thing of using a nested IF, but you would only have to change it in one place within the formula.

Long story short...

Genevieve's solution is easier to troubleshoot and will work wonderfully on a smaller scale.

The INDEX solution is harder to troubleshoot, but is easier to expand.

Just a little something to keep in mind if you ever need something similar but have many more rows or columns.

Amy Evans2

Thank you both for great solutions. I'll probably have to read these a few time to digest everything here, but appreciate the wealth of knowledge you bring to the table!

And, appreciate the background and explanation. Really helps to hear the "WHY" behind the statement syntax and how it can scale in the future. Priceless feedback here.

You guys are AMAZING!

Amy

Paul Newcome

Happy to help! 👍️

I also wanted to note that the scaling alternative I provided can actually be tweaked a little further to scale even better if you were needing to pull form numerous columns. Using the IF statement is fine for 2 or even 3 columns, but after that we may want to consider replacing the IF with a second MATCH. It all depends on the size (and shape) of the table that you are referencing when we start talking larger scales.

For the smaller scale with only 4 rows and 2 columns, I do suggest @Genevieve P's solution because of it being more clear exactly what is happening and how with a formula that isn't too monstrous.