+ Reply to Thread
Results 1 to 3 of 3

Lookup and return culumn headers approximate match

  1. #1
    Registered User
    Join Date
    09-27-2018
    Location
    Malaysia
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Lookup and return culumn headers approximate match

    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: copy to clipboard
    Please Login or Register  to view this content.


    Attached is my workbook
    Your help will greatly appreciated

    Thank and Best Regards,
    Kenneth
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

    Re: Lookup and return culumn headers approximate match

    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]])
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-27-2018
    Location
    Malaysia
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Lookup and return culumn headers approximate match

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] User Defined function for comparing 2 array and return approximate match
    By menonarun in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 05-13-2018, 01:09 AM
  2. [SOLVED] Lookup (index/match) and return column headers
    By Barslund in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-08-2017, 06:01 AM
  3. Replies: 4
    Last Post: 03-31-2017, 05:49 AM
  4. [SOLVED] LOOKUP several approximate matches
    By M1234 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-08-2015, 06:40 PM
  5. Match value and return multiple headers
    By hcorbitt in forum Excel General
    Replies: 5
    Last Post: 07-24-2014, 02:45 PM
  6. Replies: 5
    Last Post: 02-08-2011, 09:07 AM
  7. Match or Lookup multiple approximate values from a table
    By fasih in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2009, 11:01 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1