I'm creating a spreadsheet to monitor the last time we contacted a customer. In tab "Overview" I have a list of all the customer names and addresses for example:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Name | Address | Telephone | Date of Next Contact | Action Required | Special Notes |
| A. Anderson | 123 Fake St | 00000 | 01/07/2021 | Yes | Does not speak English |
In tab "Log" I have a VLOOKUP where someone will put the name and it will drag through their contact details for example =IFERROR(VLOOKUP(A2,Overview!$A$1:$F$442,2,FALSE),"Ensure Name is Exactly as shown on 'Overview' tab")
The layout of the "Log" tab is:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Name | Address | Date Contacted | Outcome | Action Required | Action Completed | Date of Next Contact | Special Notes |
| A. Anderson | 123 Fake St | 01/02/2021 | Completed | No | 01/05/2021 | - | Does not speak English |
| A. Anderson | 123 Fake St | 01/05/2021 | Completed | Yes | 05/05/2021 | 01/07/2021 | Does not speak English |
I've included an example where there are two records for the same customer. This is because I want to keep a record of contacts, so a new row will be added for each contact. But in tab 'Overview' I'd like the "Date of Next Contact", "Action Required", and "Special Notes" to come through automatically from the "Log" tab when updated. I used a VLOOKUP to bring the data back, but now that there are duplicates from the two records Excel only brings back one - the oldest. Whereas I am looking to only bring back the most recent information. Any time a new row is added it should update the relevant record in the "Overview" tab.
The formula on "Overview" column D I'm using is just: =IFERROR(VLOOKUP(A2,Log!$A$1:$O$8,7,FALSE),"")
Is there a way to incorporate a check for the most recent date into the VLOOKUP function? I've searched online but every solution I've found involves returning all of the records, not just the most recent.
3 Answers
If you have a newer version of excel use =XLOOKUP()
your formula then becomes: =XLOOKUP(A2,Log!$A$1:$A$8,Log!$G$1:$G$8,"",0,-1)
one of the things the xlookup does is combines the iferror and vlookup into 1 function. The other thing that it does is it can search backwards. So as long as your log is in chronological order you can use the -1 as the last item and it will search backwards (of course you could continue with your formula and just sort your table array with newest on top...)
1If you don't mind adding a helper column, then here's one way. In your LOG sheet, add another column. Formula for column I (Max_Contact_Flag)...
=IF(MAXIFS(C:C,A:A,[@Name])=[@[Date Contacted]],"Yes","No")This flag is looking up the most recent contact date per person, and if the max date matches the current row's date for that person, then it's returning a YES value.
If you're not using a table, then replace @Variables with column ranges. But I highly recommend using a table because the helper column will automatically recalculate when adding a new row, so no need to keep dragging formulas.
Then, you could either filter on the YES values in your table, or, you could also create a pivot table on your data. In the pivot, then set your filter to Yes. This pivot would represent your OVERVIEW.
To make it easier to understand, convert your Log into an Excel Table (CTRL t) with headers. I named it tLog:
Then in Overview, starting in cell C2, put this formula and drag it down:
= INDEX(XLOOKUP(A2,INDEX(SORTBY(tLog,tLog[Date Contacted],-1),,1),INDEX(SORTBY( tLog,tLog[Date Contacted],-1 ),,)),1,{2,7,5,8})Where A2 is the name of the person you are looking up. The {2,7,5,8} is list of columns you want to retrieve from tLog. (NB: if you just leave that blank, it will grab all of the columns.)
This is hard to read, so you can simplify it and speed up calculation time by defining a Named Range in the Formulas tab.
You could name the sorting table _sTable and set it to this formula:
=SORTBY( tLog, tLog[Date Contacted], -1 )That will allow you to put a more readable and much faster formula in C2:
= INDEX(XLOOKUP(A2,INDEX(_sTable,,1),INDEX(_sTable,,)),1,{2,7,5,8})The result will look like this:
The way this works is that it creates a new version of your Log in memory that is sorted by Date of Next Contact and then performs the XLOOKUP search on this table instead of your Log table.
