INDEX + MATCH function returning wrong values

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?

JarrBett

Posted 2015-08-21T19:48:05.370

Reputation: 43

How are the values in col A stored (time values, text, etc.)? – fixer1234 – 2015-08-21T19:53:04.433

Both the column A values and the search value (I4) are formatted as time (ex. 8:00 AM) – JarrBett – 2015-08-21T19:55:35.703

1I wonder if the problem is floating point rounding error. – fixer1234 – 2015-08-21T20:01:45.267

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 – 2015-08-21T20:06:08.403

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 – 2015-08-21T20:10:56.123

    Just on the off chance this is the issue... cells formatted as time may still have a date attached. You won't see it because it's hidden, but MATCH would be affected by that. Maybe temporarily switch the formatting to date & time to double check they're all on the same day? – Ask About Monica – 2015-08-21T20:17:03.930

    I think it must be what fixer1234 said about the floating point rounding problem - what I did was format the interval time column to text (which of course changed all the values to decimal values), and just entered the interval times in again treating them as text only. Everything seems to search correctly now on all interval times. Thank you both for your input, that was driving me nuts! – JarrBett – 2015-08-21T20:30:24.820

    Answers

    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.

    Scott

    Posted 2015-08-21T19:48:05.370

    Reputation: 17 653

    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 – 2015-08-21T20:45:04.073

    That's a lot simpler a solution than I was coming up with. Strange that only those three times had a problem. – fixer1234 – 2015-08-21T21:51:06.867

    @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 – 2015-08-21T22:07:33.383

    @fixer1234: Congratulations on joining Club 5000. – Scott – 2015-08-23T08:13:08.860

    Asked: 2015-08-21T19:48:05.370

    Viewed: 3 209 times

    Active: 2015-08-21T21:06:30.537