Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that looks for a specific name in three columns, takes all of the rows that match to get dates from another column. Then from the dates that match the rows with the names, take the second earliest date and generate it.
The below formula is giving me the second date listed for the specific person instead of giving me the second earliest date.
=IFERROR(INDEX('Input Data'!$E$5:$E$150,SMALL(IF(Tracker!$B6='Input Data'!$G$5:$I$150,ROW('Input Data'!$G$5:$I$150)-ROW('Input Data'!$G$5)+1),2)), "N/A")
Solved by A. J. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/10/2018 - 12:11
Hello and thank you for choosing Got It Pro-ExcelChat! I can help you with your problem.
Excelchat Expert
10/10/2018 - 12:11
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows. Let me ask you a couple of quick questions to make sure I fully understand your problem. :)
User
10/10/2018 - 12:11
Thank you
Excelchat Expert
10/10/2018 - 12:11
I see you need help modifying your existing formula for it to work properly as to your requirements. May you please provide the file you're currently working on for us to better assess the concern., Thanks
User
10/10/2018 - 12:12
Hold on one moment
User
10/10/2018 - 12:13
I've just e-mailed it to myself from my laptop
User
10/10/2018 - 12:13
It is from work. Please delete it after you use it.
Excelchat Expert
10/10/2018 - 12:13
Oh I see, noted on that. Will await your uploaded file then.
Excelchat Expert
10/10/2018 - 12:13
Yes of course. :)
Excelchat Expert
10/10/2018 - 12:13
All data is purged once the session ends. :)
User
10/10/2018 - 12:14
Thanks
User
10/10/2018 - 12:14
It should be here any moment
User
10/10/2018 - 12:14
Thank you
User
10/10/2018 - 12:15
I am uploading it
Excelchat Expert
10/10/2018 - 12:16
Yes, please do.
Excelchat Expert
10/10/2018 - 12:16
You may upload a file by clicking on the paper clip icon in our chatbox.
User
10/10/2018 - 12:17
It does not load
Excelchat Expert
10/10/2018 - 12:18
Please try refreshing your browser and reuploading again.
User
10/10/2018 - 12:18
It's an xlsm file
Excelchat Expert
10/10/2018 - 12:18
Oh I see. In that case, please save it as .xlsx first and upload it here.
User
10/10/2018 - 12:18
Doing that now
User
10/10/2018 - 12:19
It worked
[Uploaded an Excel file]
User
10/10/2018 - 12:20
The formulas is in the tracker tab'
Excelchat Expert
10/10/2018 - 12:20
Already downloaded and opened! I believe we need to work on column I of the Tracker sheet. This is linked to the input data sheet as per the formula.
User
10/10/2018 - 12:21
column I
User
10/10/2018 - 12:21
Yes
User
10/10/2018 - 12:21
It is yielding the second item listed
User
10/10/2018 - 12:21
For example for Rod Sinton, it should show from the Input Data tab, E18, since it is the second earliest date.
User
10/10/2018 - 12:22
Instead it shows the second date listed for him - E20
Excelchat Expert
10/10/2018 - 12:23
Oh okay I see. So basically, in column I, you would want to return the second earliest date corresponding to the name in column B. Is this correct?
User
10/10/2018 - 12:23
Yes
Excelchat Expert
10/10/2018 - 12:23
Nice to know! Let me work on this within a local version I downloaded and will upload it here in our chat once done. I may be able to do this within 30 minutes. Let me work on it for a while and update you the soonest! :)
User
10/10/2018 - 12:23
Thank you
User
10/10/2018 - 12:23
!
Excelchat Expert
10/10/2018 - 12:38
Hi! Please check out this updated file for your review. :)
[Uploaded an Excel file]
Excelchat Expert
10/10/2018 - 12:39
Just to explain what I did, I shortened and corrected the formula by only using SMALL and IF within an IFERROR so as to pick the second earliest dates corresponding to a given name.
Excelchat Expert
10/10/2018 - 12:40
The IF function simply lets us filter what dates correspond to a name while the SMALL function lets us choose the second earliest date among these. Lastly, if there are no dates available, the IFERROR allows us to display "N/A" instead of an error. :)
User
10/10/2018 - 12:40
tHANKS
User
10/10/2018 - 12:40
I am taking a look now
User
10/10/2018 - 12:41
Thank you!
Excelchat Expert
10/10/2018 - 12:41
Glad to be of help! Does this particularly solve your main query? :)
User
10/10/2018 - 12:41
Yes
Excelchat Expert
10/10/2018 - 12:41
Nice to know! Do you have any more clarifications with the provided solution? :)
User
10/10/2018 - 12:42
This is good
User
10/10/2018 - 12:42
Thanks
Excelchat Expert
10/10/2018 - 12:42
Nice! You may now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
Excelchat Expert
10/10/2018 - 12:45
Please do not forget to click the End Session button; otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you. :)
User
10/10/2018 - 12:45
You too
User
10/10/2018 - 12:45
thanks!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
