excel - Does INDEX-MATCH lookup system have limitations with ascending/descending lists? -

excel - Does INDEX-MATCH lookup system have limitations with ascending/descending lists? -

solved stated @ 1st comment link, -1 [match_type] must used ascending order list, , viceversa 1, requires descending order list.

tl;dr summary: [match_type] entry match function seems variable must come in depending on whether list of lookup numbers ascending or descending, case , hence limitation or using wrong?

i trying evaluate readings measurements. evaluation must rely on theoretical values calculated compare actual values perfect situation. have defined table calculated values. utilize index-match scheme lookup closest theoretical value (for comparison) experimental one.

fyi index-match structured follows:

=index(return_value_range, match(lookup_value, lookup_value_range, [match_type]))

below illustration of origin of table:

b c 1 c o zr 2 5.707 7.602 86.691 3 5.719 7.587 86.694 4 5.730 7.572 86.697 5 5.742 7.557 86.701 6 5.753 7.543 86.704 7 5.765 7.528 86.707 8 5.777 7.513 86.711 [ascending] [descending] [descending]

here illustration of values:

d e f g h 1 run o c zr (o index) zr (c index) 2 1 2.90 9.23 [formula] [formula] 3 2 3.57 9.72 [formula] [formula] 4 3 2.86 9.45 [formula] [formula]

so formula lookup zr value in cell g2 based off of experimental o value in e2 follows

=index(b2:bxxx,match(e2,c2:cxxx,-1))

this returns values correctly here seems run inherent limitation: formula in e2 must set [match_type] value @ end of match function -1 otherwise returns me either #n/a value.

similarly case when entering same formula lookup zr value based off of experimental c value in cell h2, except [match_type] value has +1 follows:

=index(a2:axxx,match(f2,c2:cxxx,1))

the [match_type] part of match function can have 3 value, explained here, are:

0 exact match. -1 nearest greater match. 1 nearest less match.

my conclusion have take [match_type] based off whether lookup_value_range list either ascending or descending.

question: observation limitation of scheme or wrong utilize of mine of technique?

excel indexing match lookup-tables