Why Doesn't This MATCH Work - Array Question

[kcc]

I'm trying to do a match to find a number equal or larger than
an input value. Since the values in the table are ascending I
need to reverse them to use a -1 Match type.
I've got a half dozen tricks that work. What I would like to
find out is why one in particular doesn't work.
A simplified example:
A1:J1 contains the values 2, 4,...,20.
A2 is the look up value. (eg 15)
A3 is =MATCH(A2,OFFSET(J1,0,1-ROW(Count!1:10)),-1)
where count is a blank sheet to return the numbers from 1 to 10.
I can see that the offset returns the array in reverse order which
is what I want. The Match however returns 1 for any A2<=20
and #N/A otherwise, which indicates to me that only the first
element of the offset array is passed to the Match.
Entering it as an array formula doesn't seem to effect how it works.

MATCH(15,{20;18;16;14;12;10;8;6;4;2},-1) returns 3 as I
would expect and the components of my formula evaluate to
the same values so I would think I would get the same result.

Any ideas that do not require sorting, reserving the array into cells
first, or doing a Type 1 match and adding 1 would also be welcome.

Thanks
kcc

 

[Peo Sjoblom]

Here's a way to return equal or nearest greater value sorted or not

=MATCH(SMALL(A1:J1,COUNTIF(A1:J1,"<"&A2)+1),A1:J1,0)


--
Regards,

Peo Sjoblom

(No private emails please)

 

[Alan Beban]

Is =10-MATCH(A2,A1:J1,1) helpful?

Alan Beban

 

[kcc]

Peo, thanks that does the trick for the immediate need.

Alan, the reason I'm trying to avoid a type 1 match is because
of a detail I left out. The list in reality is only partially sorted
from the right end though one cell past the one I need.
From that point to the left the order is random, so
unfortunately your formula won't work.

If anyone can explain why my formula doesn't work I would appreciate it.
Thanks
kcc

 

[Peo Sjoblom]

MATCH and some other functions (INDIRECT is one of them) can't evaluate an
array like that even though if passed as {20;18;16etc
it will work, it needs a second evaluation, wrapped in N this will return 3
using your setup

=MATCH(A2,N(OFFSET(J1,0,1-ROW(Count!1:10))),-1)

needs to be array entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please)

 

[kcc]

I have never used N() since excel will generally do conversions
automatically, and even if I had, I don't think converting a number
to a number would have occurred to me. This sort of
inconsistency in excel really bugs me. SUM applied to the offset
works fine, so at some level excel understands that the array is
made up of numbers.
This one is definitely getting saved in my bag of tricks.
Thanks