PDA

View Full Version : VLOOKUP question



austenr
08-13-2010, 08:49 AM
I'm using a named range in my VLOOKUP and if I hide some of the columns if doesn't work. Can you not hide columns in a VLOOKUP?

Bob Phillips
08-13-2010, 09:31 AM
I just tried it and it worked fine here. Which columns are hidden?

austenr
08-13-2010, 09:33 AM
O and P

austenr
08-13-2010, 09:33 AM
Might be user error. That would be me.

austenr
08-13-2010, 10:24 AM
Thanks Bob. Here is a related VLOOKUP question.

Have three cells named Age, SPAge and Split.

If Split = "Yes" and Age < 65 and SpAge > 65 then I need to use another named range called "Years of Service" to do a VLOOKUP on a named range called "Indemnity", then I need to take column 7 and 16, add them and divide by 12.

Can you do that in a formula?

Bob Phillips
08-13-2010, 12:07 PM
Try

=(VLOOKUP(YearsOfService,Indemnity,7,FALSE)+VLOOKUP(YearsOfService,Indemnit y,16,FALSE))/12

austenr
08-13-2010, 12:08 PM
Never thought about nested VLOOKUP's. Ill give it a go.

austenr
08-13-2010, 12:13 PM
Spot on. Thanks.

austenr
08-13-2010, 12:50 PM
Hmmm...When I put this in a Sub and run it I get wacky answers:

=vlookup(YearsofService, Indemnity, 7, False) + Vlookup(YearsofService, Indemnity, 16, FALSE)/12

7 = 3563.33
16 = 2691.43

Am I missing a () somewhere?

austenr
08-13-2010, 12:51 PM
:banghead::banghead::banghead::banghead::banghead:

Never mind I see it.

austenr
08-13-2010, 01:17 PM
One more question. Trying to test both ways in this. Works fuine with the first If, but the second one gives "#VALUE" error.

=IF(AND(Age>65,SPAge<65),"Yes","No"),IF(AND(Age<65,SPAge>65),"Yes","No")

Bob Phillips
08-13-2010, 01:49 PM
Try this

=IF(OR(AND(Age>65,SpAge<65),AND(Age<65,SpAge>65)),"Yes","No")

austenr
08-13-2010, 01:50 PM
You know it works. Thanks Bob.