Vlookup, What is correct formula for problem below?

Looks like you need to get the 85 choices from the third sheet back into the
second sheet
next to the numbers in A2 to A86
you could then enter a vlookup function in say D1
such as =VLOOKUP(Sheet1!A23,A2:B86,2,FALSE)

i.e in english
this will look for the value in sheet1 A23 and find the same value in the
table on sheet 2 A2:B86
then it will pick the numer in the second column of that table.

--
Greetings from New Zealand
Bill K
"Bill R" <[email protected]> wrote in message
news:[email protected]...
> The workbook contains three sheets. The formula needs to be in the 2nd
> sheet
> and it needs to pull a factor from a possible 85 choices located in
> (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> The
> correct one to use is determined by the term of the loan which I need to
> pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column.
> What
> would be the correct formula to use? I'm stumped. Thanks.

Thank you. I had to join both columns together to make them work. Here is to
formula:=VLOOKUP('F&I Menu '!BA6,Tables!A2:B85,2,FALSE)



"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

If Term is only one column, then excel is telling you that it's having trouble
returning that second column of that single column range.

I'd create a new name (for both columns) and call it TermRate and use:
=VLOOKUP('F&I Menu '!BA6,TermRate,2,FALSE)

or maybe:
=index(rate,match('f&i menu '!ba6,term,0))

But I wouldn't use Rate as a name, either. Excel has a function called
=rate(). (And it would confuse the heck out of me!)

Bill R wrote:
>
> I input the following formula and it returns a #Ref error. (I named the
> columns: Column is named"Term" and Column 2 is named "Rate".) The term is
> pulling correctly but the rest of it does not seem to work. What can I do?
> Thanks.
>
> =VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)
>
> "Ken Hudson" wrote:
>
> > And, if you are going to copy/drag your formula down from E12, you'll need to
> > "anchor" your Sheet3 range reference in your formula:
> >
> > =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
> >
> > --
> > Ken Hudson
> >
> >
> > "Bill Kuunders" wrote:
> >
> > > So, in E12 on sheet2 enter
> > > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > > Regards
> > > Bill K
> > >
> > > "Bill R" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > > on
> > > > that sheet and match it with the number in the 1st column to pull the
> > > > factor
> > > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > > premium. Thanks.
> > > >
> > > > Term Rate
> > > > 1 0.29000
> > > > 2 0.57000
> > > > 3 0.86000
> > > > 4 1.03000
> > > > 5 1.20000
> > > > 6 1.37000
> > > > 7 1.52000
> > > > 8 1.67000
> > > > 9 1.82000
> > > > 10 1.95000
> > > > 11 2.07000
> > > > 12 2.20000
> > > > 13 2.27000
> > > > 14 2.33000
> > > >
> > > > "Bill R" wrote:
> > > >
> > > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > > >> sheet
> > > >> and it needs to pull a factor from a possible 85 choices located in
> > > >> (B2:B86)
> > > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > > >> The
> > > >> correct one to use is determined by the term of the loan which I need to
> > > >> pull
> > > >> from the 1st sheet in the workbook. The term of the loan will match the
> > > >> number in the 1st column next to the correct factor in the 2nd column.
> > > >> What
> > > >> would be the correct formula to use? I'm stumped. Thanks.
> > >
> > >
> > >

--

Dave Peterson

I input the following formula and it returns a #Ref error. (I named the
columns: Column is named"Term" and Column 2 is named "Rate".) The term is
pulling correctly but the rest of it does not seem to work. What can I do?
Thanks.

=VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)

"Ken Hudson" wrote:

> And, if you are going to copy/drag your formula down from E12, you'll need to
> "anchor" your Sheet3 range reference in your formula:
>
> =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
>
> --
> Ken Hudson
>
>
> "Bill Kuunders" wrote:
>
> > So, in E12 on sheet2 enter
> > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > Regards
> > Bill K
> >
> > "Bill R" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > on
> > > that sheet and match it with the number in the 1st column to pull the
> > > factor
> > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > premium. Thanks.
> > >
> > > Term Rate
> > > 1 0.29000
> > > 2 0.57000
> > > 3 0.86000
> > > 4 1.03000
> > > 5 1.20000
> > > 6 1.37000
> > > 7 1.52000
> > > 8 1.67000
> > > 9 1.82000
> > > 10 1.95000
> > > 11 2.07000
> > > 12 2.20000
> > > 13 2.27000
> > > 14 2.33000
> > >
> > > "Bill R" wrote:
> > >
> > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > >> sheet
> > >> and it needs to pull a factor from a possible 85 choices located in
> > >> (B2:B86)
> > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > >> The
> > >> correct one to use is determined by the term of the loan which I need to
> > >> pull
> > >> from the 1st sheet in the workbook. The term of the loan will match the
> > >> number in the 1st column next to the correct factor in the 2nd column.
> > >> What
> > >> would be the correct formula to use? I'm stumped. Thanks.
> >
> >
> >

This is the first two columns on sheet #3. I am trying to Pull the finance
term from a payment calculator in the 1st sheet which is located at (BA6) on
that sheet and match it with the number in the 1st column to pull the factor
in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
premium. Thanks.

Term Rate
1 0.29000
2 0.57000
3 0.86000
4 1.03000
5 1.20000
6 1.37000
7 1.52000
8 1.67000
9 1.82000
10 1.95000
11 2.07000
12 2.20000
13 2.27000
14 2.33000

"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

And, if you are going to copy/drag your formula down from E12, you'll need to
"anchor" your Sheet3 range reference in your formula:

=VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)

--
Ken Hudson


"Bill Kuunders" wrote:

> So, in E12 on sheet2 enter
> =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> Regards
> Bill K
>
> "Bill R" <[email protected]> wrote in message
> news:[email protected]...
> > This is the first two columns on sheet #3. I am trying to Pull the finance
> > term from a payment calculator in the 1st sheet which is located at (BA6)
> > on
> > that sheet and match it with the number in the 1st column to pull the
> > factor
> > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > premium. Thanks.
> >
> > Term Rate
> > 1 0.29000
> > 2 0.57000
> > 3 0.86000
> > 4 1.03000
> > 5 1.20000
> > 6 1.37000
> > 7 1.52000
> > 8 1.67000
> > 9 1.82000
> > 10 1.95000
> > 11 2.07000
> > 12 2.20000
> > 13 2.27000
> > 14 2.33000
> >
> > "Bill R" wrote:
> >
> >> The workbook contains three sheets. The formula needs to be in the 2nd
> >> sheet
> >> and it needs to pull a factor from a possible 85 choices located in
> >> (B2:B86)
> >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> >> The
> >> correct one to use is determined by the term of the loan which I need to
> >> pull
> >> from the 1st sheet in the workbook. The term of the loan will match the
> >> number in the 1st column next to the correct factor in the 2nd column.
> >> What
> >> would be the correct formula to use? I'm stumped. Thanks.
>
>
>

