in range?

[Bruce Bowler]

(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
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Bernd P]

Hello Bruce,

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

Regards,
Bernd

 

[Bruce Bowler]

Hello Bruce,

Array-enter into B1:
=INDEX(E:E,MATCH(1,(A1>=$C$1:$C$99)*(A1<=$D$1:$D$99),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

 

[Bernd P]

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

 

[Bruce Bowler]

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
(e-mail address removed) | action. - Anonymous
+-------------------+---------------------------------------------------+