FIND IF =>

[instereo911 via OfficeKB.com]

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 D25 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

 

[T. Valko]

Try this array formula** :

=INDEX(A2:A5,MATCH(TRUE,D25<=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

 

[Guest]

or without an array

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

Mike

 

[instereo911 via OfficeKB.com]

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

Try this array formula** :

=INDEX(A2:A5,MATCH(TRUE,D25<=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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

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

Try this array formula** :

=INDEX(A2:A5,MATCH(TRUE,D25<=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

 

[T. Valko]

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(D25<=B1,,1),0))

Or:

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

Biff