Hi All,
I have an audit table that records the change to an account's information. I need to look up the most recent datetime and street address for the account only when the street_updated column is True and look it up by the cust_id.
In the example dataset below, I would expect my lookup output to be
cust_id: ABC1
datetime : 4/3/24 6:22 PM
street: 123 Forest
cust_id: ABC2
datetime : 4/3/24 6:22 PM
street: 789 Forest
Screenshot 2024-04-25 at 6.11.00 PM.png
Last edited by cwwazy; 04-25-2024 at 10:42 PM.
If, by any chance, you have updated to 365, you can use:
Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi TMS,
Thanks so much for answering! This formula works, but I missed some more details in my original question. How would the formula change if I wanted to perform a kind of xlookup by cust_id? I have updated my question details.
Thanks!
Hi cwwazy I think you are probably using Office 365 - please update your forum profile accordingly. Thanks.
Cell F4 formula
Formula:Please Login or Register to view this content.
Hi wk9128,
Thanks for the reminder and your reply! I have updated my version to 365 on my profile.
Adding to your formula you provided, if I wanted to add the cust_id as a look up value in the formula, is that possible? Like for example, in my dataset, I would have say many cust_id records, but I am only interested in cust_id ABC1 and ABC2. If my cust_id's are in F4 and F5, how could it be referenced in the formula.
Attachment 867250
Hi cwwazy Invalid Attachment specified and a valid link
Please read and refer to this link, how to upload attachments,follow mathod 2
https://www.excelforum.com/the-water...his-forum.html
Cell F11
Formula:Please Login or Register to view this content.
Last edited by wk9128; 04-25-2024 at 09:44 PM.
Hi wk9128,
I have re-attached the excel file using method 2. It is " Sample dataset.xlsx "
Thanks!
POST#6 formulaOriginally Posted by cwwazy
Thanks wk9128! This worked for me!
cwwazy You're Welcome. Glad to help . Thank You for the feedback and rep.
You're welcome. Thanks for the rep.
i have answered you new post - about this formula not working on large data set
ALSO the full data is NOT the same as the sample data - for example column B is a number with decimals (and you are trying to match to an integer) and also its NOT alphanumeric as shown here
column B is not an integer and so will not meet criteria
Also the MAXIFS() will not work as it pulls out the maxvalue in the dataset , and not where it matches with column B
anyway - a bit of a messy reply , as i replied and then found more things wrong as i went along - BUT its here
https://www.excelforum.com/excel-for...ml#post5944552
Just luck I think it worked on the NEW data set for a range up to row 23 - - as the maximum date for when I =TRUE also happened to match the criteria you used for B - coincidence i think in the first 23 rows on the new data sheet
i will be offline from tomorrow for a week
Last edited by etaf; 04-27-2024 at 04:53 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks