Excel "MATCH" function

[jeanne]

"MATCH" will pick the smallest number, in decending
order, that is greater than a designated number. However,
when the column of numbers are not all in decending order
it will assume the column ends when the next number is
larger. See below;

Need to pick first number from bottom of column larger
than 5.7, which is 6.2.

MATCH starts from top and chooses 6.1. It assumes column
ends at 5.0.

8.0
7.4
6.1
5.0 Column ends here
6.2
4.0
3.3

I have several spreadsheets with this problem that would
be very time consuming to change the function. However,
if there is no solution, what function should I use?

 

[Mark Graesser]

Jeanne,
Check out Chip Pearson's sit at:

http://www.cpearson.com/excel/lookups.htm#ClosestMatch

Good Luck,
Mark Graesser
(e-mail address removed)

----- jeanne wrote: -----

"MATCH" will pick the smallest number, in decending
order, that is greater than a designated number. However,
when the column of numbers are not all in decending order
it will assume the column ends when the next number is
larger. See below;

Need to pick first number from bottom of column larger
than 5.7, which is 6.2.

MATCH starts from top and chooses 6.1. It assumes column
ends at 5.0.

8.0
7.4
6.1
5.0 Column ends here
6.2
4.0
3.3

I have several spreadsheets with this problem that would
be very time consuming to change the function. However,
if there is no solution, what function should I use?

 

[Peo Sjoblom]

I doubt that it will help since it is based on logic, there is nothing logic
in the way the OP wants this to work
Logic says that either 5.0 or 6.1 should be picked, not 6.2
If she sorted it in descending order and took off 1 from match it would find
6.2

=MATCH(5.7,Range,-1)-1

 

[Harlan Grove]

"MATCH" will pick the smallest number, in decending
order, that is greater than a designated number. However,
when the column of numbers are not all in decending order
it will assume the column ends when the next number is
larger. See below;

Need to pick first number from bottom of column larger
than 5.7, which is 6.2.

MATCH starts from top and chooses 6.1. It assumes column
ends at 5.0.

8.0
7.4
6.1
5.0 Column ends here
6.2
4.0
3.3

I have several spreadsheets with this problem that would
be very time consuming to change the function. However,
if there is no solution, what function should I use?

If all these formulas pull from the same ranges, then you could sort those
ranges in descending order. That's the only quick fix that avoids needing to
edit formulas. If you need to maintain the existing order in these ranges, then
you have no alternative to changing all formulas, replacing

MATCH(SomeValue,SomeRange,-1)

with

=MATCH(MIN(IF(SomeRange>SomeValue,SomeRange)),SomeRange,0)

 

[Harlan Grove]

Reposted with fixed subject line. Sorry.

"MATCH" will pick the smallest number, in decending
order, that is greater than a designated number. However,
when the column of numbers are not all in decending order
it will assume the column ends when the next number is
larger. See below;

Need to pick first number from bottom of column larger
than 5.7, which is 6.2.

MATCH starts from top and chooses 6.1. It assumes column
ends at 5.0.

8.0
7.4
6.1
5.0 Column ends here
6.2
4.0
3.3

I have several spreadsheets with this problem that would
be very time consuming to change the function. However,
if there is no solution, what function should I use?

If all these formulas pull from the same ranges, then you could sort those
ranges in descending order. That's the only quick fix that avoids needing to
edit formulas. If you need to maintain the existing order in these ranges, then
you have no alternative to changing all formulas, replacing

MATCH(SomeValue,SomeRange,-1)

with

=MATCH(MIN(IF(SomeRange>SomeValue,SomeRange)),SomeRange,0)

 

[Mark Graesser]

Harlan

=MATCH(MIN(IF(SomeRange>SomeValue,SomeRange)),SomeRange,0

If this is arrayed entered it will find the lowest number greater than the SomeValue

It appeares Jeanne is looking for a number greater than the SomeValue that is positioned lowest in the list

I provided one solution under Peo's post

Regards
Mark Graesse
(e-mail address removed)

 

[Harlan Grove]

...
...

=MATCH(MIN(IF(SomeRange>SomeValue,SomeRange)),SomeRange,0)

...

@#$%! Make that

=MATCH(MIN(IF(SomeRange>=SomeValue,SomeRange)),SomeRange,0)

 

[Harlan Grove]

If this is arrayed entered it will find the lowest number greater than the
SomeValue.

It appeares Jeanne is looking for a number greater than the SomeValue that
is positioned lowest in the list.

...

Yup, I misread the problem.

Your formula only works when the range begins in row 1.

 

[Mark Graesser]

OK here is the definitive formula. (I think

=INDEX(A1:A7,LARGE((A1:A7>=B1)*(ROW(A1:A7)),1)-ROW(A1:A7)+1

-Array entere
-A1:A7 can be replaced with the appropriate range. (4 times
-B1 can be replaced with the cell reference of the lookup valu


----- Harlan Grove wrote: ----

is positioned lowest in the list

..

Yup, I misread the problem

Your formula only works when the range begins in row 1

-
To top-post is human, to bottom-post and snip is sublime