In an excel spreadsheet as below: Cell A1 = 53.25. I need a formula in cell
B1 that would find where the 53.25 would fall within columns C and D with a
result from column E. As in 53.25 falls between 46 and 55, so the resulting
'3' would be the answer I need in cell B1. Is there such a formula? I'm
sorry I'm not explaining this scenario well, but I would be forever grateful
for any help.

A B C D E
1 53.25 _____ 26 35 1
2 36 45 2
3 46 55 3
4 56 65 4

one way:

=SUM(IF(($A$1=$C$1:$C$4)*($A$1<=$D$1:$D$4),$E$1:$ E$4,))

this is an array-formula so insert it with CTRL+SHIFT+ENTER

You could also use:
=VLOOKUP(A1,C1:E4,3,TRUE)
--
John C


"DebC" wrote:

In an excel spreadsheet as below: Cell A1 = 53.25. I need a formula in cell
B1 that would find where the 53.25 would fall within columns C and D with a
result from column E. As in 53.25 falls between 46 and 55, so the resulting
'3' would be the answer I need in cell B1. Is there such a formula? I'm
sorry I'm not explaining this scenario well, but I would be forever grateful
for any help.

A B C D E
1 53.25 _____ 26 35 1
2 36 45 2
3 46 55 3
4 56 65 4

in this particular situation also the following works:

=LOOKUP(A1,$C$1:$C$4,E1:E4)

for 10, 27, 37, 55 etc.

and I don't know why

;-)))

On Wed, 13 Aug 2008 08:23:00 -0700, DebC
wrote:

In an excel spreadsheet as below: Cell A1 = 53.25. I need a formula in cell
B1 that would find where the 53.25 would fall within columns C and D with a
result from column E. As in 53.25 falls between 46 and 55, so the resulting
'3' would be the answer I need in cell B1. Is there such a formula? I'm
sorry I'm not explaining this scenario well, but I would be forever grateful
for any help.

A B C D E
1 53.25 _____ 26 35 1
2 36 45 2
3 46 55 3
4 56 65 4

Given just what you post, all you need is column D:

26
36
46
56

and this formula:

=IF(OR(A1<26,A165),"",MATCH(A1,D1:D4))

--ron

Thank you so much for responding...actually when I entered the formula, it
gave me the result of 2, and should have been 3. Have I done something
wrong?

"Ron Rosenfeld" wrote:

On Wed, 13 Aug 2008 08:23:00 -0700, DebC
wrote:

In an excel spreadsheet as below: Cell A1 = 53.25. I need a formula in cell
B1 that would find where the 53.25 would fall within columns C and D with a
result from column E. As in 53.25 falls between 46 and 55, so the resulting
'3' would be the answer I need in cell B1. Is there such a formula? I'm
sorry I'm not explaining this scenario well, but I would be forever grateful
for any help.

A B C D E
1 53.25 _____ 26 35 1
2 36 45 2
3 46 55 3
4 56 65 4

Given just what you post, all you need is column D:

26
36
46
56

and this formula:

=IF(OR(A1<26,A165),"",MATCH(A1,D1:D4))

--ron

Thank you!! This actually worked. I'm not at all sure I understand the
formula, but it gave me the desired result. I appreciate it very much.

"Jarek Kujawa" wrote:

one way:

=SUM(IF(($A$1=$C$1:$C$4)*($A$1<=$D$1:$D$4),$E$1:$ E$4,))

this is an array-formula so insert it with CTRL+SHIFT+ENTER

and this one worked too. One thing I found with this one, is that if I
replace the 53.25 with a larger number thatn the formula covers, I get a
perpetual "4" for my answer. Smaller #s return N/A, which I expected.
Again, I thank you and everyone who has taken time to help me.

"Jarek Kujawa" wrote:

in this particular situation also the following works:

=LOOKUP(A1,$C$1:$C$4,E1:E4)

for 10, 27, 37, 55 etc.

and I don't know why

;-)))

On Wed, 13 Aug 2008 11:12:23 -0700, DebC
wrote:

Thank you so much for responding...actually when I entered the formula, it
gave me the result of 2, and should have been 3. Have I done something
wrong?

Perhaps you entered the table reference incorrectly?

Here is my table, and the cell references:

D1 26
D2 36
D3 46
D4 56


Here is the formula:

=IF(OR(A1<26,A165),"",MATCH(A1,D1:D4))

With

A1: 53.25

the formula -- 3
--ron

yep, that's what I did! thanks, again

"Ron Rosenfeld" wrote:

On Wed, 13 Aug 2008 11:12:23 -0700, DebC
wrote:

Thank you so much for responding...actually when I entered the formula, it
gave me the result of 2, and should have been 3. Have I done something
wrong?

Perhaps you entered the table reference incorrectly?

Here is my table, and the cell references:

D1 26
D2 36
D3 46
D4 56


Here is the formula:

=IF(OR(A1<26,A165),"",MATCH(A1,D1:D4))

With

A1: 53.25

the formula -- 3
--ron