Forumla that Looks up a range of output from one cell
 

I have been struggling with getting a formula to look at the input of one
cell, decide where it falls within a range, and then determine where to go
lookup the correct output.

Here is the formula I am using:
=IF(I10<90%,LOOKUP(C8,C8),IF(I1090.01%<105%,LOOKU P(E8,E8),IF(I10105.01%<115%,LOOKUP(G8,G8),IF(I10 115%,LOOKUPI8,I8))))

This will give me either C8 or I8.

The other formula I used was:

=IF(I10<90%,LOOKUP(C8,C8),IF(I1090.01%,LOOKUP(E8, E8),IF(I10105.01%,LOOKUP(G8,G8),IF(I10115%,LOOKU PI8,I8))))

This will give me C8 or E8.

So am I stuck to just this TRUE/FALSE or is there a way to have the
additional conditions met?

Forumla that Looks up a range of output from one cell
 

Try this:

=IF(COUNT(I10),IF(I10<=90%,C8,IF(I10<=105%,E8,IF(I 10<=115%,G8,I8))),"")

--
Biff
Microsoft Excel MVP


"DKinNorthCakalacki" wrote in
message ...
I have been struggling with getting a formula to look at the input of one
cell, decide where it falls within a range, and then determine where to go
lookup the correct output.

Here is the formula I am using:
=IF(I10<90%,LOOKUP(C8,C8),IF(I1090.01%<105%,LOOKU P(E8,E8),IF(I10105.01%<115%,LOOKUP(G8,G8),IF(I10 115%,LOOKUPI8,I8))))

This will give me either C8 or I8.

The other formula I used was:

=IF(I10<90%,LOOKUP(C8,C8),IF(I1090.01%,LOOKUP(E8, E8),IF(I10105.01%,LOOKUP(G8,G8),IF(I10115%,LOOKU PI8,I8))))

This will give me C8 or E8.

So am I stuck to just this TRUE/FALSE or is there a way to have the
additional conditions met?

Forumla that Looks up a range of output from one cell
 

I may be misunderstanding, but how can LOOKUP(A1,A1) ever return any other
value than A1? Wouldn't your first formula be simpler like this?

=IF(I10<90%,C8,IF(I1090.01%<105%,E8,IF(I10105.01 %<115%,G8,IF(I10115%,I8,I8))))

And that being the case, why check the last one? Just make it

=IF(I10<90%,C8,IF(I1090.01%<105%,E8,IF(I10105.01 %<115%,G8,I8)))

And why check if you don't have to?

=IF(I10<90%,C8,IF(I10<105%,E8,IF(I10<115%,G8,I8)))

However, NO, you're not stuck even with this simplified string of IF
functions. This works, but in my view it's better for several reasons to set
up this information in a separate table and use one of the lookup funcctions.

For example, you could set aside an area at AA1:AB15, like so:

0% =C8
90% =E8
105% =G8
115% =I8
999% =I8

You could add more values any time. Then instead of the nested IFs you can
just use =LOOKUP(I10,AA1:AB5); the LOOKUP function searches for I10 in the
left column (AA) and returns the corresponding value from the right column
(AB).

There are other ways of arranging the table and other lookup functions, but
the point to them all is not having to set up an indefinitely large coil of
IFs.

--- "DKinNorthCakalacki" wrote:
I have been struggling with getting a formula to look at the input of one
cell, decide where it falls within a range, and then determine where to go
lookup the correct output.

Here is the formula I am using:
=IF(I10<90%,LOOKUP(C8,C8),IF(I1090.01%<105%,LOOKU P(E8,E8),IF(I10105.01%<115%,LOOKUP(G8,G8),IF(I10 115%,LOOKUPI8,I8))))

This will give me either C8 or I8.

The other formula I used was:

=IF(I10<90%,LOOKUP(C8,C8),IF(I1090.01%,LOOKUP(E8, E8),IF(I10105.01%,LOOKUP(G8,G8),IF(I10115%,LOOKU PI8,I8))))

This will give me C8 or E8.

So am I stuck to just this TRUE/FALSE or is there a way to have the
additional conditions met?