I have a spreadsheet in Excel 2013 that uses an
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
The interval time column ranges from
A37 (6:00 AM - 11:30 PM, in half-hour increments), and the values I want returned range from
ALL VALUES return correctly except when searching for values in
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
I4 = 8:30 AM (
B6 instead of
I4 = 10:00 AM (
B9 instead of
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?