So, in E12 on sheet2 enter
=VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
Regards
Bill K

"Bill R" <[email protected]> wrote in message
news:[email protected]...
> This is the first two columns on sheet #3. I am trying to Pull the finance
> term from a payment calculator in the 1st sheet which is located at (BA6)
> on
> that sheet and match it with the number in the 1st column to pull the
> factor
> in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> premium. Thanks.
>
> Term Rate
> 1 0.29000
> 2 0.57000
> 3 0.86000
> 4 1.03000
> 5 1.20000
> 6 1.37000
> 7 1.52000
> 8 1.67000
> 9 1.82000
> 10 1.95000
> 11 2.07000
> 12 2.20000
> 13 2.27000
> 14 2.33000
>
> "Bill R" wrote:
>
>> The workbook contains three sheets. The formula needs to be in the 2nd
>> sheet
>> and it needs to pull a factor from a possible 85 choices located in
>> (B2:B86)
>> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
>> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
>> The
>> correct one to use is determined by the term of the loan which I need to
>> pull
>> from the 1st sheet in the workbook. The term of the loan will match the
>> number in the 1st column next to the correct factor in the 2nd column.
>> What
>> would be the correct formula to use? I'm stumped. Thanks.

Thank you. I had to join both columns together to make them work. Here is to
formula:=VLOOKUP('F&I Menu '!BA6,Tables!A2:B85,2,FALSE)



"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

If Term is only one column, then excel is telling you that it's having trouble
returning that second column of that single column range.

I'd create a new name (for both columns) and call it TermRate and use:
=VLOOKUP('F&I Menu '!BA6,TermRate,2,FALSE)

or maybe:
=index(rate,match('f&i menu '!ba6,term,0))

But I wouldn't use Rate as a name, either. Excel has a function called
=rate(). (And it would confuse the heck out of me!)

Bill R wrote:
>
> I input the following formula and it returns a #Ref error. (I named the
> columns: Column is named"Term" and Column 2 is named "Rate".) The term is
> pulling correctly but the rest of it does not seem to work. What can I do?
> Thanks.
>
> =VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)
>
> "Ken Hudson" wrote:
>
> > And, if you are going to copy/drag your formula down from E12, you'll need to
> > "anchor" your Sheet3 range reference in your formula:
> >
> > =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
> >
> > --
> > Ken Hudson
> >
> >
> > "Bill Kuunders" wrote:
> >
> > > So, in E12 on sheet2 enter
> > > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > > Regards
> > > Bill K
> > >
> > > "Bill R" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > > on
> > > > that sheet and match it with the number in the 1st column to pull the
> > > > factor
> > > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > > premium. Thanks.
> > > >
> > > > Term Rate
> > > > 1 0.29000
> > > > 2 0.57000
> > > > 3 0.86000
> > > > 4 1.03000
> > > > 5 1.20000
> > > > 6 1.37000
> > > > 7 1.52000
> > > > 8 1.67000
> > > > 9 1.82000
> > > > 10 1.95000
> > > > 11 2.07000
> > > > 12 2.20000
> > > > 13 2.27000
> > > > 14 2.33000
> > > >
> > > > "Bill R" wrote:
> > > >
> > > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > > >> sheet
> > > >> and it needs to pull a factor from a possible 85 choices located in
> > > >> (B2:B86)
> > > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > > >> The
> > > >> correct one to use is determined by the term of the loan which I need to
> > > >> pull
> > > >> from the 1st sheet in the workbook. The term of the loan will match the
> > > >> number in the 1st column next to the correct factor in the 2nd column.
> > > >> What
> > > >> would be the correct formula to use? I'm stumped. Thanks.
> > >
> > >
> > >

--

Dave Peterson

I input the following formula and it returns a #Ref error. (I named the
columns: Column is named"Term" and Column 2 is named "Rate".) The term is
pulling correctly but the rest of it does not seem to work. What can I do?
Thanks.

=VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)

"Ken Hudson" wrote:

> And, if you are going to copy/drag your formula down from E12, you'll need to
> "anchor" your Sheet3 range reference in your formula:
>
> =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
>
> --
> Ken Hudson
>
>
> "Bill Kuunders" wrote:
>
> > So, in E12 on sheet2 enter
> > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > Regards
> > Bill K
> >
> > "Bill R" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > on
> > > that sheet and match it with the number in the 1st column to pull the
> > > factor
> > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > premium. Thanks.
> > >
> > > Term Rate
> > > 1 0.29000
> > > 2 0.57000
> > > 3 0.86000
> > > 4 1.03000
> > > 5 1.20000
> > > 6 1.37000
> > > 7 1.52000
> > > 8 1.67000
> > > 9 1.82000
> > > 10 1.95000
> > > 11 2.07000
> > > 12 2.20000
> > > 13 2.27000
> > > 14 2.33000
> > >
> > > "Bill R" wrote:
> > >
> > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > >> sheet
> > >> and it needs to pull a factor from a possible 85 choices located in
> > >> (B2:B86)
> > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > >> The
> > >> correct one to use is determined by the term of the loan which I need to
> > >> pull
> > >> from the 1st sheet in the workbook. The term of the loan will match the
> > >> number in the 1st column next to the correct factor in the 2nd column.
> > >> What
> > >> would be the correct formula to use? I'm stumped. Thanks.
> >
> >
> >

And, if you are going to copy/drag your formula down from E12, you'll need to
"anchor" your Sheet3 range reference in your formula:

=VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)

--
Ken Hudson


"Bill Kuunders" wrote:

