Hi
I have a Loan Records table which has Following columns
The above is illustrative data
Now my problem is this. For each "Customer Reference", and for each "Loan Type", I want to pull the "Loan Closed Date" corresponding to the latest "Loan Disbursed Date". I have been able to use a MAXIF function to get the latest "Loan Disbursed Date" of a particular "Loan Type" (by setting MAXIF conditions to "Loan Type" and "Customer Reference".) However, the output of MAXIF is a value, but I need the cell reference so that I can Use Offset function on the Cell reference and get to the corresponding "Loan Closed Date"
In the above, Example, Customer 1 has 3 "Personal Loans" where the latest Loan Disbursed Date was on 1st January 2022. I want to pull the Loan Closed date corresponding to this loan i.e. 31st March 2022
Any help would be greatly appreciated
PS: I am not good at array formulae and would like to avoid the same if possible. Also I know this can be easily coded in VBA but would like to avoid VBA too.
I have a Loan Records table which has Following columns
| Customer Reference | Loan Type | Loan Disbursed Date | Loan Closed Date |
| 1 | Personal Loan | 1st January 2020 | 30th June 2021 |
| 1 | Personal Loan | 1st January 2021 | 31st December 2021 |
| 1 | Personal Loan | 1st January 2022 | 31st March 2022 |
| 1 | Loan against Shares | 1st June 2020 | 31st December 2021 |
| 1 | Loan against Shares | 1st October 2020 | 28th February 2022 |
| 2 | Car Loan | 1st January 2016 | 30th June 2021 |
| 2 | Car Loan | 1st January 2017 | 31st December 2021 |
| 2 | Car Loan | 1st January 2015 | 30st September 2021 |
The above is illustrative data
Now my problem is this. For each "Customer Reference", and for each "Loan Type", I want to pull the "Loan Closed Date" corresponding to the latest "Loan Disbursed Date". I have been able to use a MAXIF function to get the latest "Loan Disbursed Date" of a particular "Loan Type" (by setting MAXIF conditions to "Loan Type" and "Customer Reference".) However, the output of MAXIF is a value, but I need the cell reference so that I can Use Offset function on the Cell reference and get to the corresponding "Loan Closed Date"
In the above, Example, Customer 1 has 3 "Personal Loans" where the latest Loan Disbursed Date was on 1st January 2022. I want to pull the Loan Closed date corresponding to this loan i.e. 31st March 2022
Any help would be greatly appreciated
PS: I am not good at array formulae and would like to avoid the same if possible. Also I know this can be easily coded in VBA but would like to avoid VBA too.
