G'day everyone

I need some advice & help on simplifying the following please.

=IF(D3<=$B$15,$C$15,IF(D3$A$16<$B$16,$C$16,IF(D3 $A$17<$B$17,$C$17,IF(D3$A$18<$B$18,$C$18,IF(D3$A $19<$B$19,$C$19,IF(D3$A$20,$C$20,0))))))

Essentially there is 6 arguments, the only argument that will return a value
is the last, all the others return 0

D3 has a value that is a multiplier of 2 cells to arrive at the value, what
I would like to happen is that once the value updates, is for each of the
arguments to check which cell range that value fits between and return a
corresponding value from another cell.

assume D3 = 100

A15 = 10 & B15 = 20 C15 = 0.0226
A16 = 21 & B15 = 30 C16 = 0.0333 Etc.......

So when it get to

A19 = 91 & B19 = 100 C19 = 1.125

The response I should get in my formula cell is 1.125

Appreciate any assistance.
TIA
Mark.

Try this

=VLOOKUP(D3,A10:C18,3,TRUE)

Note that Column A must be sorted for this to work (as in your example) and
that despite column B being within the range it's not being used. The formula
simply looks at column A and as soon as it finds a value that exceed D3 (or
the last value) it returns the value in Column C

Mike

"NoodNutt" wrote:

G'day everyone

I need some advice & help on simplifying the following please.

=IF(D3<=$B$15,$C$15,IF(D3$A$16<$B$16,$C$16,IF(D3 $A$17<$B$17,$C$17,IF(D3$A$18<$B$18,$C$18,IF(D3$A $19<$B$19,$C$19,IF(D3$A$20,$C$20,0))))))

Essentially there is 6 arguments, the only argument that will return a value
is the last, all the others return 0

D3 has a value that is a multiplier of 2 cells to arrive at the value, what
I would like to happen is that once the value updates, is for each of the
arguments to check which cell range that value fits between and return a
corresponding value from another cell.

assume D3 = 100

A15 = 10 & B15 = 20 C15 = 0.0226
A16 = 21 & B15 = 30 C16 = 0.0333 Etc.......

So when it get to

A19 = 91 & B19 = 100 C19 = 1.125

The response I should get in my formula cell is 1.125

Appreciate any assistance.
TIA
Mark.

simply looks at column A and as soon as it finds a value that exceed D3 (or
the last value) it returns the value in Column C

It finds the closest match less than. Sorry for the confusion

"Mike H" wrote:

Try this

=VLOOKUP(D3,A10:C18,3,TRUE)

Note that Column A must be sorted for this to work (as in your example) and
that despite column B being within the range it's not being used. The formula
simply looks at column A and as soon as it finds a value that exceed D3 (or
the last value) it returns the value in Column C

Mike

"NoodNutt" wrote:

G'day everyone

I need some advice & help on simplifying the following please.

=IF(D3<=$B$15,$C$15,IF(D3$A$16<$B$16,$C$16,IF(D3 $A$17<$B$17,$C$17,IF(D3$A$18<$B$18,$C$18,IF(D3$A $19<$B$19,$C$19,IF(D3$A$20,$C$20,0))))))

Essentially there is 6 arguments, the only argument that will return a value
is the last, all the others return 0

D3 has a value that is a multiplier of 2 cells to arrive at the value, what
I would like to happen is that once the value updates, is for each of the
arguments to check which cell range that value fits between and return a
corresponding value from another cell.

assume D3 = 100

A15 = 10 & B15 = 20 C15 = 0.0226
A16 = 21 & B15 = 30 C16 = 0.0333 Etc.......

So when it get to

A19 = 91 & B19 = 100 C19 = 1.125

The response I should get in my formula cell is 1.125

Appreciate any assistance.
TIA
Mark.

G'day Mike

Thank you so much, that hit the spot nicely.

Regards
Mark.

Glad I could help and thanks for the feedback

"NoodNutt" wrote:

G'day Mike

Thank you so much, that hit the spot nicely.

Regards
Mark.

Actually Mike

I was working on trish's problem.

I think what I have put together will get her out of trouble, with your help
of course.

Thx again
Mark.