Excel 2010 - multiple if and vlookup statement help 2

[ITALIAORIANA]

Hi Everyone,

I am having a problem combining IF statements with VLOOKUP. The statements all work perfect independently but I need to combine them. I tried just putting a comma between each statement to combine them but I just get #VALUE returned. These are looking up rates for member premiums which will then be loaded to the database.


=IF(A2="LICS",VLOOKUP(B2,RATE_RESULTS!$A$2:$F$23,3,FALSE))

=IF(A2="PRTC",VLOOKUP(B2,RATE_RESULTS!$A$2:$F$23,4,FALSE))

=IF(A2="PRTD",VLOOKUP(B7,RATE_RESULTS!$A$2:$F$23,5,FALSE))


Thanks,
Deana

 

[zelgar]

Try

=IF(A2="LICS",VLOOKUP(B2,RATE_RESULTS!$A$2:$F$23,3,FALSE),IF(A2="PRTC",VLOOKUP(B2,RATE_RESULTS!$A$2:$F$23,4,FALSE),IF(A2="PRTD",VLOOKUP(B7,RATE_RESULTS!$A$2:$F$23,5,FALSE))))

 

[Andrzejek]

If you would be open to learn a little VBA, you could create a simple macro to do what you need. It would be a lot easier to see the logic and a lot simpler to modified/add, if needed.

You can get a lot of VBA help at this TT Forum.

Just a suggestion


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ITALIAORIANA]

OMG zelgar, THANKS so much! This works perfectly. I had my () all mixed up and could NOT get it to work.

Andrzejek - Thanks, I am going to check out the VB code. I am completely fascinated by the things excel can do and the vast amount that I don't know about yet.


Thanks
Deana

 

[zelgar]

another way of doing your IF statement using an ARRAY since you'r looking at the end result in the same range for the VLOOKUP table...

=IF(OR(A2={"LICS","PRTC","PRTD"}),VLOOKUP(B2,RATE_RESULTS!$A$2:$F$23,VLOOKUP(A2,{"LICS",3;"PRTC",4;"PRTD",5},2,FALSE),FALSE))

 

[SkipVought]

@Deana, I'm curious about the B[highlight]7[/highlight] reference. It seems odd that you would have two references to row 2 and one to row 7, rather than another row 2 reference???

 

[SkipVought]

...that is in this third formula...

=IF(A2="PRTD",VLOOKUP(B7,RATE_RESULTS!$A$2:$F$23,5,FALSE))

 

[zelgar]

Hmm.. I didn't notice that Skip when I was making my second attempt with the array. If the OP needs it to refer to cell B7 when A2="PRTD" then my last formula needs to be revised to

=IF(OR(A2={"LICS","PRTC","PRTD"}),VLOOKUP(VLOOKUP(A2,{"LICS",B2;"PRTC",B2;"PRTD",B7},2,FALSE),RATE_RESULTS!$A$2:$F$23,VLOOKUP(A2,{"LICS",3;"PRTC",4;"PRTD",5},2,FALSE),FALSE))