Excel Index Match with date and multiple criteria returning #N/A

[CC11]

Hey guys I don't know what is wrong with my formula... from everything I see it should work. I'm entering it with ctrl+shift+enter and other arrays are working.

I need to match both the date closest to or including a given date and match that to a customer's name.

Formula:
=INDEX('2020.07.30 Customer Balances'!O3302:O3316,MATCH(1,(("test")='2020.07.30 Customer Balances'!B3302:B3316)*(MAX(('2020.07.30 Customer Balances'!G3302:G3316<=R3302)*'2020.07.30 Customer Balances'!G3302:G3316)),0))

excelCapture.PNG

[bebo021999]

With date are sorted as per sample, try:

=LOOKUP(2,1/($A$2:$A$16="test")/($F$2:$F$16<=Q2),$N$2:$N$16)

[CC11]

With date are sorted as per sample, try:

=LOOKUP(2,1/($A$2:$A$16="test")/($F$2:$F$16<=Q2),$N$2:$N$16)

You're awesome! This worked, however it will only work as long as the dates are in order. I can't count on that 100% though.

I would still rather the index/match work as it's more reliable long term though