> So, in E12 on sheet2 enter
> =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> Regards
> Bill K
>
> "Bill R" <[email protected]> wrote in message
> news:[email protected]...
> > This is the first two columns on sheet #3. I am trying to Pull the finance
> > term from a payment calculator in the 1st sheet which is located at (BA6)
> > on
> > that sheet and match it with the number in the 1st column to pull the
> > factor
> > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > premium. Thanks.
> >
> > Term Rate
> > 1 0.29000
> > 2 0.57000
> > 3 0.86000
> > 4 1.03000
> > 5 1.20000
> > 6 1.37000
> > 7 1.52000
> > 8 1.67000
> > 9 1.82000
> > 10 1.95000
> > 11 2.07000
> > 12 2.20000
> > 13 2.27000
> > 14 2.33000
> >
> > "Bill R" wrote:
> >
> >> The workbook contains three sheets. The formula needs to be in the 2nd
> >> sheet
> >> and it needs to pull a factor from a possible 85 choices located in
> >> (B2:B86)
> >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> >> The
> >> correct one to use is determined by the term of the loan which I need to
> >> pull
> >> from the 1st sheet in the workbook. The term of the loan will match the
> >> number in the 1st column next to the correct factor in the 2nd column.
> >> What
> >> would be the correct formula to use? I'm stumped. Thanks.
>
>
>

So, in E12 on sheet2 enter
=VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
Regards
Bill K

"Bill R" <[email protected]> wrote in message
news:[email protected]...
> This is the first two columns on sheet #3. I am trying to Pull the finance
> term from a payment calculator in the 1st sheet which is located at (BA6)
> on
> that sheet and match it with the number in the 1st column to pull the
> factor
> in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> premium. Thanks.
>
> Term Rate
> 1 0.29000
> 2 0.57000
> 3 0.86000
> 4 1.03000
> 5 1.20000
> 6 1.37000
> 7 1.52000
> 8 1.67000
> 9 1.82000
> 10 1.95000
> 11 2.07000
> 12 2.20000
> 13 2.27000
> 14 2.33000
>
> "Bill R" wrote:
>
>> The workbook contains three sheets. The formula needs to be in the 2nd
>> sheet
>> and it needs to pull a factor from a possible 85 choices located in
>> (B2:B86)
>> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
>> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
>> The
>> correct one to use is determined by the term of the loan which I need to
>> pull
>> from the 1st sheet in the workbook. The term of the loan will match the
>> number in the 1st column next to the correct factor in the 2nd column.
>> What
>> would be the correct formula to use? I'm stumped. Thanks.

This is the first two columns on sheet #3. I am trying to Pull the finance
term from a payment calculator in the 1st sheet which is located at (BA6) on
that sheet and match it with the number in the 1st column to pull the factor
in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
premium. Thanks.

Term Rate
1 0.29000
2 0.57000
3 0.86000
4 1.03000
5 1.20000
6 1.37000
7 1.52000
8 1.67000
9 1.82000
10 1.95000
11 2.07000
12 2.20000
13 2.27000
14 2.33000

"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

Looks like you need to get the 85 choices from the third sheet back into the
second sheet
next to the numbers in A2 to A86
you could then enter a vlookup function in say D1
such as =VLOOKUP(Sheet1!A23,A2:B86,2,FALSE)

i.e in english
this will look for the value in sheet1 A23 and find the same value in the
table on sheet 2 A2:B86
then it will pick the numer in the second column of that table.

--
Greetings from New Zealand
Bill K
"Bill R" <[email protected]> wrote in message
news:[email protected]...
> The workbook contains three sheets. The formula needs to be in the 2nd
> sheet
> and it needs to pull a factor from a possible 85 choices located in
> (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> The
> correct one to use is determined by the term of the loan which I need to
> pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column.
> What
> would be the correct formula to use? I'm stumped. Thanks.

Thank you. I had to join both columns together to make them work. Here is to
formula:=VLOOKUP('F&I Menu '!BA6,Tables!A2:B85,2,FALSE)



"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

If Term is only one column, then excel is telling you that it's having trouble
returning that second column of that single column range.

I'd create a new name (for both columns) and call it TermRate and use:
=VLOOKUP('F&I Menu '!BA6,TermRate,2,FALSE)

or maybe:
=index(rate,match('f&i menu '!ba6,term,0))

But I wouldn't use Rate as a name, either. Excel has a function called
=rate(). (And it would confuse the heck out of me!)

Bill R wrote:
>
> I input the following formula and it returns a #Ref error. (I named the
> columns: Column is named"Term" and Column 2 is named "Rate".) The term is
> pulling correctly but the rest of it does not seem to work. What can I do?
> Thanks.
>
> =VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)
>
> "Ken Hudson" wrote:
>
> > And, if you are going to copy/drag your formula down from E12, you'll need to
> > "anchor" your Sheet3 range reference in your formula:
> >
> > =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
> >
> > --
> > Ken Hudson
> >
> >
> > "Bill Kuunders" wrote:
> >
> > > So, in E12 on sheet2 enter
> > > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > > Regards
> > > Bill K
> > >
> > > "Bill R" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > > on
> > > > that sheet and match it with the number in the 1st column to pull the
> > > > factor
> > > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > > premium. Thanks.
> > > >
> > > > Term Rate
> > > > 1 0.29000
> > > > 2 0.57000
> > > > 3 0.86000
> > > > 4 1.03000
> > > > 5 1.20000
> > > > 6 1.37000
> > > > 7 1.52000
> > > > 8 1.67000
> > > > 9 1.82000
> > > > 10 1.95000
> > > > 11 2.07000
> > > > 12 2.20000
> > > > 13 2.27000
> > > > 14 2.33000
> > > >
> > > > "Bill R" wrote:
> > > >
> > > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > > >> sheet
> > > >> and it needs to pull a factor from a possible 85 choices located in
> > > >> (B2:B86)
> > > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > > >> The
> > > >> correct one to use is determined by the term of the loan which I need to
> > > >> pull
> > > >> from the 1st sheet in the workbook. The term of the loan will match the
> > > >> number in the 1st column next to the correct factor in the 2nd column.
> > > >> What
> > > >> would be the correct formula to use? I'm stumped. Thanks.
> > >
> > >
> > >

--

Dave Peterson

Looks like you need to get the 85 choices from the third sheet back into the
second sheet
next to the numbers in A2 to A86
you could then enter a vlookup function in say D1
such as =VLOOKUP(Sheet1!A23,A2:B86,2,FALSE)

