Using MATCH / LOOKUP to find the next highest value.
 

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/

Using MATCH / LOOKUP to find the next highest value.
 

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/

Using MATCH / LOOKUP to find the next highest value.
 

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

"Bhupinder Rayat" wrote in message
...
| 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/
|
|
|

Using MATCH / LOOKUP to find the next highest value.
 

You're missing an ELSE branch

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Max" wrote in message ...
| 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/
|
|
|

Using MATCH / LOOKUP to find the next highest value.
 

"Niek Otten" wrote:
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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Using MATCH / LOOKUP to find the next highest value.
 

That's great, thanks guys!

"Niek Otten" wrote:

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

"Bhupinder Rayat" wrote in message
...
| 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/
|
|
|

Using MATCH / LOOKUP to find the next highest value.
 

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.

--
Biff
Microsoft Excel MVP


"Bhupinder Rayat" wrote in
message ...
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/

Using MATCH / LOOKUP to find the next highest value.
 

On Mon, 18 Feb 2008 04:43:00 -0800, 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/



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

Using MATCH / LOOKUP to find the next highest value.
 

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.

"Max" wrote:

"Niek Otten" wrote:
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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Using MATCH / LOOKUP to find the next highest value.
 

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


On Wed, 26 Mar 2008 08:08:01 -0700, FrankM
wrote:

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.

"Max" wrote:

"Niek Otten" wrote:
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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Using MATCH / LOOKUP to find the next highest value.
 

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

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



"FrankM" wrote:

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.

"Max" wrote:

"Niek Otten" wrote:
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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---