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:

Index	Date	Balance
1	05/01/2018	100
2	07/01/2018	150
3	07/01/2018	142
4	07/01/2018	145
5	09/01/2018	500

<tbody>
</tbody>

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.