i.e in english
this will look for the value in sheet1 A23 and find the same value in the
table on sheet 2 A2:B86
then it will pick the numer in the second column of that table.

--
Greetings from New Zealand
Bill K
"Bill R" <[email protected]> wrote in message
news:[email protected]...
> The workbook contains three sheets. The formula needs to be in the 2nd
> sheet
> and it needs to pull a factor from a possible 85 choices located in
> (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> The
> correct one to use is determined by the term of the loan which I need to
> pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column.
> What
> would be the correct formula to use? I'm stumped. Thanks.

I input the following formula and it returns a #Ref error. (I named the
columns: Column is named"Term" and Column 2 is named "Rate".) The term is
pulling correctly but the rest of it does not seem to work. What can I do?
Thanks.

=VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)

"Ken Hudson" wrote:

> And, if you are going to copy/drag your formula down from E12, you'll need to
> "anchor" your Sheet3 range reference in your formula:
>
> =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
>
> --
> Ken Hudson
>
>
> "Bill Kuunders" wrote:
>
> > So, in E12 on sheet2 enter
> > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > Regards
> > Bill K
> >
> > "Bill R" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > on
> > > that sheet and match it with the number in the 1st column to pull the
> > > factor
> > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > premium. Thanks.
> > >
> > > Term Rate
> > > 1 0.29000
> > > 2 0.57000
> > > 3 0.86000
> > > 4 1.03000
> > > 5 1.20000
> > > 6 1.37000
> > > 7 1.52000
> > > 8 1.67000
> > > 9 1.82000
> > > 10 1.95000
> > > 11 2.07000
> > > 12 2.20000
> > > 13 2.27000
> > > 14 2.33000
> > >
> > > "Bill R" wrote:
> > >
> > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > >> sheet
> > >> and it needs to pull a factor from a possible 85 choices located in
> > >> (B2:B86)
> > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > >> The
> > >> correct one to use is determined by the term of the loan which I need to
> > >> pull
> > >> from the 1st sheet in the workbook. The term of the loan will match the
> > >> number in the 1st column next to the correct factor in the 2nd column.
> > >> What
> > >> would be the correct formula to use? I'm stumped. Thanks.
> >
> >
> >

And, if you are going to copy/drag your formula down from E12, you'll need to
"anchor" your Sheet3 range reference in your formula:

=VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)

--
Ken Hudson


"Bill Kuunders" wrote:

> So, in E12 on sheet2 enter
> =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> Regards
> Bill K
>
> "Bill R" <[email protected]> wrote in message
> news:[email protected]...
> > This is the first two columns on sheet #3. I am trying to Pull the finance
> > term from a payment calculator in the 1st sheet which is located at (BA6)
> > on
> > that sheet and match it with the number in the 1st column to pull the
> > factor
> > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > premium. Thanks.
> >
> > Term Rate
> > 1 0.29000
> > 2 0.57000
> > 3 0.86000
> > 4 1.03000
> > 5 1.20000
> > 6 1.37000
> > 7 1.52000
> > 8 1.67000
> > 9 1.82000
> > 10 1.95000
> > 11 2.07000
> > 12 2.20000
> > 13 2.27000
> > 14 2.33000
> >
> > "Bill R" wrote:
> >
> >> The workbook contains three sheets. The formula needs to be in the 2nd
> >> sheet
> >> and it needs to pull a factor from a possible 85 choices located in
> >> (B2:B86)
> >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> >> The
> >> correct one to use is determined by the term of the loan which I need to
> >> pull
> >> from the 1st sheet in the workbook. The term of the loan will match the
> >> number in the 1st column next to the correct factor in the 2nd column.
> >> What
> >> would be the correct formula to use? I'm stumped. Thanks.
>
>
>

So, in E12 on sheet2 enter
=VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
Regards
Bill K

"Bill R" <[email protected]> wrote in message
news:[email protected]...
> This is the first two columns on sheet #3. I am trying to Pull the finance
> term from a payment calculator in the 1st sheet which is located at (BA6)
> on
> that sheet and match it with the number in the 1st column to pull the
> factor
> in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> premium. Thanks.
>
> Term Rate
> 1 0.29000
> 2 0.57000
> 3 0.86000
> 4 1.03000
> 5 1.20000
> 6 1.37000
> 7 1.52000
> 8 1.67000
> 9 1.82000
> 10 1.95000
> 11 2.07000
> 12 2.20000
> 13 2.27000
> 14 2.33000
>
> "Bill R" wrote:
>
>> The workbook contains three sheets. The formula needs to be in the 2nd
>> sheet
>> and it needs to pull a factor from a possible 85 choices located in
>> (B2:B86)
>> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
>> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
>> The
>> correct one to use is determined by the term of the loan which I need to
>> pull
>> from the 1st sheet in the workbook. The term of the loan will match the
>> number in the 1st column next to the correct factor in the 2nd column.
>> What
>> would be the correct formula to use? I'm stumped. Thanks.

This is the first two columns on sheet #3. I am trying to Pull the finance
term from a payment calculator in the 1st sheet which is located at (BA6) on
that sheet and match it with the number in the 1st column to pull the factor
in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
premium. Thanks.

Term Rate
1 0.29000
2 0.57000
3 0.86000
4 1.03000
5 1.20000
6 1.37000
7 1.52000
8 1.67000
9 1.82000
10 1.95000
11 2.07000
12 2.20000
13 2.27000
14 2.33000

"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

And, if you are going to copy/drag your formula down from E12, you'll need to
"anchor" your Sheet3 range reference in your formula:

=VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)

--
Ken Hudson


"Bill Kuunders" wrote:

