I would like some assistance in creating a formula that looks up an employee id number and returns the value in column F based on the most recent date. There are potential duplicates in the employee id column which is why I would like to search for the most recent "transaction." I have attached a sample workbook. I can't quite wrap my head around index/match formulas and I believe some variation of that will be required to make this happen.
Thank you in advance.
Last edited by jekeith; 05-21-2018 at 11:39 AM.
Try
in B4
=MAX(IF($D$4:$D$1000=A4,$E$4:$E$1000))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
format column b as mm/dd/yyyy;;;@ to hide the 0 results
Thanks John, I actually want to return the value on the "Type of Leave" column not the date value. Also I need it to lookup based on the values in column A
Last edited by jekeith; 05-21-2018 at 11:47 AM. Reason: clarification
Try
=IFERROR(INDEX($F$4:$F$1000,MATCH(MAX(IF($D$4:$D$1000=A4,$E$4:$E$1000)),$E$4:$E$1000,0)),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
I am trying to return the leave type with the most recent date associated with a given ID. Maxifs won't get me quite where I need to go. @chief_abound.
I will try this out. Thanks John!Originally Posted by JohnTopley
I tried the formula out and while it looks more like what I thought might required it only returns the error value. Maybe If i rephrase what I am asking for help with, that might getting us going in the right direction. Using the spreadsheet provided above. I want start by looking up the ID value in column A in table 1[column d] and return the value in column F for the most recent date IF there are duplicates.
This actually seems to have done the trick.
Please Login or Register to view this content.
Thanks everyone.
Last edited by jekeith; 05-21-2018 at 12:57 PM.
See attached
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks