Formula help please
 

I have the following entry fields:
A B C D
1 inv. obj % cash % Bond % stock
2 Growth 10% 30% 60%
3
4
Then I have made a table that contains criteria as follows:
A b c d
5 Inv. obj cash low range cash high range bond low
6 Growth 0% 3% 6%
7 Income 5% 12% 60%
8 Preservation 20% 60% 30%

Etc. And column e would be the bond high range, then
column f would be the stock low range, and column g would
be the stock high range.

I would like to create a formula in cell E2 that says the
following:
Lookup my entry in a2 in the table from column a6:a8.
Then determine if my cash percentage entry is either
within the range, or out of the range that is determined
by the table column b and c(for whichever inv. obj. that
is being looked up. If it is out of range, return "out of
range", if it is in range, return "OK".

Thanks alot.
Scott

Formula help please
 

Hi Scott
try the following in E2
=IF(AND(B2=VLOOKUP(A2,$A$6:$D8,2,0),B2<=VLOOKUP(A 2,$A$6:$D8,3,0)),"OK"
,"Out of range")

HTH
Frank

Scott wrote:
I have the following entry fields:
A B C D
1 inv. obj % cash % Bond % stock
2 Growth 10% 30% 60%
3
4
Then I have made a table that contains criteria as follows:
A b c d
5 Inv. obj cash low range cash high range bond low
6 Growth 0% 3% 6%
7 Income 5% 12% 60%
8 Preservation 20% 60% 30%

Etc. And column e would be the bond high range, then
column f would be the stock low range, and column g would
be the stock high range.

I would like to create a formula in cell E2 that says the
following:
Lookup my entry in a2 in the table from column a6:a8.
Then determine if my cash percentage entry is either
within the range, or out of the range that is determined
by the table column b and c(for whichever inv. obj. that
is being looked up. If it is out of range, return "out of
range", if it is in range, return "OK".

Thanks alot.
Scott

Formula help please
 

-----Original Message-----
Hi Scott
try the following in E2
=IF(AND(B2=VLOOKUP(A2,$A$6:$D8,2,0),B2<=VLOOKU P
(A2,$A$6:$D8,3,0)),"OK"
,"Out of range")

HTH
Frank

Scott wrote:
I have the following entry fields:
A B C D
1 inv. obj % cash % Bond % stock
2 Growth 10% 30% 60%
3
4
Then I have made a table that contains criteria as
follows:
A b c d
5 Inv. obj cash low range cash high range bond low
6 Growth 0% 3% 6%
7 Income 5% 12% 60%
8 Preservation 20% 60% 30%

Etc. And column e would be the bond high range, then
column f would be the stock low range, and column g
would
be the stock high range.

I would like to create a formula in cell E2 that says
the
following:
Lookup my entry in a2 in the table from column a6:a8.
Then determine if my cash percentage entry is either
within the range, or out of the range that is determined
by the table column b and c(for whichever inv. obj. that
is being looked up. If it is out of range, return "out
of
range", if it is in range, return "OK".

Thanks alot.
Scott


.
Hi Frank. Thank you for being willing to come to my
rescue again. These formulas can be pretty overwhelming
sometime. I entered the formula that you gave me and am
getting a #n/a result. Any suggestions?

Formula help please
 

scott wrote:
Hi Frank. Thank you for being willing to come to my
rescue again. These formulas can be pretty overwhelming
sometime. I entered the formula that you gave me and am
getting a #n/a result. Any suggestions?

Hi
looks like the MATCH function did not find a match. Possible reasons:
wrong lookup criteria, different text, etc. If you like, mail me your
spreadsheet and I'll look at it
Frank

Formula help please
 

-----Original Message-----
Hi Scott
try the following in E2
=IF(AND(B2=VLOOKUP(A2,$A$6:$D8,2,0),B2<=VLOOKU P
(A2,$A$6:$D8,3,0)),"OK"
,"Out of range")

HTH
Frank

Scott wrote:
I have the following entry fields:
A B C D
1 inv. obj % cash % Bond % stock
2 Growth 10% 30% 60%
3
4
Then I have made a table that contains criteria as
follows:
A b c d
5 Inv. obj cash low range cash high range bond low
6 Growth 0% 3% 6%
7 Income 5% 12% 60%
8 Preservation 20% 60% 30%

Etc. And column e would be the bond high range, then
column f would be the stock low range, and column g
would
be the stock high range.

I would like to create a formula in cell E2 that says
the
following:
Lookup my entry in a2 in the table from column a6:a8.
Then determine if my cash percentage entry is either
within the range, or out of the range that is determined
by the table column b and c(for whichever inv. obj. that
is being looked up. If it is out of range, return "out
of
range", if it is in range, return "OK".

Thanks alot.
Scott


.
Frank. Just wanted to let you know that I had made a
small mistake in entering your formula. When I
highlighed the range cells I forgot to include column a,
so I had $b$6:$d8 instead of $a$6:$8. For some reason it
seemed logical for me to do it this way since I already
entered column a as the look up column, but I guess it
needs to be included here also.
So I made this adjustment and it worked!
Thanks again. Scott
Just curious, why do you only put one dollar sign in front
of d8, rather than $d$8?

Formula help please
 

scott wrote:
Frank. Just wanted to let you know that I had made a
small mistake in entering your formula. When I
highlighed the range cells I forgot to include column a,
so I had $b$6:$d8 instead of $a$6:$8. For some reason it
seemed logical for me to do it this way since I already
entered column a as the look up column, but I guess it
needs to be included here also.
So I made this adjustment and it worked!
Thanks again. Scott
Just curious, why do you only put one dollar sign in front
of d8, rather than $d$8?

quite simple. I just forgot it :-)
Frank