+ Reply to Thread
Results 1 to 6 of 6

INDEX/MATCH Result Irregularity

  1. #1
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    INDEX/MATCH Result Irregularity

    I have copy/pasted (Values (V)) to Columns A and B in a new sheet (attached). In using an INDEX/MATCH formula, the appropriate results are generated until I plug in the time 7:14 into Cell [D32]. Then #N/A appears. Same for plugging in 7:16. Then, the formula works again for 7:17 and beyond. I have used =D32=A32 to see if values are the same, and they are. Now, if I type 7:14 fresh into cell [A32] the formula works. So, there appears to be something invisible and embedded in the original copy/paste data that interferes. Is there a way to “clean” the original data in cells A32 and A33 to allow the INDEX/Match formula to work? Or am I stuck having to type in the time fresh into these two cells? Many thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: INDEX/MATCH Result Irregularity

    It's down to floating point arithmetic....

    =INDEX($B$2:B42,MATCH($D$32,ROUND(1440*$A$2:A42,0)/1440,0))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: INDEX/MATCH Result Irregularity

    The values in col A are not exact, for instance A32 is not exactly 7:14 as can be seen using this
    =(TIME(7,14,0)-A32)*10^20

    You can get round that like
    =INDEX($B$2:B42,MATCH(ROUND($D$32,10),ROUND($A$2:A42,10),0))

  4. #4
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: INDEX/MATCH Result Irregularity

    Excellent. Thanks for sending the file as I coped the formula stated in the post and it did not work. That was because it is an array. Once I saw that in the file and made the change, it worked great! Many thanks.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: INDEX/MATCH Result Irregularity

    Glad to help & thanks for the feedback.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: INDEX/MATCH Result Irregularity

    Likewise, an apols for forgetting to comment about array entry!

+ 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 more than 1 result available but only displaying the 1st result
    By MarkPr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-23-2018, 04:48 AM
  2. [SOLVED] index match , 0 instead of N/A when no result
    By muss1210 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-22-2017, 01:02 PM
  3. [SOLVED] INDEX/MATCH generates incorrect result by referencng nearest cell if match not found
    By aglawrence in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2017, 08:59 AM
  4. [SOLVED] Index/Match - More than 1 Result
    By LJenny in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-13-2015, 11:08 AM
  5. [SOLVED] Index/Match result should again search for zero result
    By ursanil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 04:45 AM
  6. index match, ..... skip result if blank value ...... jump to next match
    By gehawk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 04:42 AM
  7. [SOLVED] Index Match if result is not something
    By Jaron_t in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 01:19 PM

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