Less Than MATCH()

[bgetson]

Is there any way to use the MATCH() function to grab only values that
are less than the lookup value (instead of <=)? I've considered using
an IF() with ISNA(MATCH(**,**,0)) to setup separate conditions, but
I'm afraid of what it will to do my already long processing time.

Any help would be appreciated.

 

[Guest]

Try this:

=MATCH(**,**,1)

 

[Guest]

Make sure your data is sorted in Ascending order

 

[bgetson]

MATCH(**,**,1) returns the largest value that is less than **or equal
to** the lookup value. I'm trying to find the closest value that is
only less than my lookup. "-1" as the final parameter would probably
work instead, but I can't sort my data in descending order.

 

[Guest]

How about subtracting one from the lookup value?
e.g =MATCH(I2-1,G3:G12) assuming that the lookp array is sorted

Peter

 

[D Hilberg]

=SUMPRODUCT( MAX( (A1:A10<YourValue)*A1:A10) )

- David Hilberg

 

[bgetson]

Thanks for the help. I think I've got it figured out now.