# Solved: Need help with a vlookup formula

Discussion in 'Business Applications' started by anonymos105, Oct 3, 2008.

Not open for further replies.

Joined:
May 6, 2005
Messages:
30
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.

2. ### Rollin_Again

Joined:
Sep 4, 2003
Messages:
4,912

Regards,
Rollin

Joined:
May 6, 2005
Messages:
30
I have attached a sample of the worksheet as you can see the column in yellow is not calculating right.

File size:
22.5 KB
Views:
61
4. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
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

Joined:
May 6, 2005
Messages:
30
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.

As Seen On

### Welcome to Tech Support Guy!

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!