Array formula help

[mattri]

Hi,

I'm hoping someone can help with first attempt at an array formula. I have a bank statement in the form:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Date[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2018[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]07/01/2018[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]07/01/2018[/TD]
[TD]142[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]07/01/2018[/TD]
[TD]145[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]09/01/2018[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

I would like to be able to type a date into a cell and be provided with a Balance for the same, or nearest previous date, with the largest index value if there are duplicates of the same date. e.g. if I typed 08/01/2018 I would like to get the result 145. I would want the same result if I typed 07/01/2018.

I have managed to get the date index using a helpful array formula from this site, but it is returning the balance for the first matching date in the list. I am unsure how to add the extra criteria to then choose the largest Index out of the matching dates.
{=INDEX(C2:C6,MATCH(MAX(IF((B2:B6<=CHOSENDATE),B2:B6)),B2:B6,0))}

Many thanks in advance.

 

[Eric W]

Welcome to the forum!

How about:

=LOOKUP(2,1/(B2:B6<=CHOSENDATE),C2:C6)

assuming the indexes are in ascending order?

 

[mattri]

Welcome to the forum!

How about:

=LOOKUP(2,1/(B2:B6<=CHOSENDATE),C2:C6)

assuming the indexes are in ascending order?

Eric

That works brilliantly and seems so simple, yet my brain hurts trying to understand it!

If there were a solution that didn't need the indexes in order then that would be helpful, but it's not essential and this will do the trick.

Many thanks.