> So, in E12 on sheet2 enter
> =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> Regards
> Bill K
>
> "Bill R" <[email protected]> wrote in message
> news:[email protected]...
> > This is the first two columns on sheet #3. I am trying to Pull the finance
> > term from a payment calculator in the 1st sheet which is located at (BA6)
> > on
> > that sheet and match it with the number in the 1st column to pull the
> > factor
> > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > premium. Thanks.
> >
> > Term Rate
> > 1 0.29000
> > 2 0.57000
> > 3 0.86000
> > 4 1.03000
> > 5 1.20000
> > 6 1.37000
> > 7 1.52000
> > 8 1.67000
> > 9 1.82000
> > 10 1.95000
> > 11 2.07000
> > 12 2.20000
> > 13 2.27000
> > 14 2.33000
> >
> > "Bill R" wrote:
> >
> >> The workbook contains three sheets. The formula needs to be in the 2nd
> >> sheet
> >> and it needs to pull a factor from a possible 85 choices located in
> >> (B2:B86)
> >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> >> The
> >> correct one to use is determined by the term of the loan which I need to
> >> pull
> >> from the 1st sheet in the workbook. The term of the loan will match the
> >> number in the 1st column next to the correct factor in the 2nd column.
> >> What
> >> would be the correct formula to use? I'm stumped. Thanks.
>
>
>

Looks like you need to get the 85 choices from the third sheet back into the
second sheet
next to the numbers in A2 to A86
you could then enter a vlookup function in say D1
such as =VLOOKUP(Sheet1!A23,A2:B86,2,FALSE)

i.e in english
this will look for the value in sheet1 A23 and find the same value in the
table on sheet 2 A2:B86
then it will pick the numer in the second column of that table.

--
Greetings from New Zealand
Bill K
"Bill R" <[email protected]> wrote in message
news:[email protected]...
> The workbook contains three sheets. The formula needs to be in the 2nd
> sheet
> and it needs to pull a factor from a possible 85 choices located in
> (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> The
> correct one to use is determined by the term of the loan which I need to
> pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column.
> What
> would be the correct formula to use? I'm stumped. Thanks.

If Term is only one column, then excel is telling you that it's having trouble
returning that second column of that single column range.

I'd create a new name (for both columns) and call it TermRate and use:
=VLOOKUP('F&I Menu '!BA6,TermRate,2,FALSE)

or maybe:
=index(rate,match('f&i menu '!ba6,term,0))

But I wouldn't use Rate as a name, either. Excel has a function called
=rate(). (And it would confuse the heck out of me!)

Bill R wrote:
>
> I input the following formula and it returns a #Ref error. (I named the
> columns: Column is named"Term" and Column 2 is named "Rate".) The term is
> pulling correctly but the rest of it does not seem to work. What can I do?
> Thanks.
>
> =VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)
>
> "Ken Hudson" wrote:
>
> > And, if you are going to copy/drag your formula down from E12, you'll need to
> > "anchor" your Sheet3 range reference in your formula:
> >
> > =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
> >
> > --
> > Ken Hudson
> >
> >
> > "Bill Kuunders" wrote:
> >
> > > So, in E12 on sheet2 enter
> > > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > > Regards
> > > Bill K
> > >
> > > "Bill R" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > > on
> > > > that sheet and match it with the number in the 1st column to pull the
> > > > factor
> > > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > > premium. Thanks.
> > > >
> > > > Term Rate
> > > > 1 0.29000
> > > > 2 0.57000
> > > > 3 0.86000
> > > > 4 1.03000
> > > > 5 1.20000
> > > > 6 1.37000
> > > > 7 1.52000
> > > > 8 1.67000
> > > > 9 1.82000
> > > > 10 1.95000
> > > > 11 2.07000
> > > > 12 2.20000
> > > > 13 2.27000
> > > > 14 2.33000
> > > >
> > > > "Bill R" wrote:
> > > >
> > > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > > >> sheet
> > > >> and it needs to pull a factor from a possible 85 choices located in
> > > >> (B2:B86)
> > > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > > >> The
> > > >> correct one to use is determined by the term of the loan which I need to
> > > >> pull
> > > >> from the 1st sheet in the workbook. The term of the loan will match the
> > > >> number in the 1st column next to the correct factor in the 2nd column.
> > > >> What
> > > >> would be the correct formula to use? I'm stumped. Thanks.
> > >
> > >
> > >

--

Dave Peterson

I input the following formula and it returns a #Ref error. (I named the
columns: Column is named"Term" and Column 2 is named "Rate".) The term is
pulling correctly but the rest of it does not seem to work. What can I do?
Thanks.

=VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)

"Ken Hudson" wrote:

> And, if you are going to copy/drag your formula down from E12, you'll need to
> "anchor" your Sheet3 range reference in your formula:
>
> =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
>
> --
> Ken Hudson
>
>
> "Bill Kuunders" wrote:
>
> > So, in E12 on sheet2 enter
> > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > Regards
> > Bill K
> >
> > "Bill R" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > on
> > > that sheet and match it with the number in the 1st column to pull the
> > > factor
> > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > premium. Thanks.
> > >
> > > Term Rate
> > > 1 0.29000
> > > 2 0.57000
> > > 3 0.86000
> > > 4 1.03000
> > > 5 1.20000
> > > 6 1.37000
> > > 7 1.52000
> > > 8 1.67000
> > > 9 1.82000
> > > 10 1.95000
> > > 11 2.07000
> > > 12 2.20000
> > > 13 2.27000
> > > 14 2.33000
> > >
> > > "Bill R" wrote:
> > >
> > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > >> sheet
> > >> and it needs to pull a factor from a possible 85 choices located in
> > >> (B2:B86)
> > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > >> The
> > >> correct one to use is determined by the term of the loan which I need to
> > >> pull
> > >> from the 1st sheet in the workbook. The term of the loan will match the
> > >> number in the 1st column next to the correct factor in the 2nd column.
> > >> What
> > >> would be the correct formula to use? I'm stumped. Thanks.
> >
> >
> >

So, in E12 on sheet2 enter
=VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
Regards
Bill K

"Bill R" <[email protected]> wrote in message
news:[email protected]...
> This is the first two columns on sheet #3. I am trying to Pull the finance
> term from a payment calculator in the 1st sheet which is located at (BA6)
> on
> that sheet and match it with the number in the 1st column to pull the
> factor
> in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> premium. Thanks.
>
> Term Rate
> 1 0.29000
> 2 0.57000
> 3 0.86000
> 4 1.03000
> 5 1.20000
> 6 1.37000
> 7 1.52000
> 8 1.67000
> 9 1.82000
> 10 1.95000
> 11 2.07000
> 12 2.20000
> 13 2.27000
> 14 2.33000
>
> "Bill R" wrote:
>
>> The workbook contains three sheets. The formula needs to be in the 2nd
>> sheet
>> and it needs to pull a factor from a possible 85 choices located in
>> (B2:B86)
>> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
>> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
>> The
>> correct one to use is determined by the term of the loan which I need to
>> pull
>> from the 1st sheet in the workbook. The term of the loan will match the
>> number in the 1st column next to the correct factor in the 2nd column.
>> What
>> would be the correct formula to use? I'm stumped. Thanks.

