I have the folowing formula in a spreadsheet in cell c1 (nested lookup
function)
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
Lets say this brings back the results from cell x5 (or.25).
If I want to bring back the next few rows (x6,x7and next few columns
z5,z6,z7) in the rows and colums next to this response is there a way to do
it ?
I hope my question is clear - Im confused just writing it.
Thanks,
Yosef

PS - One more silly question, when I post questions - I am often getting
messages that my request cant be done and brought to a generic page. Any
idea why ? Thanks again.

Hi!

You could use an array Offset formula to return a 3R by 3C range. But to be
more specific you'd have to tell us where all of this stuff is:

INDIRECT(VLOOKUP(B2,productlookup,2,0))

Biff

"ynissel" wrote in message
...
I have the folowing formula in a spreadsheet in cell c1 (nested lookup
function)
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
Lets say this brings back the results from cell x5 (or.25).
If I want to bring back the next few rows (x6,x7and next few columns
z5,z6,z7) in the rows and colums next to this response is there a way to
do
it ?
I hope my question is clear - Im confused just writing it.
Thanks,
Yosef

PS - One more silly question, when I post questions - I am often getting
messages that my request cant be done and brought to a generic page. Any
idea why ? Thanks again.

Ok - Ill try to spell it all out correctly . I tried the array but couldnt
get it right.
MY formula is this:
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
I am currently in a sheet called pricing tool. B4 is an interest rate (I
multiplied by 100 because the table is not in percent form) and B2 is a
product drop down box.
The product lookup is a lookup table in a sheet called rate sheet backup
(cells b8-d17) It takes the product from cell b2 above and determines which
table to look up the interest rate table in sheet "rate sheet" and returns
the price.
This is productlookup:
product drop down list range name of the individual rate tables
Alt A 30 Yr. fixed30 fixed30rates
Alt A 15 Yr. fixed15 fixed15rates
Alt A 1 Month ARM Arm1mo Arm1morates
Alt A 6 Month ARM Arm6mo Arm6morates
Alt A 2/6 ARM arm26 arm26rates
Alt A 3/6 ARM arm36 arm36rates
Alt A 5/6 ARM arm56 arm56rates
Alt B 30 Yr. Altb30 Altb30rates
Alt B 15 Yr. Altb15 Altb15rates
Alt A Seconds seconds secondsrates

This is one of the interest rate tables in rate sheet

(a8-e22 is fixed30 - the rest of these tables are exactly the same format

rate price 15 price 30 price 45 price 60
5.500 1.375 1.500 1.625 1.750
5.625 1.000 1.125 1.250 1.375
5.750 0.625 0.750 0.875 1.000
5.875 0.125 0.250 0.375 0.500
6.000 -0.250 -0.125 0.000 0.125
6.125 -0.375 -0.250 -0.125 0.000
6.250 -0.625 -0.500 -0.375 -0.250
6.375 -1.125 -1.000 -0.875 -0.750
6.500 -1.500 -1.375 -1.250 -1.125
6.625 -1.625 -1.500 -1.375 -1.250
6.750 -1.750 -1.625 -1.500 -1.375
6.875 -1.875 -1.750 -1.625 -1.500
7.000 -2.125 -2.000 -1.875 -1.750
7.125 -2.375 -2.250 -2.125 -2.000
7.250 -3.000 -2.875 -2.750 -2.625

While I need the specific price corresponding to the interest rate from b4 -
I also want to make a table to the side that returns the price for a few
interest rate ranges above and below the rate that was chosen in b4.
Can you help me out ?

Thanks !
Yosef



"Biff" wrote:

Hi!

You could use an array Offset formula to return a 3R by 3C range. But to be
more specific you'd have to tell us where all of this stuff is:

INDIRECT(VLOOKUP(B2,productlookup,2,0))

Biff

"ynissel" wrote in message
...
I have the folowing formula in a spreadsheet in cell c1 (nested lookup
function)
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
Lets say this brings back the results from cell x5 (or.25).
If I want to bring back the next few rows (x6,x7and next few columns
z5,z6,z7) in the rows and colums next to this response is there a way to
do
it ?
I hope my question is clear - Im confused just writing it.
Thanks,
Yosef

PS - One more silly question, when I post questions - I am often getting
messages that my request cant be done and brought to a generic page. Any
idea why ? Thanks again.

Hi!

I did some work for a mortgage broker not too long ago that wanted almost
the exact same thing! I had to rebuild all of the rate tables and in
general, make things user friendly!

The easy way to do this would be to just use your current lookup formula and
use a range of lookup values.

For example, if you enter a rate in B4 of 5% and would also like to display
the next lower rate and the next higher rate:

B3 = 4.5%
B4 = 5.0%
B5 = 5.5%

And maybe have the formulas in C3:C5.

An array Offset could still probably been done but with all those different
named tables would make it more complicated. Not something I could do
without seeing the actual file.

Biff

"ynissel" wrote in message
...
Ok - Ill try to spell it all out correctly . I tried the array but
couldnt
get it right.
MY formula is this:
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
I am currently in a sheet called pricing tool. B4 is an interest rate (I
multiplied by 100 because the table is not in percent form) and B2 is a
product drop down box.
The product lookup is a lookup table in a sheet called rate sheet backup
(cells b8-d17) It takes the product from cell b2 above and determines
which
table to look up the interest rate table in sheet "rate sheet" and returns
the price.
This is productlookup:
product drop down list range name of the individual rate tables
Alt A 30 Yr. fixed30 fixed30rates
Alt A 15 Yr. fixed15 fixed15rates
Alt A 1 Month ARM Arm1mo Arm1morates
Alt A 6 Month ARM Arm6mo Arm6morates
Alt A 2/6 ARM arm26 arm26rates
Alt A 3/6 ARM arm36 arm36rates
Alt A 5/6 ARM arm56 arm56rates
Alt B 30 Yr. Altb30 Altb30rates
Alt B 15 Yr. Altb15 Altb15rates
Alt A Seconds seconds secondsrates

This is one of the interest rate tables in rate sheet

(a8-e22 is fixed30 - the rest of these tables are exactly the same format

rate price 15 price 30 price 45 price 60
5.500 1.375 1.500 1.625 1.750
5.625 1.000 1.125 1.250 1.375
5.750 0.625 0.750 0.875 1.000
5.875 0.125 0.250 0.375 0.500
6.000 -0.250 -0.125 0.000 0.125
6.125 -0.375 -0.250 -0.125 0.000
6.250 -0.625 -0.500 -0.375 -0.250
6.375 -1.125 -1.000 -0.875 -0.750
6.500 -1.500 -1.375 -1.250 -1.125
6.625 -1.625 -1.500 -1.375 -1.250
6.750 -1.750 -1.625 -1.500 -1.375
6.875 -1.875 -1.750 -1.625 -1.500
7.000 -2.125 -2.000 -1.875 -1.750
7.125 -2.375 -2.250 -2.125 -2.000
7.250 -3.000 -2.875 -2.750 -2.625

While I need the specific price corresponding to the interest rate from
b4 -
I also want to make a table to the side that returns the price for a few
interest rate ranges above and below the rate that was chosen in b4.
Can you help me out ?

Thanks !
Yosef



"Biff" wrote:

Hi!

You could use an array Offset formula to return a 3R by 3C range. But to
be
more specific you'd have to tell us where all of this stuff is:

INDIRECT(VLOOKUP(B2,productlookup,2,0))

Biff

"ynissel" wrote in message
...
I have the folowing formula in a spreadsheet in cell c1 (nested lookup
function)
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
Lets say this brings back the results from cell x5 (or.25).
If I want to bring back the next few rows (x6,x7and next few columns
z5,z6,z7) in the rows and colums next to this response is there a way
to
do
it ?
I hope my question is clear - Im confused just writing it.
Thanks,
Yosef

PS - One more silly question, when I post questions - I am often
getting
messages that my request cant be done and brought to a generic page.
Any
idea why ? Thanks again.

Small world !
Ill try it your way - sounds simple enough.
Thanks.

"Biff" wrote in message
...
Hi!

I did some work for a mortgage broker not too long ago that wanted almost
the exact same thing! I had to rebuild all of the rate tables and in
general, make things user friendly!

The easy way to do this would be to just use your current lookup formula
and use a range of lookup values.

For example, if you enter a rate in B4 of 5% and would also like to
display the next lower rate and the next higher rate:

B3 = 4.5%
B4 = 5.0%
B5 = 5.5%

And maybe have the formulas in C3:C5.

An array Offset could still probably been done but with all those
different named tables would make it more complicated. Not something I
could do without seeing the actual file.

Biff

"ynissel" wrote in message
...
Ok - Ill try to spell it all out correctly . I tried the array but
couldnt
get it right.
MY formula is this:
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
I am currently in a sheet called pricing tool. B4 is an interest rate (I
multiplied by 100 because the table is not in percent form) and B2 is a
product drop down box.
The product lookup is a lookup table in a sheet called rate sheet backup
(cells b8-d17) It takes the product from cell b2 above and determines
which
table to look up the interest rate table in sheet "rate sheet" and
returns
the price.
This is productlookup:
product drop down list range name of the individual rate tables
Alt A 30 Yr. fixed30 fixed30rates
Alt A 15 Yr. fixed15 fixed15rates
Alt A 1 Month ARM Arm1mo Arm1morates
Alt A 6 Month ARM Arm6mo Arm6morates
Alt A 2/6 ARM arm26 arm26rates
Alt A 3/6 ARM arm36 arm36rates
Alt A 5/6 ARM arm56 arm56rates
Alt B 30 Yr. Altb30 Altb30rates
Alt B 15 Yr. Altb15 Altb15rates
Alt A Seconds seconds secondsrates

This is one of the interest rate tables in rate sheet

(a8-e22 is fixed30 - the rest of these tables are exactly the same format

rate price 15 price 30 price 45 price 60
5.500 1.375 1.500 1.625 1.750
5.625 1.000 1.125 1.250 1.375
5.750 0.625 0.750 0.875 1.000
5.875 0.125 0.250 0.375 0.500
6.000 -0.250 -0.125 0.000 0.125
6.125 -0.375 -0.250 -0.125 0.000
6.250 -0.625 -0.500 -0.375 -0.250
6.375 -1.125 -1.000 -0.875 -0.750
6.500 -1.500 -1.375 -1.250 -1.125
6.625 -1.625 -1.500 -1.375 -1.250
6.750 -1.750 -1.625 -1.500 -1.375
6.875 -1.875 -1.750 -1.625 -1.500
7.000 -2.125 -2.000 -1.875 -1.750
7.125 -2.375 -2.250 -2.125 -2.000
7.250 -3.000 -2.875 -2.750 -2.625

While I need the specific price corresponding to the interest rate from
b4 -
I also want to make a table to the side that returns the price for a few
interest rate ranges above and below the rate that was chosen in b4.
Can you help me out ?

Thanks !
Yosef



"Biff" wrote:

Hi!

You could use an array Offset formula to return a 3R by 3C range. But to
be
more specific you'd have to tell us where all of this stuff is:

INDIRECT(VLOOKUP(B2,productlookup,2,0))

Biff

"ynissel" wrote in message
...
I have the folowing formula in a spreadsheet in cell c1 (nested lookup
function)
=VLOOKUP(ROUND(B4*100,3),INDIRECT(VLOOKUP(B2,produ ctlookup,2,0)),2)
Lets say this brings back the results from cell x5 (or.25).
If I want to bring back the next few rows (x6,x7and next few columns
z5,z6,z7) in the rows and colums next to this response is there a way
to
do
it ?
I hope my question is clear - Im confused just writing it.
Thanks,
Yosef

PS - One more silly question, when I post questions - I am often
getting
messages that my request cant be done and brought to a generic page.
Any
idea why ? Thanks again.