VLOOKUP question

[austenr]

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]

I just tried it and it worked fine here. Which columns are hidden?

[austenr]

O and P

[austenr]

Might be user error. That would be me.

[austenr]

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]

Try

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

[austenr]

Never thought about nested VLOOKUP's. Ill give it a go.

[austenr]

Spot on. Thanks.

[austenr]

Hmmm...When I put this in a Sub and run it I get wacky answers:

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

7 = 3563.33
16 = 2691.43

Am I missing a () somewhere?

[austenr]

Never mind I see it.

[austenr]

One more question. Trying to test both ways in this. Works fuine with the first If, but the second one gives "#VALUE" error.

[VBA]=IF(AND(Age>65,SPAge<65),"Yes","No"),IF(AND(Age<65,SPAge>65),"Yes","No")[/VBA]

[Bob Phillips]

Try this

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

[austenr]

You know it works. Thanks Bob.