Please help with lookup for last date of the month & value

[yanyanyan]

Hi

I need help with a formula to find out highlighted yellow cells. For example, I need to find out the last date balance for Machine #1 in July 18, table on the right have 31 Jul 18 balance of $500..

Thanks!

 

[Special-K99]

There are no cell references which isn't helpful, therefore.
Assuming "Opening Bal" is in B1...

in C2
=INDEX(J3:J8,MATCH(EOMONTH(C1,0),E3:E8,0),1)
in C3
=INDEX(J13:J18,MATCH(EOMONTH(C1,0),E13:E18,0),1)

Adjust the formulas to match your spreadsheet, this part of the solution is down to you.


Because of the layout of your Machine # tables you'll need to change the ranges in each formula.
The layout you have of Machine# tables is not the best way to do this.


UPDATE: This won't work for machine #2 as the last date is not end of month.
Thinking...

 

[Special-K99]

in C2
=INDEX(J3:J8,MATCH(MAX(IF(MONTH(C1)=MONTH(E$3:E$8),E3:E8)),E3:E8,0),1)
in C3
=INDEX(J13:J18,MATCH(MAX(IF(MONTH(C1)=MONTH(E$3:E$8),E3:E8)),E3:E8,0),1)

These are both
Array formula, use Ctrl-Shift-Enter

 

[yanyanyan]

in C2
=INDEX(J3:J8,MATCH(MAX(IF(MONTH(C1)=MONTH(E$3:E$8),E3:E8)),E3:E8,0),1)
in C3
=INDEX(J13:J18,MATCH(MAX(IF(MONTH(C1)=MONTH(E$3:E$8),E3:E8)),E3:E8,0),1)

These are both
Array formula, use Ctrl-Shift-Enter

Thank you so much, it works!