Forum Discussion

JPAlves89's avatar
JPAlves89
Copper Contributor
May 26, 2023
Solved

How to find the latest update on a list of accounts

Hello everyone! I'm looking for some help on a set of data where I have account numbers with update dates and times. Each time there's an update on one of these accounts, there is another entry on ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 26, 2023

    JPAlves89 

    You can use a formula of the form

     

    =MAXIFS(date_range, account_range, specific_account)

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 26, 2023

JPAlves89 

To find the latest update for each account in Excel, you can use a combination of the MAX function and the INDEX-MATCH formula. Here's how you can do it:

Assuming your account numbers are in column B and the update dates and times are in column C, and the first row contains headers, follow these steps:

  1. Create a new column next to your existing data (e.g., column D) and enter the following formula in cell D2:

=MAX(IF($B$2:$B$100=B2,$C$2:$C$100))

Note: Adjust the range B2:B100 and C2:C100 to match your actual data range. Make sure the range covers all the data in your table.

  1. Press Ctrl+Shift+Enter instead of just pressing Enter. This will enter the formula as an array formula. The formula will appear with curly braces {} around it in the formula bar if entered correctly.
  2. Drag the formula down to apply it to the remaining cells in column D.
  3. Now, you have the latest update dates and times for each account in column D.
  4. If you want to retrieve the latest update date and time for a specific account, you can use the INDEX-MATCH formula. For example, to retrieve the latest update for account number "12345," enter the following formula in any desired cell:

=INDEX(C:C, MATCH(MAX(IF(B:B="12345", C:C)), C:C, 0))

Replace "12345" with the actual account number you want to look up.

Again, remember to enter this formula as an array formula by pressing Ctrl+Shift+Enter.

The formula will match the maximum update date and time in column C for the specified account number in column B and return the corresponding value from column C.

Note: If there are a large number of rows in your data, consider adjusting the range in the formula accordingly to improve performance.

The formulas provided, can be used in various versions of Excel, including Excel 2013, Excel 2016, Excel 2019, and Excel for Microsoft 365 (the subscription-based version of Excel).

The formula uses standard Excel functions like MAX, IF, INDEX, and MATCH, which are available in multiple versions of Excel.

However, please note that the specific implementation of array formulas, where you need to press Ctrl+Shift+Enter to enter the formula, might vary slightly depending on the Excel version.

In summary, the formulas should work in most versions of Excel, but the exact method of entering array formulas may differ slightly.

Resources