Struggling with conditions of IF statements

[heathermchle]

Hi, I'm working on an interactive spread sheet to use with the teachers that I support and I'm trying to find a way to make the cells in one column display certain text based on the conditions in another. I've literally spent the equivalent of two work days, it feels like, playing with formulas, trying different things. I KNOW there is a way but I just can't figure it out! Spreadsheet is below:

The user will input data from B8 and G8 ( I will likely move G next to B so the rest of the table populates from there.

In Column H, starting at row 8, I need an IF statement that plays on the conditions in row G8. So...

If G8 is in between I1 and G3, the text says Fall.
If G8 is greater than G3 but less than G4, it says Winter.
If G8 is greater than G4, but less than G5, it says Spring.

I1, and G3:G5 are fixed cells.

Column K works and calculates correctly based on this text (from a method I had previously tried, I decided to leave it there since I hadn't broken it yet!)

Once the text shows up in Column H correctly, I will conditionally format it to 3 different colors.

I just can't figure out how to get the date conditions to show properly. I've tried =IF( and also =IF(AND... all kinds of ways.

Someone please help!

 

[heathermchle]

I noticed a few typos in my version, but I see that you are now using a different criteria. You originally said to use the Cut-Off dates for determining the Fall/Winter/Spring terms, but your latest formula shows that you are using the Start and End dates. Which do you want? The LOOKUP formula used like that will return the last value array position satisfying the conditional criteria...and it would look like this if you want to use Start & End dates rather than the cutoff dates.

=LOOKUP(2,1/(($E8>=$C$3:$C$5)*($E8<=$D$3:$D$5)),$B$3:$B$5)

I don't know really know WHICH ones I want, I just keep trying them all in different variations until something works. I really do appreciate you looking a look and helping out.

I've been working on it all day so I've been trying everything! I was following another commenters lead on the LOOKUP function and that was as far as I got. The formula that you gave me pulled up an NA BUT.... with what I have been working on, I've been able to figure it out so far until I get a #DIV/0! error because an inputted start date exceeds the start dates in my tables. I don't really know what to do next to include that information, but otherwise, it works!

Here's the update:

 

[heathermchle]

I noticed a few typos in my version, but I see that you are now using a different criteria. You originally said to use the Cut-Off dates for determining the Fall/Winter/Spring terms, but your latest formula shows that you are using the Start and End dates. Which do you want? The LOOKUP formula used like that will return the last value array position satisfying the conditional criteria...and it would look like this if you want to use Start & End dates rather than the cutoff dates.

=LOOKUP(2,1/(($E8>=$C$3:$C$5)*($E8<=$D$3:$D$5)),$B$3:$B$5)

Just kidding! I realized some of the cell references were off and I fixed them and now it works with the formula that you gave me! OMG. You saved my day and my sanity. Thank you!

 

[heathermchle]

I noticed a few typos in my version, but I see that you are now using a different criteria. You originally said to use the Cut-Off dates for determining the Fall/Winter/Spring terms, but your latest formula shows that you are using the Start and End dates. Which do you want? The LOOKUP formula used like that will return the last value array position satisfying the conditional criteria...and it would look like this if you want to use Start & End dates rather than the cutoff dates.

=LOOKUP(2,1/(($E8>=$C$3:$C$5)*($E8<=$D$3:$D$5)),$B$3:$B$5)

One last question, since it is working in Excel, I have to upload this to Google Drive for my teachers to be able to access their own version of it, but the formulas turn up NA in Google Sheets? Is this a lost cause?? Anyone know a quick fix for this one?

 

[KRice]

Which formula is producing the NA error? Did you settle on using the LOOKUP formula or the IF/LARGE ...and are you using Start & End dates instead of cutoff dates?

 

[heathermchle]

Which formula is producing the NA error? Did you settle on using the LOOKUP formula or the IF/LARGE ...and are you using Start & End dates instead of cutoff dates?

I used the LOOKUP and Start & End Date times, from the formula you gave me. It works perfectly in Excel now but I'll need it to work in Google Sheets, too, and it doesn't.

 

[KRice]

I uploaded my mock up file to Sheets and didn’t see any issues. Do you want to post a link to a non-sensitive version of the file?

 

[heathermchle]

Yes of course, I appreciate the help. Here it is: https://docs.google.com/spreadsheet...ouid=115986899134636184795&rtpof=true&sd=true

I uploaded my mock up file to Sheets and didn’t see any issues. Do you want to post a link to a non-sensitive version of the file?

 

[KRice]

Enter this for the LOOKUP formula in H9 and drag down...Sheets requires some extra attention where arrays are concerned:
=ARRAY_CONSTRAIN(ARRAYFORMULA(LOOKUP(2,1/(($E9>=$C$4:$C$6)*($E9<=$D$4:$D$6)),$B$4:$B$6)), 1, 1)

 

[heathermchle]

It worked! You're a genius! Thank you so much for your help! I won't even tell you how much time I've actually spent trying to get this to work, I should have come here sooner. Thank you so so so much.

 

[KRice]

You’re welcome…I’m happy to help.