Using MATCH / LOOKUP to find the next highest value.

[Bhupinder Rayat]

Hi All,

How can I use MATCH to find the next highest value when there isn't exact
match.

E.g

1-Jan-08
3-Jan-08
4-Jan-08
5-Jan-08

If I match the array to 2-Jan-08, it returns the postion of 1-Jan-08, I want
it to return the 3-Jan-08 position.

The dates have to be in ascending order so changing to descending order and
returning match_type -1 is not an option.

Any ideas?

Thanks in advance.

Regards,

B/

 

[Max]

Assuming dates range in col A,
with lookup dates listed in C1 down

Put in D1, copied down:
=IF(ISNA(MATCH(C1,A:A,0)),MATCH(C1,A:A)+1)

 

[Niek Otten]

Table in A1:A21
Lookup date in B1
In C1:
=MATCH(B1,A1:A21)
In D1:
=IF(INDEX(A1:A21,C1)=B1,C1,C1+1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi All,
|
| How can I use MATCH to find the next highest value when there isn't exact
| match.
|
| E.g
|
| 1-Jan-08
| 3-Jan-08
| 4-Jan-08
| 5-Jan-08
|
| If I match the array to 2-Jan-08, it returns the postion of 1-Jan-08, I want
| it to return the 3-Jan-08 position.
|
| The dates have to be in ascending order so changing to descending order and
| returning match_type -1 is not an option.
|
| Any ideas?
|
| Thanks in advance.
|
| Regards,
|
| B/
|
|
|

 

[Niek Otten]

You're missing an ELSE branch

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Assuming dates range in col A,
| with lookup dates listed in C1 down
|
| Put in D1, copied down:
| =IF(ISNA(MATCH(C1,A:A,0)),MATCH(C1,A:A)+1)
| --
| Max
| Singapore
| http://savefile.com/projects/236895
| xdemechanik
| ---
| "Bhupinder Rayat" wrote:
| > Hi All,
| >
| > How can I use MATCH to find the next highest value when there isn't exact
| > match.
| >
| > E.g
| >
| > 1-Jan-08
| > 3-Jan-08
| > 4-Jan-08
| > 5-Jan-08
| >
| > If I match the array to 2-Jan-08, it returns the postion of 1-Jan-08, I want
| > it to return the 3-Jan-08 position.
| >
| > The dates have to be in ascending order so changing to descending order and
| > returning match_type -1 is not an option.
| >
| > Any ideas?
| >
| > Thanks in advance.
| >
| > Regards,
| >
| > B/
| >
| >
| >

 

[Max]

You're missing an ELSE branch

Thanks, yes

It should have read as:

In D1, copied down:
=IF(ISNA(MATCH(C1,A:A,0)),MATCH(C1,A:A)+1,MATCH(C1,A:A,0))

 

[Bhupinder Rayat]

That's great, thanks guys!

 

[T. Valko]

How can I use MATCH to find the next highest value

What if there is no next highest value? For example, based on your sample,
what if the lookup_value was 6-Jan-08? In this case there is no next highest
value.

 

[Ron Rosenfeld]

Hi All,

How can I use MATCH to find the next highest value when there isn't exact
match.

E.g

1-Jan-08
3-Jan-08
4-Jan-08
5-Jan-08

If I match the array to 2-Jan-08, it returns the postion of 1-Jan-08, I want
it to return the 3-Jan-08 position.

The dates have to be in ascending order so changing to descending order and
returning match_type -1 is not an option.

Any ideas?

Thanks in advance.

Regards,

B/

With your lookup date in E1, and rng representing where your dates are located:


=INDEX(rng,MATCH(TRUE,E1<=rng,FALSE))

entered as an **array** formula with <ctrl><shift><enter>
--ron

 

[FrankM]

This is awesome. It is giving me just what I need to. One question though. Is
it possible to have the date returned rather than the position? I would like
to get 06-08-2008 instead of 6.

 

[Lars-Åke Aspelin]

Just wrap an INDEX function around your function, like

=INDEX(A:A,IF(ISNA(MATCH(C1,A:A,0)),MATCH(C1,A:A)+1,MATCH(C1,A:A,0)))

Hope this helps / Lars-Åke

 

[FrankM]

I just got it to work using the following ...

'=INDEX(B1:B24,IF(ISNA(MATCH(A1,B1:B24,0)),MATCH(A1,B1:B24)+1,MATCH(A1,B1:B24,0)))