Complicated Nested If Statement

[cmschmitz24]

Hello,

I need help writing a nested if statement:

I need to pull a value into cell A53 on sheet "SL Estimate" from sheet "Year 1 Rates" based on drop down selections in 5 different cells on sheet "SL Estimate".

Drop down cells used:
Cell E8, named "Cov_Level", has either Single-1 or Family-15
Cell F8, named "Health_Plan", has a list of 18 plans located on sheet "Year 1 Rates" A5:A22 (this will be the starting point to find the value)
Cell I8, named "HDHP", has either non-HDHP or HDHP
Cell J8, named "Dental", has either With Dental or Without Dental
Cell K8, named "Rate", has either Full Time or Less Than Half Time

On sheet "Year 1 Rates", there are the different rate values for each situation above, in columns B:AW

Full Time, non-HDHP, with Dental, single, values are in column B
Full Time, non-HDHP, with Dental, family, values are in column E
Full Time, non-HDHP, without Dental, single, values are in column H
Full Time, non-HDHP, without Dental, family, values are in column K
Full Time, HDHP, with Dental, single, values are in column N
Full Time, HDHP, with Dental, family, values are in column Q
Full Time, HDHP, without Dental, single, values are in column T
Full Time, HDHP, without Dental, family, values are in column W

Less Than Half Time, non-HDHP, with Dental, single, values are in column Z
Less Than Half Time, non-HDHP, with Dental, family, values are in column AC
Less Than Half Time, non-HDHP, without Dental, single, values are in column AF
Less Than Half Time, non-HDHP, without Dental, family, values are in column AI
Less Than Half Time, HDHP, with Dental, single, values are in column AL
Less Than Half Time, HDHP, with Dental, family, values are in column AO
Less Than Half Time, HDHP, without Dental, single, values are in column AR
Less Than Half Time, HDHP, without Dental, family, values are in column AU


Please help!

I only have =VLOOKUP(Health_Plan, 'Year 1 Rates'!A:AW,
I know I have to start my nested if statements but I'm not quite sure how to finish/complete it.

 

[Eric W]

Re: Complicated Nested If Statement - Help Needed!

Your columns are nicely organized in a binary format. Which may or not mean anything to you. But the point is that all you really need to do is calculate what column to use for the VLOOKUP. Try this formula:

=VLOOKUP(Health_Plan,'Year 1 Rates'!A5:AW22,((Cov_Level="Family-15")+(Dental="Without Dental")*2+(HDHP="HDHP")*4+(Rate="Less Than Half Time")*8)*3+2,0)

The part in red calculates the column based on the values in your drop downs. Let us know if this works for you.

 

[cmschmitz24]

Re: Complicated Nested If Statement - Help Needed!

Thanks, Eric!
Everything works for the most part except the Cov_Level. It appears that you are *'s something for each drop down cell. Would that be the cause?

 

[cmschmitz24]

Re: Complicated Nested If Statement - Help Needed!

I'm following the format more and more now that I look at it, so I'm not sure that the *'s after each drop down is a cause, however the change from single to family does not populate correctly.

 

[Zanmato]

Re: Complicated Nested If Statement - Help Needed!

I cannot even begin to understand Eric's solution, however I hope for your sake he's on to a winner because, to amuse myself, I recreated your sheets as best as I could from your description and put this together. Maybe if you can't get Eric's method to work fully, you can use it to build on. The length of it is pretty extreme when you consider it only covers 2 of the possible combinations. Figuring out where the brackets had to go was tricky.

I am a novice btw so this might be a really inefficient way of doing nested IFs.

=VLOOKUP(Health_Plan,'Year 1 Rates'!A:AU,(IF(AND(Cov_Level="Single-1",HDHP="non-HDHP",Dental="With Dental",Rate="Full Time"),2,(IF(AND(Cov_Level="Family-15",HDHP="non-HDHP",Dental="With Dental",Rate="Full Time"),5,)))),FALSE)

EDIT: overuse of a word

 

[ask2tsp]

Hello,
instead of writing a 4 levels nested if statement consider the following approach:

1. for every entered option set a number to 0 ( 1st option ) or 1 ( 2nd option ) say n1, n2, n3 and n4
2. calculate 8*n1+4*n2+2*n3+n4 --> caseNr
3. columnNr=2+3*caseNr
4. A53=indirect(adres(1,columnNr))

 

[cmschmitz24]

Thanks, Zanmato, I appreciate your input - unfortunately, your code did not work.

ask2tsp - where do I write the code? I'm familiar with Macros but not sure how to put that into a sheet that won't use a macro

 

[Eric W]

Re: Complicated Nested If Statement - Help Needed!

Each category has a *x after it, except the family one, which should be *1, but since multiplying by 1 doesn't change it, it's not needed.

It's possible I got the value reversed for the family category. Are you getting the family value instead of the single value, and vice versa? If so, just change Family-15 to Single-1 in the formula.

Also, I was thinking that this formula should work, but it's very dependent on how the Health_Plan sheet is set up. If you ever add or delete a column, it won't work. Which is also the case if you use a large nested IF. If you ever anticipate changes, or just for a possibly easier formula, you might want to consider some kind of a matching formula. I notice your data starts in row 5. If you have headers above each column for the categories, we could do a MATCH. For example, if on the Health_Plan sheet B1 = "Single-1, B2 = "non-HDHP", B3 = "With Dental", and B4 = "Full Time", and all the other columns are labelled similarly, then the formula could be:

=VLOOKUP(Health_Plan,'Year 1 Rates'!A5:AW22,SUMPRODUCT(COLUMN('Year 1 Rates'!A1:AW1)*('Year 1 Rates'!A1:AW1=Cov_Level)*('Year 1 Rates'!A2:AW2=HDHP)*('Year 1 Rates'!A3:AW3=Dental)*('Year 1 Rates'!A4:AW4=Rate)),0)

 

[cmschmitz24]

Re: Complicated Nested If Statement - Help Needed!

I found what was causing the issue it was spacing in the text which wasn't the same in the code. Your code works fantastically! Thank you so much!

 

[Eric W]

Re: Complicated Nested If Statement - Help Needed!

I should have thought of that!

Glad you got it all working!