4

I have a spreadsheet in Excel 2013 that uses an INDEX and MATCH function to search by an interval time (in I4) to pull values from the corresponding row where it is matched (in column A). It works as expected on all rows except three – on those problem rows it actually returns the value from the cell above.

I am using the code:

=INDEX(B:B, (MATCH($I$4, A:A)))

The value I use to search for interval time is located in cell I4 (e.g., 10:00 AM), and the column it is matched to is A:A. The values I am trying to return are located in B:B.

The interval time column ranges from A2-A37 (6:00 AM - 11:30 PM, in half-hour increments), and the values I want returned range from B2-B37.

ALL VALUES return correctly except when searching for values in A4, A7, and A10 (7:00 AM, 8:30 AM, and 10:00 AM, respectively), which display data from the cell above the desired value (i.e., when I4 = 7:00 AM (which equals A4), the formula returns the value of B3 instead of B4, I4 = 8:30 AM (A7) → B6 instead of B7, and I4 = 10:00 AM (A10) → B9 instead of B10).

When I try to use an exact match:

=INDEX(B:B, (MATCH($I$4, A:A, 0)))

it returns the value #N/A for the problem rows, and correct values for only some of the others.

Any insight into what is happening?

  • How are the values in col A stored (time values, text, etc.)? – fixer1234 Aug 21 '15 at 19:53
  • Both the column A values and the search value (I4) are formatted as time (ex. 8:00 AM) – JarrBett Aug 21 '15 at 19:55
  • 1
    I wonder if the problem is floating point rounding error. – fixer1234 Aug 21 '15 at 20:01
  • the values I am trying to return (column B:B) are formatted as numbers with 2 decimal points. even when I use whole numbers with no decimal points it does the same thing, as well as using text - those 3 cells always return the value from the cell above – JarrBett Aug 21 '15 at 20:06
  • 1
    1) The values that matter are in col A (that's what's being compared to select the row). 2) If they are stored as times, the values are stored as decimal fraction of a day and then formatted for display purposes. – fixer1234 Aug 21 '15 at 20:10
4

I believe that @fixer1234 is correct — it's floating point rounding error on the time values.  I don't fully understand what's happening, but I was able to reproduce it on my system, and I found a work-around:  Change your formula to

=INDEX(B:B, MATCH($I$4+TIME(0,0,1), A:A))

This adds one second (TIME(0,0,1); the arguments are TIME(hours,minutes,seconds)) to the I4 value; that seems to be enough to get it "over the hump", so that it tests as being ≥ the value in A4 (or A7 or A10).  BTW, I tried TIME(0,0,0.9), but apparently TIME() won't honor fractional seconds, and so it just treats that as TIME(0,0,0); i.e., just plain zero.  If you want to get a millisecond, you can use TIME(0,0,1)*0.001.

P.S. You had an unnecessary pair of parentheses in your formula; I removed it, above.

  • that answer works for what I need it to do, and is an easy edit to my sheets that use the formula. thank you so much! – JarrBett Aug 21 '15 at 20:45
  • That's a lot simpler a solution than I was coming up with. Strange that only those three times had a problem. – fixer1234 Aug 21 '15 at 21:51
  • @fixer1234: Yeah; as I said, I don't fully understand what's happening.  $I$4<A7 evaluates to FALSE, $I$4=A7 evaluates to TRUE, and $I$4-A7 evaluates to 0, – Scott Aug 21 '15 at 22:07
  • @fixer1234: Congratulations on joining Club 5000. – Scott Aug 23 '15 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.