Index and Match between Two Dates

[matthewlouis]

Hey EXCEL experts, I need a formula for an index and match that looks for a number that is below another cell number. Attached is an image with more explanation. The key is to keep the index / match between the two dates on the sheet in each column.

Thanks in advance!

 

[matthewlouis]

pj, whatever you can offer would be much appreciated. This one has me stumped!

 

[pjmorris]

Are you able to share the spreadsheet, say via Dropbox?

 

[matthewlouis]

Not a member . . . did the trial a few years ago and haven't joined. Don't need it very much. Any other way I can get you the file?

 

[pjmorris]

I think Fluff’s answered the question that it’d have to be a cloud share. I’ll see what I can do with what I’ve got - might take a while!

 

[matthewlouis]

Man, thank you so much! Appreciate anything you can do!

 

[pjmorris]

Well I think this works, it seems to work for me!

=IFERROR(INDEX($F$16:$F$10000,SMALL(IF(($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000<I$4),ROW($F$16:$F$10000)-ROW($F$15)),COUNT(IF(($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000<I$4),ROW($F$16:$F$10000)-ROW($F$15))))),"")

entered with CTRL-SHFT-ENTER, (make sure it has curly braces around it)

It looks horrendouse, but:

The IFERROR simply puts a blank if no value is found - the inner code returns a #NUM error.
The inner code returns the row number of the value we want and INDEX($F$16:$F$10000 is the list from which we want the number.

This section: ($B$16:$B$10000>=I$2) * ($B$16:$B$10000<=I$3) * ($F$16:$F$10000<I$4) tests the dates against the earliest date, then the latest date, and then tests the values against your lower limit. If you've moved the upper date change I$3 as necessary.

The COUNT formula is used to find how many items have been returned as it is the last one in the list that we want. We could replace this whole section with 1 if the dates ran from oldest to newest down the page!

I'm indebted to www.exceltip.com for the idea of how to do this: 'How to Find the nth Occurrence'

HTH

 

[matthewlouis]

Awesome!! I’ll plug it in morning and get back. You’re awesome pj, thank you!!

 

[matthewlouis]

You, sir, are amazing! Works perfect! Thanks so much!

If I can ask . . . if I wanted to see the FIRST close over a HIGH instead of a low, would I reverse the > signs as shown below (different numbers in cell I3, of course)

=IFERROR(INDEX($F$16:$F$10000,LARGE(IF(($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000>I$4),ROW($F$16:$F$10000)-ROW($F$15)),COUNT(IF(($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000>I$4),ROW($F$16:$F$10000)-ROW($F$16))))),"")

This is also a way (bullish) for me to track the trend of the market. Would that be the formula?

Again, I cannot thank you enough for the manual work you have saved me!

 

[jasonb75]

I have the spreadsheet ready to attach -- how do you attach it? I just see mage and URL (?)

Here's how

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Also, it would help if you update your user profile to show which version of excel you use so that people helping you know what features and functions you have. To do this, click your user name in the top right corner of the screen, then go to 'Account Details', then scroll down and check the correct boxes, remember to scroll to the bottom and save any changes before leaving the page.

I've tried to simplify the formula that @pjmorris suggested, this is not tested and will not work with anything older than excel 2010. This one will not need to be array confirmed.

=IFERROR(INDEX($F:$F,AGGREGATE(15,6,ROW($F$16:$F$10000)/($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000<I$4),COUNTIFS($B$16:$B$10000,">="&I$2,$B$16:$B$10000,"<="&I$3,$F$16:$F$10000,"<"&I$4))),"")

 

[pjmorris]

Matthew,

I like your thinking, and you gave me an idea to simplify both formulas. To get the first under the figure use:

=IFERROR(INDEX($F$16:$F$10000,LARGE(IF(($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000<I$4),ROW($F$16:$F$10000)-ROW($F$15)),1)),"")

and for the first over:

=IFERROR(INDEX($F$16:$F$10000,LARGE(IF(($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000>I$4),ROW($F$16:$F$10000)-ROW($F$15)),1)),"")

Both array entered. I hadn't thought to use LARGE until you did, but it affects which of the rows is returned rather than being a function of the comparison - by using that rather than SMALL i could get rid of the COUNT section.

HTH