I have three columns of information:

RATE 15 DAY 30 DAY
5.750% 0.375 0.500
5.875% -0.250 -0.125
6.000% -0.625 -0.500
6.125% -1.125 -1.000
6.250% -1.500 -1.375
6.375% -1.875 -1.750
6.500% -2.250 -2.125
6.625% -2.750 -2.625
6.750% -3.125 -3.000
6.875% -3.625 -3.500

I three columns are the result of formulas which find the data elsewhere
(albeit simple references, i.e. =R3).

I would like to find the price in the third column closest, but never
greater than zero. and return this as my first result. The second result is
the corresponding interest rate (from the first column) that goes with the
matched price.

I've tried VLOOKUP, but I can't sort the third column another way and I keep
getting -3.50 as my matched price.

Other suggestions for how to do this? I would really appreciate it.

Karen

Try this:
With your example data in Cells A1:C11 ......and assuming that Col_C is in
descending order.

D1: =INDEX(C2:C11,MATCH(0,C2:C11,-1)+1,1)
returns -0.125

E1: =INDEX(A2:A11,MATCH(0,C2:C11,-1)+1,1)
returns 0.05875

Does that help?

***********
Regards,
Ron


"K. Westerman" wrote:

I have three columns of information:

RATE 15 DAY 30 DAY
5.750% 0.375 0.500
5.875% -0.250 -0.125
6.000% -0.625 -0.500
6.125% -1.125 -1.000
6.250% -1.500 -1.375
6.375% -1.875 -1.750
6.500% -2.250 -2.125
6.625% -2.750 -2.625
6.750% -3.125 -3.000
6.875% -3.625 -3.500

I three columns are the result of formulas which find the data elsewhere
(albeit simple references, i.e. =R3).

I would like to find the price in the third column closest, but never
greater than zero. and return this as my first result. The second result is
the corresponding interest rate (from the first column) that goes with the
matched price.

I've tried VLOOKUP, but I can't sort the third column another way and I keep
getting -3.50 as my matched price.

Other suggestions for how to do this? I would really appreciate it.

Karen

Thanks so much for your reply. Some work and some don't. Here's an example
of one that doesn't work:

Col f Col g Col h
RATE 15 DAY 30 DAY
6.250% -0.500 -0.375
6.375% -0.875 -0.750
6.500% -1.125 -1.000
6.625% -1.375 -1.250
6.750% -1.625 -1.500
6.875% -1.750 -1.625

=INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1)

This returns #N/A. Have I missed something here?

Also, I would love to know how the formula breaks down; I don't completely
understand the reason you do INDEX and then MATCH and what the -1, and +1,1
mean.

Karen

To avoid a much more complicated formula, just put these values at the top of
the table (just under the labels). The formula needs a value that is greater
than zero. (If you don't want to see them....hide the row)

RATE 15 Day 30 Day
1000.00% 999999 999999

Does that fix the situation?

***********
Regards,
Ron


"K. Westerman" wrote:

Thanks so much for your reply. Some work and some don't. Here's an example
of one that doesn't work:

Col f Col g Col h
RATE 15 DAY 30 DAY
6.250% -0.500 -0.375
6.375% -0.875 -0.750
6.500% -1.125 -1.000
6.625% -1.375 -1.250
6.750% -1.625 -1.500
6.875% -1.750 -1.625

=INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1)

This returns #N/A. Have I missed something here?

Also, I would love to know how the formula breaks down; I don't completely
understand the reason you do INDEX and then MATCH and what the -1, and +1,1
mean.

Karen

Yes, that worked beautifully. Thank you very much,

Karen

"Ron Coderre" wrote:

To avoid a much more complicated formula, just put these values at the top of
the table (just under the labels). The formula needs a value that is greater
than zero. (If you don't want to see them....hide the row)

RATE 15 Day 30 Day
1000.00% 999999 999999

Does that fix the situation?

***********
Regards,
Ron


"K. Westerman" wrote:

Thanks so much for your reply. Some work and some don't. Here's an example
of one that doesn't work:

Col f Col g Col h
RATE 15 DAY 30 DAY
6.250% -0.500 -0.375
6.375% -0.875 -0.750
6.500% -1.125 -1.000
6.625% -1.375 -1.250
6.750% -1.625 -1.500
6.875% -1.750 -1.625

=INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1)

This returns #N/A. Have I missed something here?

Also, I would love to know how the formula breaks down; I don't completely
understand the reason you do INDEX and then MATCH and what the -1, and +1,1
mean.

Karen

Thanks for the feedback, Karen....I'm glad I could help.

***********
Regards,
Ron


"K. Westerman" wrote:

Yes, that worked beautifully. Thank you very much,

Karen

"Ron Coderre" wrote:

To avoid a much more complicated formula, just put these values at the top of
the table (just under the labels). The formula needs a value that is greater
than zero. (If you don't want to see them....hide the row)

RATE 15 Day 30 Day
1000.00% 999999 999999

Does that fix the situation?

***********
Regards,
Ron


"K. Westerman" wrote:

Thanks so much for your reply. Some work and some don't. Here's an example
of one that doesn't work:

Col f Col g Col h
RATE 15 DAY 30 DAY
6.250% -0.500 -0.375
6.375% -0.875 -0.750
6.500% -1.125 -1.000
6.625% -1.375 -1.250
6.750% -1.625 -1.500
6.875% -1.750 -1.625

=INDEX($F$20:$F$25,MATCH(0,$H$20:$H$25,-1)+1,1)

This returns #N/A. Have I missed something here?

Also, I would love to know how the formula breaks down; I don't completely
understand the reason you do INDEX and then MATCH and what the -1, and +1,1
mean.

Karen