Right now the issue is that the formula returns the closest value regardless of whether its a high or lower value. I need to only Accept Lower than values.
{=INDEX($AO$2:$AO$5000,MATCH(SMALL(IF($AN$2:$AN$5000=A2,ABS($AO$2:$AO$5000-F2)),1),IF($AN$2:$AN$5000=A2,ABS($AO$2:$AO$5000-F2)),0))}
For reference:
AO is a Dollar amount
AN is a Date
A is Date
F is a Dollar amount
We are looking to match the Dates in AN to A and then find the closest Dollar amount in AO that is less than or equaled to F. There can be multiple instances on the same day with varying dollar amounts. we just need the closest one without going over what's in F to be returned. This does not have to be an index match formula if you can think of a better way to do it but I'm pretty positive it has to be an array formula as it must look at all of the values to make the correct decision.
=AGGREGATE(14,6,($AO$2:$AO$5000)/(($AN$2:$AN$5000=A2)*(($AO$2:$AO$5000)<=F2)),1)
the part after the / will evaluate to 0 if either is false. this causes an error which aggregate will ignore due to the 6. 14 tells aggregate to sort the results from largest to smallest. 1 tells aggregate to return the 1st largest value that is less than or equal to F2.