Retrieving the Most Recent Payment Made by Client

In this article we will learn how we can retrieve the most recent payment made by client in Microsoft Excel.

While preparing reports for multiple clients, you want a formula that will retrieve the date of the most recent payment made by each of the clients.

You can use a combination of MAX & IF functions to derive the most recent dates.
 

MAX: Returns the maximum number from a range of cells or array. For example, if a list of numbers contains 5, 6, 7, 8, 9 & 10, then the output will be 10.

Syntax =MAX(number1,number2,...)

There can be maximum 255 arguments. Refer below shown screenshot:

 

 
The IF function checks if a condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.

Syntax = IF(logical_test,value_if_true,value_if_false)
 

Let us take an example:

We have list of clients in column A, Dates of payment made in column B & amount paid in column C. We want a formula that will retrieve the most recent dates in a range of cells. Based on the criteria set in cell E2, the formula will return the output.

• The names of clients are fictitious.

• The formula in cell F2 would be
• =MAX(IF($A$2:$A$8=E2,$B$2:$B$8)
• Press enter on your keyboard.
• The function will return the most recent payment’s date for ABC criteria.

• In cell E2, we have to apply drop down by using Data validation feature. Now, if we change the name of the client then the result will automatically update.