Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks

You could highlight column O, then use CTRL+F to search for 31% (match entire
cell contents).

By formula:
=index($K$1:$K$5000,match(31%,$O$1:$O$5000,0))

"Oscar" wrote:

Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks

Thanls for ypur response ~L. I used the formula, but I get an error "#NA". I
would like to know if instead of only looking for 31%, can the formula give
me the list of Rates that would equal anywhere between 31.00%: 31.44%?

Thanks

"~L" wrote:

You could highlight column O, then use CTRL+F to search for 31% (match entire
cell contents).

By formula:
=index($K$1:$K$5000,match(31%,$O$1:$O$5000,0))

"Oscar" wrote:

Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks

The #N/A means it couldn't find 31% within the specified range. There could
be a number of reasons for this. For example, the number could be 31%
because the cell formatting specifies 0 decimal places (and it is 30.5% or
31.49% or something in between). Another reason could be, the number is
stored as text.

See if this suits your needs:

Array enter using CTRL+SHIFT+ENTER:

=index($K$1:$K$5000,match(1,($O$1:$O$5000=31%)*($ O$1:$O$5000<=31.44%)*,0))

My interpretation of your description was x=31% x<=31.44%, if you meant to
exclude those min and max values, remove the = signs. You might also
consider replacing the 31% and 31.44% with references to cells containing
those values, since it will be easier to change the formula.

"Oscar" wrote:

Thanls for ypur response ~L. I used the formula, but I get an error "#NA". I
would like to know if instead of only looking for 31%, can the formula give
me the list of Rates that would equal anywhere between 31.00%: 31.44%?

Thanks

"~L" wrote:

You could highlight column O, then use CTRL+F to search for 31% (match entire
cell contents).

By formula:
=index($K$1:$K$5000,match(31%,$O$1:$O$5000,0))

"Oscar" wrote:

Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks

I entered the formula exactly how you advised, but I get an error stating
that the formula contains an error. I doubled checked my cell range. Is there
something missing on the formula?

Thanks

"~L" wrote:

The #N/A means it couldn't find 31% within the specified range. There could
be a number of reasons for this. For example, the number could be 31%
because the cell formatting specifies 0 decimal places (and it is 30.5% or
31.49% or something in between). Another reason could be, the number is
stored as text.

See if this suits your needs:

Array enter using CTRL+SHIFT+ENTER:

=index($K$1:$K$5000,match(1,($O$1:$O$5000=31%)*($ O$1:$O$5000<=31.44%)*,0))

My interpretation of your description was x=31% x<=31.44%, if you meant to
exclude those min and max values, remove the = signs. You might also
consider replacing the 31% and 31.44% with references to cells containing
those values, since it will be easier to change the formula.

"Oscar" wrote:

Thanls for ypur response ~L. I used the formula, but I get an error "#NA". I
would like to know if instead of only looking for 31%, can the formula give
me the list of Rates that would equal anywhere between 31.00%: 31.44%?

Thanks

"~L" wrote:

You could highlight column O, then use CTRL+F to search for 31% (match entire
cell contents).

By formula:
=index($K$1:$K$5000,match(31%,$O$1:$O$5000,0))

"Oscar" wrote:

Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks

I'm guessing the last "*" was a mistake.

Oscar wrote:
I entered the formula exactly how you advised, but I get an error stating
that the formula contains an error. I doubled checked my cell range. Is there
something missing on the formula?

Thanks

"~L" wrote:

The #N/A means it couldn't find 31% within the specified range. There could
be a number of reasons for this. For example, the number could be 31%
because the cell formatting specifies 0 decimal places (and it is 30.5% or
31.49% or something in between). Another reason could be, the number is
stored as text.

See if this suits your needs:

Array enter using CTRL+SHIFT+ENTER:

=index($K$1:$K$5000,match(1,($O$1:$O$5000=31%)*($ O$1:$O$5000<=31.44%)*,0))

My interpretation of your description was x=31% x<=31.44%, if you meant to
exclude those min and max values, remove the = signs. You might also
consider replacing the 31% and 31.44% with references to cells containing
those values, since it will be easier to change the formula.

"Oscar" wrote:

Thanls for ypur response ~L. I used the formula, but I get an error "#NA". I
would like to know if instead of only looking for 31%, can the formula give
me the list of Rates that would equal anywhere between 31.00%: 31.44%?

Thanks

"~L" wrote:

You could highlight column O, then use CTRL+F to search for 31% (match entire
cell contents).

By formula:
=index($K$1:$K$5000,match(31%,$O$1:$O$5000,0))

"Oscar" wrote:

Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks

Can anyone please help on my last question?

"~L" wrote:

The #N/A means it couldn't find 31% within the specified range. There could
be a number of reasons for this. For example, the number could be 31%
because the cell formatting specifies 0 decimal places (and it is 30.5% or
31.49% or something in between). Another reason could be, the number is
stored as text.

See if this suits your needs:

Array enter using CTRL+SHIFT+ENTER:

=index($K$1:$K$5000,match(1,($O$1:$O$5000=31%)*($ O$1:$O$5000<=31.44%)*,0))

My interpretation of your description was x=31% x<=31.44%, if you meant to
exclude those min and max values, remove the = signs. You might also
consider replacing the 31% and 31.44% with references to cells containing
those values, since it will be easier to change the formula.

"Oscar" wrote:

Thanls for ypur response ~L. I used the formula, but I get an error "#NA". I
would like to know if instead of only looking for 31%, can the formula give
me the list of Rates that would equal anywhere between 31.00%: 31.44%?

Thanks

"~L" wrote:

You could highlight column O, then use CTRL+F to search for 31% (match entire
cell contents).

By formula:
=index($K$1:$K$5000,match(31%,$O$1:$O$5000,0))

"Oscar" wrote:

Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks