Function help requested please

[cinnie]

Hello to all

I have worked with functions in other programs, but I am totally new to
Excel. Here's my situation - I have a table like the following that extends
for 10 rows.

A B C
1 0 50 700
2 50 100 1600
3 100 150 2500
4 150 200 3400
etc...

F G
20 174 =???



I want a function in cell G20 that takes the value in F20 (in this example,
174), then determines what range that number falls in in columns A & B (in
this case, 174 is between A4 and B4), then returns the corresponding value
from C4 (in this case, 3400). I should mention as well that the range A4 to
B4, for example, means greater than or equal to A4, but strictly less than B4.

Much thanks for any help!

 

[Fred Smith]

You want Vlookup, as in:
=vlookup(f20,a1:c10,3,true)

Regards,
Fred

 

[Ron Rosenfeld]

Hello to all

I have worked with functions in other programs, but I am totally new to
Excel. Here's my situation - I have a table like the following that extends
for 10 rows.

A B C
1 0 50 700
2 50 100 1600
3 100 150 2500
4 150 200 3400
etc...

F G
20 174 =???



I want a function in cell G20 that takes the value in F20 (in this example,
174), then determines what range that number falls in in columns A & B (in
this case, 174 is between A4 and B4), then returns the corresponding value
from C4 (in this case, 3400). I should mention as well that the range A4 to
B4, for example, means greater than or equal to A4, but strictly less than B4.

Much thanks for any help!

You can use VLOOKUP.

Note that, since your ranges are contiguous, col B is not required, but you do
need a test for values greater than your largest defined value -- if not for
the "etc", it would be 200.

The general formula would be:

=VLOOKUP(F20,A1:C4,3)

You can use either an IF statement to test for a value > 200 (or max); or you
could just add a line to your table:

0 700
50 1600
100 2500
150 3400
200 undefined

and change the formula to refer to A1:C5
--ron

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((A1:A4<=F20)*(B1:B4>F20)*(C1:C4))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[NSNR]

try this function in cell G:
=IF(A2<=F2<B2,0,C2)


for G1 you should get 0 and for G20 you should get 3400


-NSNR

 

[cinnie]

Thanks to Fred, Mike, Ron and NSNR. These different appraoches work GREAT!

 

[Fred Smith]

Glad to help. Thanks for the feedback.

Fred

Thanks to Fred, Mike, Ron and NSNR. These different appraoches work
GREAT!