Thank you. I had to join both columns together to make them work. Here is to
formula:=VLOOKUP('F&I Menu '!BA6,Tables!A2:B85,2,FALSE)



"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

This is the first two columns on sheet #3. I am trying to Pull the finance
term from a payment calculator in the 1st sheet which is located at (BA6) on
that sheet and match it with the number in the 1st column to pull the factor
in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
premium. Thanks.

Term Rate
1 0.29000
2 0.57000
3 0.86000
4 1.03000
5 1.20000
6 1.37000
7 1.52000
8 1.67000
9 1.82000
10 1.95000
11 2.07000
12 2.20000
13 2.27000
14 2.33000

"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

Looks like you need to get the 85 choices from the third sheet back into the
second sheet
next to the numbers in A2 to A86
you could then enter a vlookup function in say D1
such as =VLOOKUP(Sheet1!A23,A2:B86,2,FALSE)

i.e in english
this will look for the value in sheet1 A23 and find the same value in the
table on sheet 2 A2:B86
then it will pick the numer in the second column of that table.

--
Greetings from New Zealand
Bill K
"Bill R" <[email protected]> wrote in message
news:[email protected]...
> The workbook contains three sheets. The formula needs to be in the 2nd
> sheet
> and it needs to pull a factor from a possible 85 choices located in
> (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> The
> correct one to use is determined by the term of the loan which I need to
> pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column.
> What
> would be the correct formula to use? I'm stumped. Thanks.

I input the following formula and it returns a #Ref error. (I named the
columns: Column is named"Term" and Column 2 is named "Rate".) The term is
pulling correctly but the rest of it does not seem to work. What can I do?
Thanks.

=VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)

"Ken Hudson" wrote:

> And, if you are going to copy/drag your formula down from E12, you'll need to
> "anchor" your Sheet3 range reference in your formula:
>
> =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
>
> --
> Ken Hudson
>
>
> "Bill Kuunders" wrote:
>
> > So, in E12 on sheet2 enter
> > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > Regards
> > Bill K
> >
> > "Bill R" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > on
> > > that sheet and match it with the number in the 1st column to pull the
> > > factor
> > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > premium. Thanks.
> > >
> > > Term Rate
> > > 1 0.29000
> > > 2 0.57000
> > > 3 0.86000
> > > 4 1.03000
> > > 5 1.20000
> > > 6 1.37000
> > > 7 1.52000
> > > 8 1.67000
> > > 9 1.82000
> > > 10 1.95000
> > > 11 2.07000
> > > 12 2.20000
> > > 13 2.27000
> > > 14 2.33000
> > >
> > > "Bill R" wrote:
> > >
> > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > >> sheet
> > >> and it needs to pull a factor from a possible 85 choices located in
> > >> (B2:B86)
> > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > >> The
> > >> correct one to use is determined by the term of the loan which I need to
> > >> pull
> > >> from the 1st sheet in the workbook. The term of the loan will match the
> > >> number in the 1st column next to the correct factor in the 2nd column.
> > >> What
> > >> would be the correct formula to use? I'm stumped. Thanks.
> >
> >
> >

This is the first two columns on sheet #3. I am trying to Pull the finance
term from a payment calculator in the 1st sheet which is located at (BA6) on
that sheet and match it with the number in the 1st column to pull the factor
in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
premium. Thanks.

Term Rate
1 0.29000
2 0.57000
3 0.86000
4 1.03000
5 1.20000
6 1.37000
7 1.52000
8 1.67000
9 1.82000
10 1.95000
11 2.07000
12 2.20000
13 2.27000
14 2.33000

"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

Thank you. I had to join both columns together to make them work. Here is to
formula:=VLOOKUP('F&I Menu '!BA6,Tables!A2:B85,2,FALSE)



"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

If Term is only one column, then excel is telling you that it's having trouble
returning that second column of that single column range.

I'd create a new name (for both columns) and call it TermRate and use:
=VLOOKUP('F&I Menu '!BA6,TermRate,2,FALSE)

or maybe:
=index(rate,match('f&i menu '!ba6,term,0))

But I wouldn't use Rate as a name, either. Excel has a function called
=rate(). (And it would confuse the heck out of me!)

Bill R wrote:
>
> I input the following formula and it returns a #Ref error. (I named the
> columns: Column is named"Term" and Column 2 is named "Rate".) The term is
> pulling correctly but the rest of it does not seem to work. What can I do?
> Thanks.
>
> =VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)
>
> "Ken Hudson" wrote:
>
> > And, if you are going to copy/drag your formula down from E12, you'll need to
> > "anchor" your Sheet3 range reference in your formula:
> >
> > =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
> >
> > --
> > Ken Hudson
> >
> >
> > "Bill Kuunders" wrote:
> >
> > > So, in E12 on sheet2 enter
> > > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > > Regards
> > > Bill K
> > >
> > > "Bill R" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > > on
> > > > that sheet and match it with the number in the 1st column to pull the
> > > > factor
> > > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > > premium. Thanks.
> > > >
> > > > Term Rate
> > > > 1 0.29000
> > > > 2 0.57000
> > > > 3 0.86000
> > > > 4 1.03000
> > > > 5 1.20000
> > > > 6 1.37000
> > > > 7 1.52000
> > > > 8 1.67000
> > > > 9 1.82000
> > > > 10 1.95000
> > > > 11 2.07000
> > > > 12 2.20000
> > > > 13 2.27000
> > > > 14 2.33000
> > > >
> > > > "Bill R" wrote:
> > > >
> > > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > > >> sheet
> > > >> and it needs to pull a factor from a possible 85 choices located in
> > > >> (B2:B86)
> > > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > > >> The
> > > >> correct one to use is determined by the term of the loan which I need to
> > > >> pull
> > > >> from the 1st sheet in the workbook. The term of the loan will match the
> > > >> number in the 1st column next to the correct factor in the 2nd column.
> > > >> What
> > > >> would be the correct formula to use? I'm stumped. Thanks.
> > >
> > >
> > >

