(I hope the following is clear, but it's probably not)

Is it possible to construct a formula (I know I could do it in a macro,
but for various reasons, that's not practical in this case) to do the
following?

C1 = 123, D1 = 456, E1 = 1.234
C2 = 512, D2 = 999, E2 = 3.457

If A{n} is between C{x} and D{x} then B{n} = E{x}

{n} varies from 1 to perhaps 10,000. {x} varies from 1 to perhaps 10.

Thanks
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | Dorothy Parker once described 'eternity' as a ham
1.207.633.9600 | and two people. - Erma Bombeck
|
+-------------------+---------------------------------------------------+

Hello Bruce,

Array-enter into B1:
=INDEX(E:E,MATCH(1,(A1=$C$1:$C$99)*(A1<=$D$1:$D$9 9),0))
and copy down.

Regards,
Bernd

On Tue, 05 May 2009 13:37:11 -0700, Bernd P wrote:

Hello Bruce,

Array-enter into B1:
=INDEX(E:E,MATCH(1,(A1=$C$1:$C$99)*(A1<=$D$1:$D$9 9),0)) and copy down.

Regards,
Bernd

That doesn't seem to work. I'm running 2003, is that a 2007 enhancement?

The part the "fails" is the A1=$C$1:$C$99 (and the <= case too), it
returns #value.

Having said that, it turns out that in my case

=INDEX(E:E,MATCH(A1,C:C,1))

does what I need, largely because the ranges in C and D will never
overlap (I know, 'never' is a dangerous statement :-)

Thanks for the pointer.
Bruce

Hello Bruce,

I tested in Excel 2002 and 2007. It works fine.

Are you sure that you "array-entered" my formula? - that is:
Entering with CTRL + SHIFT + ENTER, not just ENTER

Regards,
Bernd

On Wed, 06 May 2009 08:53:06 -0700, Bernd P wrote:

Hello Bruce,

I tested in Excel 2002 and 2007. It works fine.

Are you sure that you "array-entered" my formula? - that is: Entering
with CTRL + SHIFT + ENTER, not just ENTER

Regards,
Bernd

Ahhh... Missed that in your original post. Works fine now. Thanks!



--
+-------------------+---------------------------------------------------+
Bruce Bowler | GUI's normally make it simple to accomplish simple
1.207.633.9600 | actions and impossible to accomplish complex
| action. - Anonymous
+-------------------+---------------------------------------------------+