on tab "AS Data" i have a table Y through AB
Y = Names
Z = Dates
AA = Start Time
AB = End Time
on tab Report I have a drop down in cell a2 with names and column A has dates from 12-29-2016 to 3-23-2017.
I need a formula that vlookups the name in cell a2, matches the date on tab Report and returns the time in column AA with that criteria
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
DATE in tab Report tab is Form A4
DATA in AS Data is Y2:AB6,
ARRAY Formula in B4 in tab Report sheet, then drag down for start time.
=IFERROR(INDEX('AS Data'!$AA$2:$AA$6,SMALL(IF(('AS Data'!$Y$2:$Y$6='tab Report'!$A$2)*('AS Data'!$Z$2:$Z$6='tab Report'!$A4),ROW($Y$2:$Y$6),""),1)-ROW($Y$2)+1),"")
ARRAY Formula in C4 in tab Report sheet, then drag down for End time.
=IFERROR(INDEX('AS Data'!$AB$2:$AB$6,SMALL(IF(('AS Data'!$Y$2:$Y$6='tab Report'!$A$2)*('AS Data'!$Z$2:$Z$6='tab Report'!$A4),ROW($Y$2:$Y$6),""),1)-ROW($Y$2)+1),"")
ARRAY formula is used
To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.
intended result is in cell b5 on report tab
kvsrinivasamurthy , thanks for the reply. I pasted your formula and I'm not getting any result. I am following the array instructions too. I attached the file with intended results.
There is no file attached anywhere.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
try now, i believe there was an issue with the file size before
ARRAY formula should be confirmed with shft+Ctrl+Enter keys together, not with Only Enter key.
Pl see my previous post
ARRAY formula in B5 of Report sheet, then drag down.
ARRAY formula in B5 of Report sheet, then drag down.
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by kvsrinivasamurthy; 03-26-2017 at 01:44 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks