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!

 

[pjmorris]

Matthew, Jason,

I've had a look at Jason's non array formula and that to can be simplified as I have done with my original, to:

=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)*($B$16:$B$40>=I$2)*($B$16:$B$40<=I$3)*($F$16:$F$40<I$4),1)) for the lower value

and

=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)*($B$16:$B$40>=I$2)*($B$16:$B$40<=I$3)*($F$16:$F$40>I$4),1)) for the upper value.

The difference between the two approaches is that Aggregate doesn't throw an error when no value meets the criteria, the result is a 0. Whereas my solution will return a blank (courtesy of the IFERROR function). It depends on what you need as the result, that said its pretty easy to make 0 show as a blank: custom cell format "General;General;"

Jason, I know you didn't test the formula (which never makes it easy), but for info you didn't need the '/' between the ROWS and the criteria (it creates a #DIV0 error when the formula is parsed and therefore a 0 as the result), the '*' performs the array AND function that we need. And my COUNT section was a clumsy way to handle the order in which numbers are presented. Changing SMALL to LARGE (15 to 14 in the AGGREGATE function) means we can remove the count and simply replace with 1. PS. Thanks for offering the AGGREGATE - I've never used it before, but suspect it won't be the last time I do as I think your formula is tidier than mine and avoids an array formulas which I don't particularly like.

Regards

 

[jasonb75]

Peter, the use of / in aggregate instead of * is there to purposely create #DIV/0 errors on non matching rows and prevent the 0 result when the criteria is not met.

Also, for clarity (although a bit pedantic) it is still an array formula, it just doesn't require CSE confirmation.

 

[pjmorris]

hmmm, understand how you've used /, but actually its not needed and doesn't prevent a 0 result. (See the result in M8).

closing low.xlsx
	E	F	G	H	I	J	K	L	M
1
2			Daily SPL Date		02-Jun-20
3			60		24-Aug-20
4			Daily SPL Low		3999
5			Daily SPL Volume
6			Date Daily Closed Under		Thu 11-Jun-20	Tue 02-Jun-20			Jason's Formula
7			Daily Closing Price		3002.1	3080.82			3080.82
8									0
9
Sheet1

Cell Formulas
Range	Formula
I6:J6	I6	=OFFSET($B$15,MATCH(I$7,$F$16:$F$10000,0),0)
I7	I7	=MIN(($B$16:$B$10000>=$I$2)*($B$16:$B$10000<=$I$3)*($F$16:$F$10000)+NOT(($B$16:$B$10000>=$I$2)*($B$16:$B$10000<=$I$3))*MAX(($F$16:$F$10000)+1))
J7	J7	=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)),"")
J8	J8	=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)),"")
M7	M7	=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$10000)/($B$16:$B$10000>=I$2)*($B$16:$B$10000<=I$3)*($F$16:$F$10000<I$4),1))
M8	M8	=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)/($B$16:$B$40>=I$2)*($B$16:$B$40<=I$3)*($F$16:$F$40>I$4),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

It should be one of the following, Peter. My original formula was a copy / paste edit of yours from an earlier post, I didn't realise that I had missed a couple of changes.

=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)/($B$16:$B$40>=I$2)/($B$16:$B$40<=I$3)/($F$16:$F$40>I$4),1))

=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)/(($B$16:$B$40>=I$2)*($B$16:$B$40<=I$3)*($F$16:$F$40>I$4)),1))

Which will return an error if there is no matching row.

The first of the 2 methods above is my personal preference, although there is no difference in functionality between them. Naturally, the same change will need to be applied to the formula in M7 as well.

 

[matthewlouis]

I will get to these this afternoon on the simplified formulas AND the new for breaks OVER. Thanks, PJ!

Jason, thanks for the responses and advice on updating my profile. Just did that for excel version. Thank you for helping on this.

 

[pjmorris]

It should be one of the following, Peter. My original formula was a copy / paste edit of yours from an earlier post, I didn't realise that I had missed a couple of changes.

=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)/($B$16:$B$40>=I$2)/($B$16:$B$40<=I$3)/($F$16:$F$40>I$4),1))

=INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)/(($B$16:$B$40>=I$2)*($B$16:$B$40<=I$3)*($F$16:$F$40>I$4)),1))

Which will return an error if there is no matching row.

The first of the 2 methods above is my personal preference, although there is no difference in functionality between them. Naturally, the same change will need to be applied to the formula in M7 as well.

HI Jason, I've tried all the variants above and my own version of the AGGREGATE formula - none of them ever return an error regardless of whether a value is returned or not - I have a dataset of about 600 entries, the first 40 match the OPs data and the remainder were created using RANDBETWEEN to constrain the values to approximately the range shown in the question. Oddly it seems that because the return value from the comparators is either true or false (1 or 0) that it doesnt matter whether you divide or multiply - aggregate ignores errors. That said, I don't like to use a formula and rely on the function to negate any errors (apart from IFERROR of course).

Very strange that we seem to be seeing different behaviours - something to keep an eye on when I use it again.

Regards

 

[pjmorris]

I will get to these this afternoon on the simplified formulas AND the new for breaks OVER. Thanks, PJ!

Jason, thanks for the responses and advice on updating my profile. Just did that for excel version. Thank you for helping on this.

Thanks Matthew, my pleasure and I've learnt quite a lot (and also to Jason for the learning!).

 

[jasonb75]

HI Jason, I've tried all the variants above and my own version of the AGGREGATE formula - none of them ever return an error regardless of whether a value is returned or not - I have a dataset of about 600 entries, the first 40 match the OPs data and the remainder were created using RANDBETWEEN to constrain the values to approximately the range shown in the question.

Can you post your test sheet in XL2BB down as far as the first row with the RANDBEETWEEN functions please, Peter? I can't understand why you're not seeing an the errors when they should occur. The formula method works fine (I've used it many times previously) there is probably one nuance somewhere that is causing different behaviour.

Using =INDEX(F:F,AGGREGATE(14,6,ROW($F$16:$F$40)/($B$16:$B$40>=I$2)/($B$16:$B$40<=I$3)/($F$16:$F$40>I$4),1)) you should get #NUM! if there are no rows meeting the criteria.

Is summary, ROW(x:y)/({FALSE})/({FALSE})/({FALSE}) will return a #DIV/0 error for the entire array, only rows where all of the criteria are TRUE will return a valid number.
The AGGREGATE option to ignore errors only ignores the #DIV/0 errors with its own array, if there are no valid results then the result to pass to INDEX will still be an error on the basis of SMALL({#DIV/0,#DIV/0,#DIV/0},1), it there are no numeric values then there is no number to return. This would still need to be trapped by IFERROR.

 

[pjmorris]

Thanks Jason, you've made me check further - I think I lied in post 26 - I clearly hadn't tried all variations and hadn't used / between all the clauses; I had /**. What is interesting is that the choice of / or * doesn't seem to affect the outcome when all clauses return a value, but when the clause returns all FALSE the choice will decide whether an error is created or not - which means you can use them to tune the response for each clause: to cause an error use / to return 0 use *.

Something to put in the bag of tricks I think, thank you for sharing.

 

[jasonb75]

What is interesting is that the choice of / or * doesn't seem to affect the outcome when all clauses return a value

/ and * both have the same effect on TRUE, but not on FALSE.
/FALSE in any criteria will generate the #DIV/0 error, *FALSE generates 0.
/TRUE and * TRUE both generate a result of 1

There are other functions that the method can be used with, but not many, LOOKUP works without CSE, MATCH works with CSE, possibly others but nothing coming to mind (at least not without nesting the array in IFERROR).