How do I write formula to check a range of cells?

T

Tayo

Please I have the following:
COLUMN A COLUMN B
COLUMN C
ROW1 500 1000
5:00
ROW2 1001 1500
4:50
ROW3 1501 2000
4:25
ROW4 2001 2500
4:00

How can I write a formula for excel to check the value of a number in column
F check the row that the number will be fit in in the above table and use the
value in column C of the above table to multiply the number. For example, if
the value in column F is 1325, this number will fall in row 2, then I want my
formula to now use 1325*4:50 (i.e. the value of C2 to multiply the number).

Another example, if I have 2431 in column F, then the formula should be
2431*4:00.

How can I have a general formula that will cater for this?

All help will be appreciated please.
Thank you.
 
T

Tayo

Thank you Luke.

that was a quick response and it answered my question. Thank you very much.
 
B

Bernard Liengme

either
=F1*INDEX(C1:C4,MATCH(F1,A1:A4,2))

or
=F1*INDEX(C1:C4,MATCH(F1,A1:A4,2))

best wishes
 
T

T. Valko

What result do you expect from:

1325*4:50

You're multiplying a *time value* by an integer which evaluates as:

1325*0.201388888888889 = 266.840277777778

Is that the result you expect?

Try this:

=F2*VLOOKUP(F2,A2:C5,3)
 
L

Luke M

Something like:

=F2*LOOKUP(F2,A$2:A$10,C$2:C$10)

Where A2:A10 contains your lower boundaries of each section. Note that this
formula does not handle errors, such as what to do it F2 is below lowest
limit, or greater than highest limit. You will need to add an IF function if
that is a possible issue.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top