# INDEX + MATCH function returning wrong values

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
• 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) 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

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