+ Reply to Thread
Results 1 to 7 of 7

VLOOKUP help

  1. #1
    Registered User
    Join Date
    05-19-2016
    Location
    China
    MS-Off Ver
    2013
    Posts
    12

    VLOOKUP help

    Hello,

    I have to make a pivot table based on time in half hour intervals, but I'm having to manually enter in the time window, so I want to automate this process.

    How do I get the "start time" section in column B put into 30 min time intervals like in column C?

    I've tried doing it with VLOOKUP but I keep getting #N/A, unless copy and paste the the date from the "Test" tab as highlighted in red.

    I attached the file I'm working on.

    Dave
    Attached Files Attached Files
    Last edited by Chasermelb; 03-06-2018 at 04:00 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: VLOOKUP help

    TRy

    =VLOOKUP(TIME(HOUR(B2),MINUTE(B2),SECOND(B2)),Test!$A$2:$B$10000,2,1)

  3. #3
    Registered User
    Join Date
    12-04-2017
    Location
    Hanoi
    MS-Off Ver
    MS Office 2013
    Posts
    11

    Re: VLOOKUP help

    Try with formula below:
    Please Login or Register  to view this content.
    Ctrl+Shift+Enter
    Fill down

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: VLOOKUP help

    D2=VLOOKUP(MOD(B2,1),Test!A$2:B$7781,2,1)
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: VLOOKUP help

    Or try:

    =LOOKUP(MOD(B2,1),Test!A$2:B$7781)

  6. #6
    Registered User
    Join Date
    05-19-2016
    Location
    China
    MS-Off Ver
    2013
    Posts
    12

    Re: VLOOKUP help

    Thanks John, this worked perfectly

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VLOOKUP help

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. Replies: 5
    Last Post: 12-07-2016, 09:18 AM
  2. Using vLookup based on results from a vLookup & returning an undetermined list
    By NormalityBan in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-14-2014, 05:02 AM
  3. Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  4. Display Cell within VLookup range that excel thinks matches the VLookup Value
    By headachexcelperson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 04:56 PM
  5. [SOLVED] Vlookup is not working and giving =vlookup(B2,$T$2:$U$135,2,false) this kind of values.
    By yogeshsharma1981 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2013, 04:08 PM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Replies: 5
    Last Post: 07-29-2009, 07:53 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