+ Reply to Thread
Results 1 to 7 of 7

Multiple Vlookup to nearest time

  1. 08-02-2016, 08:02 AM #1
    ronettes
    ronettes is offline
    Registered User
    Join Date
    03-30-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    63

    Multiple Vlookup to nearest time

    HI Gurus

    I have a great brain twister...

    Is it possible to have multiple V-lookups / matched data? and to the nearest time?

    Please see the attachment...

    Colm A-E = Data 1
    Colm I - N = Data 2

    I am not sure if in anyway possible to have it match the nearest time slot to a particular other time. I made some color coded examples. Would make better sense...

    We have a customer service button as soon as clients pay, and we try to link the time of the sale with the time of the button pressed...TO see which cashier got what rating.

    I hope anyone can help me...

    THANKS A LOT...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-02-2016, 11:36 AM #2
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Multiple Vlookup to nearest time

    Not sure if you want to match I values to C or the other way around - is there a requirement that one time be greater than the other, or just the closest time to the first?
    Last edited by Bernie Deitrick; 08-02-2016 at 11:40 AM.
    Bernie Deitrick
    Excel MVP 2000-2010
    Reply With QuoteRegister To Reply
  3. 08-04-2016, 01:41 AM #3
    ronettes
    ronettes is offline
    Registered User
    Join Date
    03-30-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Multiple Vlookup to nearest time

    Quote Originally Posted by Bernie Deitrick View Post
    Not sure if you want to match I values to C or the other way around - is there a requirement that one time be greater than the other, or just the closest time to the first?
    Hi Bernie

    The Idea is to match the closest to the first column... The first data is sales made by the cashier, then the customer need to press a rated button, but it is not linked to our system. So now I am trying to match the 2 via excel to see which cashier is rated for all their sales...

    Something in that line...
    Reply With QuoteRegister To Reply
  4. 08-04-2016, 10:28 AM #4
    MrShorty
    MrShorty is online now
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Multiple Vlookup to nearest time

    Here's how I would probably do it (each step represents a single cell, since I like to spread calculations over several cells with simple formulas rather than great big single cell megaformulas):

    1) A MATCH() function to locate the lookup value in the table (It will find the time just before the lookup value)
    2) INDEX() function using the result of (1) to extract the time before lookup_value from the table
    3) INDEX() function using the result of (1)+1 to extract the time after lookup_value from the table
    4) Subtract the lookup value (take absolute value) from each extracted time
    5) Determine which difference is smaller. and pull that time stamp into the final table.

    Links to help files for these functions: https://support.office.com/en-us/art...1-63f26a86c0eb
    Which step do you get stuck on?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
  5. 08-04-2016, 12:25 PM #5
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Multiple Vlookup to nearest time

    Is it something like this you want? Note that I restricted the formulas to the length of the red and green fields, if not there would be at least one more hit. The time difference is not specified so I put in a spinner button just for the purpose of testing.
    The formulas surrounded by brackets are array formulas, they have to be entered with Ctrl + Shift + Enter.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  6. 08-04-2016, 01:06 PM #6
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Multiple Vlookup to nearest time

    Try the attached....
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 08-05-2016, 02:49 AM #7
    ronettes
    ronettes is offline
    Registered User
    Join Date
    03-30-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Multiple Vlookup to nearest time

    Quote Originally Posted by Bernie Deitrick View Post
    Try the attached....
    This is me being Amazed!! Yeah I would have never in a Million years came up with that formula - But I do understand it now with the data.

    Thank you soooo much!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Look up value based on nearest time
    By EXPB in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-26-2016, 04:39 AM
  2. Need to find Nearest Time
    By maddy_82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-23-2015, 01:31 AM
  3. Formula to round up time to the nearest given end time.
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2015, 05:43 PM
  4. Vlookup with multiple conditions (One condition need to be set to nearest match)
    By ravikiran in forum Excel General
    Replies: 4
    Last Post: 06-30-2014, 06:29 PM
  5. [SOLVED] Return nearest value to specified time within + or - 1 min.
    By PeanutPete in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-04-2013, 06:47 AM
  6. VLOOKUP to the nearest value
    By BobTheRocker in forum Excel General
    Replies: 8
    Last Post: 10-19-2010, 02:55 PM
  7. Rounding Time Intervals to the Nearest Specified Time Increment
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 12:21 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1