lookup/index/match with two reference cells!!

[Rasberry]

My data looks like this:

2460564010	NORTHEAST BANK	SHRI GURU, INC. DBA	8/16/2021​	110.21​
2460564010	NORTHEAST BANK	SHRI GURU, INC. DBA	10/15/2021​	209.72​
2460564010	NORTHEAST BANK	SHRI GURU, INC. DBA	10/15/2021​	99.45​
2632185007	COMERICA BANK	BAUHAUS PROPETIES LLC AND ZIMMER FREI	8/16/2021​	848.61​
2632185007	COMERICA BANK	BAUHAUS PROPETIES LLC AND ZIMMER FREI	9/15/2021​	814.8​
2632185007	COMERICA BANK	BAUHAUS PROPETIES LLC AND ZIMMER FREI	10/15/2021​	835.27​
3084016001	READYCAP LENDING, LLC	FIREPLACE SUPPLY, INC.	8/16/2021​	48.64​
3084016001	READYCAP LENDING, LLC	FIREPLACE SUPPLY, INC.	9/15/2021​	50.21​
3084016001	READYCAP LENDING, LLC	FIREPLACE SUPPLY, INC.	10/15/2021​	51.55​
3906245004	COMMERCE BANK, A DIVISION OF	LIBERTY HEALTHCARE	8/16/2021​	127.87​
3906245004	COMMERCE BANK, A DIVISION OF	LIBERTY HEALTHCARE	9/15/2021​	0​
3906245004	COMMERCE BANK, A DIVISION OF	LIBERTY HEALTHCARE	10/15/2021​	121.31​

I can use this formula to bring back the value in the last column: =(INDEX('payment history (16)'!AO:AO,(MATCH('Latest payment'!A11,'payment history (16)'!C:C,0))))
And I can use this formula to bring back the latest (most recent) payment date in the second to last column): =(MAX(IF(A9='payment history (16)'!C:C,'payment history (16)'!AN:AN)))

Assume there are many columns of data I have left out of this snapshot. The reference pieces are the identifier (first column), payment date (fourth column), and the value I want is the fifth column, but only the latest payment, not the first payment in the list?

My problem: How can I adjust back the value that is adjacent to the latest payment date? My formula keeps bringing back the first instance rather than either searching the max of the payment date and bringing back the value adjacent to it. Two of us having been trying to solve this issue for awhile now.

 

[Rasberry]

I did a work-around. I sorted the data so that the most recent payment data for each loan number is at the top of each group of loans. Then my Index formula brings back the most recent payment. But I am still curious for future reference, how you nest to have excel use the first reference (loan number), then the second reference to identify the correct value to bring back.

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Rasberry]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done -- Office 365

 

[Fluff]

Thanks for that.
How about

=TAKE(SORT(FILTER('payment history (16)'!$AN$2:$AO$1000,A11='payment history (16)'!$C$2:$C$1000),1,-1),1,-1)

 

[Rasberry]

Thanks for that.
How about

=TAKE(SORT(FILTER('payment history (16)'!$AN$2:$AO$1000,A11='payment history (16)'!$C$2:$C$1000),1,-1),1,-1)

That is interesting, and it works in two instances, but something is not quite right. A coupole of times it works, then it returns #CALC!.

 

[Fluff]

The #calc means that there was nothing matching the criteria.

 

[Rasberry]

=INDEX('payment history (16)'!AO:AO,(MATCH('Latest payment'!A18&D18,'payment history (16)'!C:C&'payment history (16)'!AN:AN,0)))

This formula works! I matched two columns using the match function with the ampersand (&) and returned the value in the indexed column.

 

[Fluff]

Glad you sorted it & thanks for letting us know.