Reputation: 21
I have a problematic application of the MATCH
function in Microsoft Excel.
I'm quite used to using the INDEX-MATCH
combo and have never faced an issue like this before.
The problem is that the row number returned by a MATCH
function is outside the lookup array.
Input data may seem a bit complex:
A
contains datetimes at 10 minute intervals (Example: 08.03.2016 00:00, 08.03.2016 00:10, 08.03.2016 00:20, dd.mm.yyyy hh:mm).B
contains the lookup array, consisting of varying set of values related to each datetime.The list is long, and column B is not the only result value column, which is why I have created some named ranges too.
The problematic function is:
=MATCH(E37;INDEX(MainData;MATCH(E18+E19;DateSelector;0);COLUMN(hex1_cur)):INDEX(MainData;MATCH(E21+E20;DateSelector;0);COLUMN(hex1_cur));1)
Where the named ranges are:
MainData
= All datetime and result cells, approximately A4:D2000
.DateSelector
= Datetime cells (dynamic array, acknowledges the amount of cells in Column A
).hex1_cur
= Identifier for a result column, in this case indicating to Column B
.E18+E19
form starting datetime for evaluation period, and E21+E20
marks the ending point.
E37
has the lookup value, and as you can see, I use match_type 1
, because I want to locate the largest value less than or equal to my lookup.
Why does the function work for the first evaluation period in screen snip (formula is in D38
), but not anymore for the next (E38
)?
Please note that the INDEX-MATCH
array in the lookup array section of the main MATCH search works fine, so the fault shouldn't be in my named ranges.
I use this exactly same structure also for other purposes in this worksheet, and it's been correct so far.
So how can the main MATCH
function in my formula return a row number outside of the INDEX-MATCH
-restricted lookup array?
In this test case, the INDEX-MATCH
lookup array produces accurate location results from row 187
to row 309
, yet the resulting row number is 39
, as in the picture in E38
.
On contrary, row 61
, the result for D38
formula, is accurately within its own INDEX-MATCH
-defined lookup array:
My apologies for unclear explanation. Please ask if you didn't understand the situation properly.
Upvotes: 1
Views: 488
Reputation: 29362
The confusion is in your interpretation of the result of the Match
function. The result is not a row number, but a position relative to the beginning of the submitted array parameter.
In this test case, the INDEX-MATCH lookup array produces accurate location results from row 187 to row 309 - yet the resulting row number is 39
The result 39 in this example case means row 225; that is, 39 away from the beginning of the array.
Upvotes: 1