Good morning,

I have a document I need help on.

A B C D E
1 5
2 01/01/2007 7
3 01/02/2007 6
4 01/03/2007 5.5
5 01/04/2007 4.2

I want to say "Find the first value that is either less than or equal to b1
in D2:D5 and play what the date is on cell E1"

Hopefully that makes sense.

I tried VLOOKUP, didn't make sense, tried Find ... didn't work. I am lost and
a noob on excel.

Thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1

Try this array formula** :

=INDEX(A2:A5,MATCH(TRUE,D2:D5<=B1,0))

I'm assuming there are no empty cells in column D.

Format the result as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"instereo911 via OfficeKB.com" <u27979@uwe wrote in message
news:73bf3b94fbb76@uwe...
Good morning,

I have a document I need help on.

A B C D E
1 5
2 01/01/2007 7
3 01/02/2007 6
4 01/03/2007 5.5
5 01/04/2007 4.2

I want to say "Find the first value that is either less than or equal to
b1
in D2:D5 and play what the date is on cell E1"

Hopefully that makes sense.

I tried VLOOKUP, didn't make sense, tried Find ... didn't work. I am lost
and
a noob on excel.

Thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1

or without an array

=OFFSET(D2,MATCH(B1,$D$2:$D$5,0)-1,-3,1,1)

Mike

"instereo911 via OfficeKB.com" wrote:

Good morning,

I have a document I need help on.

A B C D E
1 5
2 01/01/2007 7
3 01/02/2007 6
4 01/03/2007 5.5
5 01/04/2007 4.2

I want to say "Find the first value that is either less than or equal to b1
in D2:D5 and play what the date is on cell E1"

Hopefully that makes sense.

I tried VLOOKUP, didn't make sense, tried Find ... didn't work. I am lost and
a noob on excel.

Thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1

Worked Like a charm ! Thank you so very much guys -

T. Valko wrote:
Try this array formula** :

=INDEX(A2:A5,MATCH(TRUE,D2:D5<=B1,0))

I'm assuming there are no empty cells in column D.

Format the result as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

Good morning,

[quoted text clipped - 18 lines]

Thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1

You're welcome. Thanks for the feedback!

Biff

"instereo911 via OfficeKB.com" <u27979@uwe wrote in message
news:73bfa7fcaa406@uwe...
Worked Like a charm ! Thank you so very much guys -

T. Valko wrote:
Try this array formula** :

=INDEX(A2:A5,MATCH(TRUE,D2:D5<=B1,0))

I'm assuming there are no empty cells in column D.

Format the result as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

Good morning,

[quoted text clipped - 18 lines]

Thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1

or without an array
=OFFSET(D2,MATCH(B1,$D$2:$D$5,0)-1,-3,1,1)

That would only work for an exact match.

Non-array version:

=INDEX(A2:A5,MATCH(TRUE,INDEX(D2:D5<=B1,,1),0))

Or:

=INDEX(A2:A5,MATCH(1,INDEX(--(D2:D5<=B1),,1),0))

Biff

"Mike H" wrote in message
...
or without an array

=OFFSET(D2,MATCH(B1,$D$2:$D$5,0)-1,-3,1,1)

Mike

"instereo911 via OfficeKB.com" wrote:

Good morning,

I have a document I need help on.

A B C D E
1 5
2 01/01/2007 7
3 01/02/2007 6
4 01/03/2007 5.5
5 01/04/2007 4.2

I want to say "Find the first value that is either less than or equal to
b1
in D2:D5 and play what the date is on cell E1"

Hopefully that makes sense.

I tried VLOOKUP, didn't make sense, tried Find ... didn't work. I am lost
and
a noob on excel.

Thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200706/1