--

Dave Peterson

And, if you are going to copy/drag your formula down from E12, you'll need to
"anchor" your Sheet3 range reference in your formula:

=VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)

--
Ken Hudson


"Bill Kuunders" wrote:

> So, in E12 on sheet2 enter
> =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> Regards
> Bill K
>
> "Bill R" <[email protected]> wrote in message
> news:[email protected]...
> > This is the first two columns on sheet #3. I am trying to Pull the finance
> > term from a payment calculator in the 1st sheet which is located at (BA6)
> > on
> > that sheet and match it with the number in the 1st column to pull the
> > factor
> > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > premium. Thanks.
> >
> > Term Rate
> > 1 0.29000
> > 2 0.57000
> > 3 0.86000
> > 4 1.03000
> > 5 1.20000
> > 6 1.37000
> > 7 1.52000
> > 8 1.67000
> > 9 1.82000
> > 10 1.95000
> > 11 2.07000
> > 12 2.20000
> > 13 2.27000
> > 14 2.33000
> >
> > "Bill R" wrote:
> >
> >> The workbook contains three sheets. The formula needs to be in the 2nd
> >> sheet
> >> and it needs to pull a factor from a possible 85 choices located in
> >> (B2:B86)
> >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> >> The
> >> correct one to use is determined by the term of the loan which I need to
> >> pull
> >> from the 1st sheet in the workbook. The term of the loan will match the
> >> number in the 1st column next to the correct factor in the 2nd column.
> >> What
> >> would be the correct formula to use? I'm stumped. Thanks.
>
>
>

So, in E12 on sheet2 enter
=VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
Regards
Bill K

"Bill R" <[email protected]> wrote in message
news:[email protected]...
> This is the first two columns on sheet #3. I am trying to Pull the finance
> term from a payment calculator in the 1st sheet which is located at (BA6)
> on
> that sheet and match it with the number in the 1st column to pull the
> factor
> in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> premium. Thanks.
>
> Term Rate
> 1 0.29000
> 2 0.57000
> 3 0.86000
> 4 1.03000
> 5 1.20000
> 6 1.37000
> 7 1.52000
> 8 1.67000
> 9 1.82000
> 10 1.95000
> 11 2.07000
> 12 2.20000
> 13 2.27000
> 14 2.33000
>
> "Bill R" wrote:
>
>> The workbook contains three sheets. The formula needs to be in the 2nd
>> sheet
>> and it needs to pull a factor from a possible 85 choices located in
>> (B2:B86)
>> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
>> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
>> The
>> correct one to use is determined by the term of the loan which I need to
>> pull
>> from the 1st sheet in the workbook. The term of the loan will match the
>> number in the 1st column next to the correct factor in the 2nd column.
>> What
>> would be the correct formula to use? I'm stumped. Thanks.

The workbook contains three sheets. The formula needs to be in the 2nd sheet
and it needs to pull a factor from a possible 85 choices located in (B2:B86)
in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
(A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
correct one to use is determined by the term of the loan which I need to pull
from the 1st sheet in the workbook. The term of the loan will match the
number in the 1st column next to the correct factor in the 2nd column. What
would be the correct formula to use? I'm stumped. Thanks.

Thank you. I had to join both columns together to make them work. Here is to
formula:=VLOOKUP('F&I Menu '!BA6,Tables!A2:B85,2,FALSE)



"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

If Term is only one column, then excel is telling you that it's having trouble
returning that second column of that single column range.

I'd create a new name (for both columns) and call it TermRate and use:
=VLOOKUP('F&I Menu '!BA6,TermRate,2,FALSE)

or maybe:
=index(rate,match('f&i menu '!ba6,term,0))

But I wouldn't use Rate as a name, either. Excel has a function called
=rate(). (And it would confuse the heck out of me!)

Bill R wrote:
>
> I input the following formula and it returns a #Ref error. (I named the
> columns: Column is named"Term" and Column 2 is named "Rate".) The term is
> pulling correctly but the rest of it does not seem to work. What can I do?
> Thanks.
>
> =VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)
>
> "Ken Hudson" wrote:
>
> > And, if you are going to copy/drag your formula down from E12, you'll need to
> > "anchor" your Sheet3 range reference in your formula:
> >
> > =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
> >
> > --
> > Ken Hudson
> >
> >
> > "Bill Kuunders" wrote:
> >
> > > So, in E12 on sheet2 enter
> > > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > > Regards
> > > Bill K
> > >
> > > "Bill R" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > > on
> > > > that sheet and match it with the number in the 1st column to pull the
> > > > factor
> > > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > > premium. Thanks.
> > > >
> > > > Term Rate
> > > > 1 0.29000
> > > > 2 0.57000
> > > > 3 0.86000
> > > > 4 1.03000
> > > > 5 1.20000
> > > > 6 1.37000
> > > > 7 1.52000
> > > > 8 1.67000
> > > > 9 1.82000
> > > > 10 1.95000
> > > > 11 2.07000
> > > > 12 2.20000
> > > > 13 2.27000
> > > > 14 2.33000
> > > >
> > > > "Bill R" wrote:
> > > >
> > > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > > >> sheet
> > > >> and it needs to pull a factor from a possible 85 choices located in
> > > >> (B2:B86)
> > > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > > >> The
> > > >> correct one to use is determined by the term of the loan which I need to
> > > >> pull
> > > >> from the 1st sheet in the workbook. The term of the loan will match the
> > > >> number in the 1st column next to the correct factor in the 2nd column.
> > > >> What
> > > >> would be the correct formula to use? I'm stumped. Thanks.
> > >
> > >
> > >

--

Dave Peterson

Looks like you need to get the 85 choices from the third sheet back into the
second sheet
next to the numbers in A2 to A86
you could then enter a vlookup function in say D1
such as =VLOOKUP(Sheet1!A23,A2:B86,2,FALSE)

i.e in english
this will look for the value in sheet1 A23 and find the same value in the
table on sheet 2 A2:B86
then it will pick the numer in the second column of that table.

