Vlookup, What is correct formula for problem below?

[Guest]

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.

 

[Bill Kuunders]

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.

 

[Guest]

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 Kuunders]

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

 

[Guest]

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)

 

[Guest]

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)

 

[Dave Peterson]

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!)

 

[Guest]

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)