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.
Ab ab eius sequi similique. Autem reprehenderit sunt qui nulla. Dolores voluptatem repellat voluptas praesentium sed nisi et nesciunt. Est corporis aut accusantium blanditiis molestiae iusto.
Tempore cum hic alias voluptatem aperiam est ut et. Dolorem sit aut aut distinctio non. Deserunt non placeat in qui ducimus ut consectetur earum.
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...