Combining VLookup, max and other formulas to find customer's most recent visit - guidance?

Question

Hello! Looking for some help with a formula - attaching an example spreadsheet with some simple data. My data are in two tabs - it needs to stay that way. On the second tab, I would like a formula that finds the name of the most recent visit date for each customer.

I will be honest - I have spent time reviewing multiple videos to try to piece this formula together. The concept seems simple enough to me where I feel like I should be able to do this. But...I realize it's a bit over my head. Asking for any assistance if you are able to help.

If it's just as easy, I'm attaching screenshots so you can see my data - identical to the attached Excel spreadsheet. I have no formulas in here because I am misunderstanding some key concepts in creating the formula.

Sincere thanks in advance!!

Nancy

harun24hr · Answer

nmlynch&nbsp;Use FILTER() function with SORT().=@SORT(FILTER('Raw Visit Data'!$C$3:$D$17,'Raw Visit Data'!$B$3:$B$17=B3),2,-1)&nbsp;&nbsp;

djclements · Answer

Harun24HR&nbsp;Nice use of the reference operator (@) to return the first result of the array, rather than TAKE(array, 1, 1). I like it! 🙂

oliverscheurich · Answer

nmlynch&nbsp;=INDEX('Raw Visit Data'!$C$3:$C$17,MATCH(LARGE(IF(('Raw Visit Data'!$B$3:$B$17='Needed Output'!B3)*(NOT(ISBLANK('Raw Visit Data'!$D$3:$D$17))),'Raw Visit Data'!$D$3:$D$17),1),IF(('Raw Visit Data'!$B$3:$B$17='Needed Output'!B3)*(NOT(ISBLANK('Raw Visit Data'!$D$3:$D$17))),'Raw Visit Data'!$D$3:$D$17),0))&nbsp;You can try this formula which works with e.g. Excel 2013 as well. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.