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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.