May 26 2023 02:37 AM
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 the table with its date and time. This means that there may be several updates per account, and thus, the accounts column (which is my lookup value) will definately have duplicates.
What I'm looking for is a formula that would let me know what is the latest update for each of the accounts listed in column B.
Given the limitations vlookup and index-match have with duplicates, I've been stumped on how to solve this with a formula.
Thank you guys in advance!
May 26 2023 02:58 AM
SolutionMay 26 2023 03:00 AM
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:
=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.
=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.
May 26 2023 11:00 AM
May 26 2023 02:58 AM
SolutionYou can use a formula of the form
=MAXIFS(date_range, account_range, specific_account)