Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Using MATCH / LOOKUP to find the next highest value.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 64
Default 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/



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
  
Posts: 9,221
Default 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/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,440
Default 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/
|
|
|


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
  
Posts: 9,221
Default 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
---
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 45
Default 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
---
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 913
Default 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
---

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 45
Default 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
---
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,440
Default 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/
|
|
|


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 64
Default 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/
|
|
|



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default 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/





Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
LOOKUP OR INDEX/MATCH TO FIND NUMBER? A.S. Excel Discussion (Misc queries) 3 February 5th 07 10:29 PM
Find, Lookup, Match, can't figure it out. fdebelo Excel Worksheet Functions 3 January 16th 06 07:21 PM
Sum values in multiple sheets using Lookup to find a text match CheriT63 Excel Worksheet Functions 7 December 4th 05 02:33 AM
The match and lookup functions can find literal data but not the same data referenced from a cell Jeff Melvaine Excel Discussion (Misc queries) 3 April 30th 05 01:29 PM
FIND DATA WITHIN DATA (V-OR-H LOOKUP/FIND/MATCH?) Jaladino Excel Worksheet Functions 0 February 22nd 05 11:22 PM


All times are GMT +1. The time now is 07:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement