+ Reply to Thread
Results 1 to 4 of 4

Issue with time in index match

  1. #1
    Registered User
    Join Date
    06-22-2018
    Location
    NYC
    MS-Off Ver
    2016
    Posts
    2

    Issue with time in index match

    I know this is a common issue, I looked around for how to fix but I am still having issues with Index and match using time to look up a value.

    19:40:00 19:40 2
    20:00:00 20:00 1
    20:20:00 20:20 0
    20:40:00 20:40 2
    21:00:00 21:00 0
    21:20:00 21:20 3
    21:40:00 21:40 0
    20:00:00 20:00
    20:00:00 #N/A

    The #N/A is =INDEX(C1:C7,MATCH(B8,A1:A7,0))
    A column is normal time format
    B is =TEXT(A1,"HH:MM")

    Any way I cam reformat this for index match to read the time and get a match.

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

    Re: Issue with time in index match

    You are trying to match text against time values. I haven't created a file from scratch to type in your data but try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-22-2018
    Location
    NYC
    MS-Off Ver
    2016
    Posts
    2

    Re: Issue with time in index match

    So now I get #VALUE
    But I switched some of the functions around. Do you use TIMEVALUE to get times to match up?

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

    Re: Issue with time in index match

    I am using TIMEVALUE to convert the text to time, and then search column B for that time. For more specific help attach an actual Excel file. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

+ 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] Index & Match Issue
    By Terry-J in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-21-2017, 04:39 PM
  2. Issue: Only returning 1st match on Index/Match
    By tbr2891 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2014, 11:54 AM
  3. [SOLVED] index and match issue
    By BusiMan in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-30-2014, 08:23 PM
  4. [SOLVED] Match Issue - Consolidate Columns of Data With Match/Index/etc?
    By excelsior123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2014, 05:14 PM
  5. [SOLVED] index match issue
    By deanusa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2014, 02:40 PM
  6. [SOLVED] index match issue
    By jw01 in forum Excel General
    Replies: 3
    Last Post: 07-19-2012, 12:31 AM
  7. index, match issue
    By step_one in forum Excel General
    Replies: 3
    Last Post: 05-03-2011, 03:40 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1