CODEWITHSUNDEEP
excelindexingmatch
Doug Coats
Doug Coats

Reputation: 7107

Index/match for date ranges

After doing some research and finding multiple good resources, I still find myself stuck on a simple index match formula (as the titles suggests). I clearly must be under/over (most likely under) thinking something here, and instead of of spending too much time messing around with it, I figured I would ask you guys :) Here is my formula:

 {=INDEX($P$1:$Q$8,MAX(IF(($B$1>=$P$1:$P$8)*($B$1<=$Q$1:$Q$8),MATCH(ROW($Q$1:$Q$8),ROW($Q$1:$Q$8)))))}

To explain a little bit-

I want the user input from b1 to be referenced against the date ranges that fall from $P$1:$Q$8 and return the end date(found in column q) of the latest date (in case of overlaps). However, with the current list, I am getting a #REF error. I tried playing around with some of the values, but ended up getting the user input from b1 instead of the corresponding Q:Q date. At first I thought it was due to my lack of understanding how MATCH worked, but even experimenting has lead me to believe even more so that that is in fact the case :/

enter image description here

Upvotes: 1

Views: 2775

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

It worked fine for me if I just added a column to the INDEX:-

=INDEX($P$1:$Q$8,MAX(IF(($B$1>=$P$1:$P$8)*($B$1<=$Q$1:$Q$8),MATCH(ROW($Q$1:$Q$8),ROW($Q$1:$Q$8)))),2)

Upvotes: 2

Related Questions