CODEWITHSUNDEEP
arraysexcelexcel-formulamatchexcel-match
Jaakko
Jaakko

Reputation: 21

Excel Match Function Returns Row Outside the Lookup Array

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:

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:

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:

Screen snip showing the result row and location of the formula cells

My apologies for unclear explanation. Please ask if you didn't understand the situation properly.

Upvotes: 1

Views: 488

Answers (1)

A.S.H
A.S.H

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

Related Questions