Solved: Need help with a vlookup formula

[anonymos105]

I am working on a spreadsheet that calculates life insurance rates and I can't for the life of me get this formula to work. Here is the situation

I need it to take the age shown in column d of sheet 1
It needs to find that age in column 1 of sheet 2 and return the matching result in column 2 of sheet 2
this all needs to appear in column L of sheet 1

so for example
cell L2 needs to show the rate that applies to employee 1
in cell D2 it has his age of 60
I need L2 to take the age of 60 in cell D2 find that age in sheet 2 column 1 (in this case A47) and return the corresponding rate in sheet 2 B47 (in this case $1.21)

Can anybody help with this, everytime I have tried to figure out the formula I get some random number thats not even on any of the spreadsheets.

 

[Rollin_Again]

Please post your sample workbook.

Regards,
Rollin

 

[anonymos105]

I have attached a sample of the worksheet as you can see the column in yellow is not calculating right.

 

[Yorkshire Guy]

Hi anonymous,

Not wanting to step on Rollin's thread, but weird formula you have in L2!
=VLOOKUP(D2,RATE,1+(G2="N")+1)

Where is RATE (maybe on another sheet, cos' I noticed your spreedsheet opened asking for links to other books) and what's with the 1+(G2='N')+1 bit, what are you trying to achieve?


Just use:
=VLOOKUP(D2,Rates!A:B,2,FALSE)



lol
Hew

 

[anonymos105]

Thanks Yorkshire, that appears to have did it. Although I must say I have no qualms about admitting I was in left field and didn't have a clue as to the formula. I have never had to use vlookup before, although I knew it was the formula needed, and the excel instructions on how to use it may as well be written in sandscrit for all their worth. I got that wacky formula from another older spreadsheet that did a similar function. But as you can tell it wasn't what I was looking for.