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.
Voluptas quibusdam mollitia dignissimos molestiae. Ratione earum deleniti fugiat laudantium. Vel voluptatem porro quis quisquam quae et excepturi. Consequatur veritatis voluptatum doloribus et. Quis quidem nobis ratione aut. Tempora quos quia quibusdam sed modi aspernatur eos.
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...