Excel - INDEX Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.