Really need help with formula(Excel Office 2003)

[DebC]

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

 

[Jarek Kujawa]

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

 

[John C]

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

 

[Jarek Kujawa]

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

;-)))

 

[Ron Rosenfeld]

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,A1>65),"",MATCH(A1,D14))

--ron

 

[DebC]

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?

 

[DebC]

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.

 

[DebC]

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.

 

[Ron Rosenfeld]

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,A1>65),"",MATCH(A1,D14))

With

A1: 53.25

the formula --> 3
--ron

 

[DebC]

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

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,A1>65),"",MATCH(A1,D14))

With

A1: 53.25

the formula --> 3
--ron