excel help
What equation can I write in excel if I want excel to show me an output within certain ranges. Looking at the data table below, if I want excel to show "B" in a cell by typing 5/6/2016 due to the number being between 1/1/15 and 1/1/2020 - I have a long list and not sure how to write the equation or what format to use.
Date Group
1/1/2012 A
1/1/2015 B
1/1/2020 C
1/1/2022 D
I’d use the IFS(…) function. Start with your earliest or latest date e.g. IFS( x > date in cell y, “A”, x > date in cell z, “B”,…)
Thanks! I am familiar with that. If you have a list of 1000 dates, challenging to write all those IFs. Is there another function in excel outside of mutliple nested IFs? Thanks for responding!
Posting the answer here: you’ll want to have the dates in ascending order (earliest first, followed by next chronologically). With corresponding values.
Then you’ll want to run and index (desired values , match (lookup value, ascending dates array, 1)
The 1 in the match function will return the highest date less than your lookup value. You may want to use -1 if you want it to “round up” instead.
Good point. PM me, may have a solution for you.
Thank you! I will try.
Enim qui non doloremque. Et harum accusamus maxime ducimus molestiae totam architecto.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...