Hi
I'm trying to perform a lookup in a table to return the column header from another table based on the approximate time in the table
I was thinking this function would solve it but it doesnt seen to work, and I think it's because the match function has an array input, but doesn't know what to return.
Formula:
Please Login or Register to view this content.
Attached is my workbook
Your help will greatly appreciated
Thank and Best Regards,
Kenneth
It doesn't work because time is not sort ascending
I rearrange from Early Out < Early Out2 < OK < OT
Then out time for night shift need to add 1 day from 00:01 to 1/1/1900 00:01:00
Please try
D7
=LOOKUP(A7,INDEX(WorkingHrs[[OK]:[Late In]],MATCH(C7,WorkingHrs[Working Hours],),),WorkingHrs[[#Headers],[OK]:[Late In]])
E7
=LOOKUP((B7<A7)+B7,INDEX(WorkingHrs[[Early Out]:[OT]],MATCH(C7,WorkingHrs[Working Hours],),),WorkingHrs[[#Headers],[Early Out]:[OT]])
Wow, you are brilliant. I tried search for solution over internet and figure out ways to lookup for the whole day but cant find any.
Didn't knew rearranging the time in ascending ordering will work.
You are my savior.
Thanks so much Bo_Ry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks