Looking fro right LOOKUP formula. Who is the formula expert???

W

Wim

I have a list of values NOT in ascending or descending order. I am
looking for a formula that looks for the first cell in the list above
that has a value that is the same or higher than the look-up value.
I use it for the following: I have a list of share values per week
(Column A: Date; Column B: Value). In column C I want the date in the
past that was equal or just lower than the today's value ("The last
time the share price was so high was on 12 March 2003!!!"). I can't
come up with a smart formula that would do the job. The Lookup formula
looks from the top down, if I'm well informed. I need a formula that
looks from the bottom UP!
Help.
Wim
 
T

T. Valko

These statements seem to contradict each other:
I am looking for a formula that looks for...a value that
is the same or higher than the look-up value.
Click to expand...
I want the date in the past that was equal or
just lower than the today's value
Click to expand...

Maybe a sample would help.

Biff
 
W

Wim

Sample:
Date Value Previous date with same of lower value
20 Nov 06 218.40
27 Nov 06 217.59
04 Dec 06 206.08
11 Dec 06 208.93
18 Dec 06 212.03
27 Dec 06 212.53
02 Jan 07 213.14
08 Jan 07 214.77
15 Jan 07 215.01
22 Jan 07 216.68
29 Jan 07 217.64 Result of formula should be: 27 Nov 06, being
the most recent date that had a value just below the current value
while 20 Nov 06 had a value just higher than the current value...
 
B

Bob Phillips

=INDEX($A$2:$A12,MATCH(TRUE,$B$2:$B11<$B12,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
W

Wim

=INDEX($A$2:$A12,MATCH(TRUE,$B$2:$B11<$B12,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.
Click to expand...

Great that works fantastic in my situation.
Wim
 
B

Bernd

Hello Wim,

What happens if you have a longer series with higher and lower (and
even equal) values than your current one?

Overwrite your 2-Jan-2007 value with 218 or with 217.64, for example.

I think your solution is more like:
=INDEX($A$2:$A11,1-($B12=INDEX($B$2:$B11,LOOKUP(2,1/($B$2:$B11>=
$B12),ROW(1:1000))))+LOOKUP(2,1/($B$2:$B11>=$B12),ROW(1:1000)))

Regards,
Bernd
 
B

Bernd

Hello Wim,

What happens if you have a longer series with higher and lower (and
even equal) values than your current one?

Overwrite your 2-Jan-2007 value with 218 or with 217.64, for example.

I think your solution is more like:
=INDEX($A$2:$A11,1-($B12=INDEX($B$2:$B11,LOOKUP(2,1/($B$2:$B11>=
$B12),ROW(1:1000))))+LOOKUP(2,1/($B$2:$B11>=$B12),ROW(1:1000)))

Regards,
Bernd
 
W

Wim

Hello Wim,

What happens if you have a longer series with higher and lower (and
even equal) values than your current one?

Overwrite your 2-Jan-2007 value with 218 or with 217.64, for example.

I think your solution is more like:
=INDEX($A$2:$A11,1-($B12=INDEX($B$2:$B11,LOOKUP(2,1/($B$2:$B11>=
$B12),ROW(1:1000))))+LOOKUP(2,1/($B$2:$B11>=$B12),ROW(1:1000)))

Regards,
Bernd
Click to expand...

Well done. Gee guys you are BRIGHT!!!!
 
B

Bernd

Hi Wim,

I left that part as an exercise: row($1:$1000) would make it
constant :)

Have fun,
Bernd
 
W

Wim

Hi Wim,

I left that part as an exercise: row($1:$1000) would make it
constant :)

Have fun,
Bernd
Click to expand...

This "Bernd" formula returns an error when the value is lower that
value in the previous row. Bernd, I wait for your fine-tuning of this
formula!
Wim
 
B

Bernd

Hi Wim,

If the current value is less than the previous one then the current
date shows up (as per definition of your request). Just increase $A11
to $A12:

=INDEX($A$2:$A12,1-($B12=INDEX($B$2:$B11,LOOKUP(2,1/($B$2:$B11>=
$B12),ROW($1:$1000))))+LOOKUP(2,1/($B$2:$B11>=$B12),ROW($1:$1000)))

But: If you need the previous occurrence and not the current date in
this case it's time to think about a small and cute macro solution.
Don't trust any formula which exceeds 1 line and which you do not
understand thoroughly.

Regards,
Bernd
 
Top