--
Greetings from New Zealand
Bill K
"Bill R" <[email protected]> wrote in message
news:[email protected]...
> The workbook contains three sheets. The formula needs to be in the 2nd
> sheet
> and it needs to pull a factor from a possible 85 choices located in
> (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> The
> correct one to use is determined by the term of the loan which I need to
> pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column.
> What
> would be the correct formula to use? I'm stumped. Thanks.

I input the following formula and it returns a #Ref error. (I named the
columns: Column is named"Term" and Column 2 is named "Rate".) The term is
pulling correctly but the rest of it does not seem to work. What can I do?
Thanks.

=VLOOKUP('F&I Menu '!BA6,Term,2,FALSE)

"Ken Hudson" wrote:

> And, if you are going to copy/drag your formula down from E12, you'll need to
> "anchor" your Sheet3 range reference in your formula:
>
> =VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)
>
> --
> Ken Hudson
>
>
> "Bill Kuunders" wrote:
>
> > So, in E12 on sheet2 enter
> > =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> > Regards
> > Bill K
> >
> > "Bill R" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is the first two columns on sheet #3. I am trying to Pull the finance
> > > term from a payment calculator in the 1st sheet which is located at (BA6)
> > > on
> > > that sheet and match it with the number in the 1st column to pull the
> > > factor
> > > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > > premium. Thanks.
> > >
> > > Term Rate
> > > 1 0.29000
> > > 2 0.57000
> > > 3 0.86000
> > > 4 1.03000
> > > 5 1.20000
> > > 6 1.37000
> > > 7 1.52000
> > > 8 1.67000
> > > 9 1.82000
> > > 10 1.95000
> > > 11 2.07000
> > > 12 2.20000
> > > 13 2.27000
> > > 14 2.33000
> > >
> > > "Bill R" wrote:
> > >
> > >> The workbook contains three sheets. The formula needs to be in the 2nd
> > >> sheet
> > >> and it needs to pull a factor from a possible 85 choices located in
> > >> (B2:B86)
> > >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> > >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> > >> The
> > >> correct one to use is determined by the term of the loan which I need to
> > >> pull
> > >> from the 1st sheet in the workbook. The term of the loan will match the
> > >> number in the 1st column next to the correct factor in the 2nd column.
> > >> What
> > >> would be the correct formula to use? I'm stumped. Thanks.
> >
> >
> >

This is the first two columns on sheet #3. I am trying to Pull the finance
term from a payment calculator in the 1st sheet which is located at (BA6) on
that sheet and match it with the number in the 1st column to pull the factor
in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
premium. Thanks.

Term Rate
1 0.29000
2 0.57000
3 0.86000
4 1.03000
5 1.20000
6 1.37000
7 1.52000
8 1.67000
9 1.82000
10 1.95000
11 2.07000
12 2.20000
13 2.27000
14 2.33000

"Bill R" wrote:

> The workbook contains three sheets. The formula needs to be in the 2nd sheet
> and it needs to pull a factor from a possible 85 choices located in (B2:B86)
> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates. The
> correct one to use is determined by the term of the loan which I need to pull
> from the 1st sheet in the workbook. The term of the loan will match the
> number in the 1st column next to the correct factor in the 2nd column. What
> would be the correct formula to use? I'm stumped. Thanks.

And, if you are going to copy/drag your formula down from E12, you'll need to
"anchor" your Sheet3 range reference in your formula:

=VLOOKUP(Sheet1!BA6,Sheet3!$A$1:$B$14,2,FALSE)

--
Ken Hudson


"Bill Kuunders" wrote:

> So, in E12 on sheet2 enter
> =VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
> Regards
> Bill K
>
> "Bill R" <[email protected]> wrote in message
> news:[email protected]...
> > This is the first two columns on sheet #3. I am trying to Pull the finance
> > term from a payment calculator in the 1st sheet which is located at (BA6)
> > on
> > that sheet and match it with the number in the 1st column to pull the
> > factor
> > in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> > premium. Thanks.
> >
> > Term Rate
> > 1 0.29000
> > 2 0.57000
> > 3 0.86000
> > 4 1.03000
> > 5 1.20000
> > 6 1.37000
> > 7 1.52000
> > 8 1.67000
> > 9 1.82000
> > 10 1.95000
> > 11 2.07000
> > 12 2.20000
> > 13 2.27000
> > 14 2.33000
> >
> > "Bill R" wrote:
> >
> >> The workbook contains three sheets. The formula needs to be in the 2nd
> >> sheet
> >> and it needs to pull a factor from a possible 85 choices located in
> >> (B2:B86)
> >> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
> >> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
> >> The
> >> correct one to use is determined by the term of the loan which I need to
> >> pull
> >> from the 1st sheet in the workbook. The term of the loan will match the
> >> number in the 1st column next to the correct factor in the 2nd column.
> >> What
> >> would be the correct formula to use? I'm stumped. Thanks.
>
>
>

So, in E12 on sheet2 enter
=VLOOKUP(Sheet1!BA6,Sheet3!A2:B86,2,FALSE)
Regards
Bill K

"Bill R" <[email protected]> wrote in message
news:[email protected]...
> This is the first two columns on sheet #3. I am trying to Pull the finance
> term from a payment calculator in the 1st sheet which is located at (BA6)
> on
> that sheet and match it with the number in the 1st column to pull the
> factor
> in the 2nd column beside it to (E12) on the 2nd sheet to calculate the Ins
> premium. Thanks.
>
> Term Rate
> 1 0.29000
> 2 0.57000
> 3 0.86000
> 4 1.03000
> 5 1.20000
> 6 1.37000
> 7 1.52000
> 8 1.67000
> 9 1.82000
> 10 1.95000
> 11 2.07000
> 12 2.20000
> 13 2.27000
> 14 2.33000
>
> "Bill R" wrote:
>
>> The workbook contains three sheets. The formula needs to be in the 2nd
>> sheet
>> and it needs to pull a factor from a possible 85 choices located in
>> (B2:B86)
>> in the 3rd sheet. The choices are numbered 1 Thru 85 in the first column
>> (A2:A86) in the 2nd sheet. These factors are for Credit Disability rates.
>> The
>> correct one to use is determined by the term of the loan which I need to
>> pull
>> from the 1st sheet in the workbook. The term of the loan will match the
>> number in the 1st column next to the correct factor in the 2nd column.
>> What
>> would be the correct formula to use? I'm stumped. Thanks.