formula help vlookup

[CRAIG20]

Hi. I have a vlookup formula.

The current formula is =VLOOKUP(IF(P1=0,5,P1),Rebate1,2,FALSE)

However I now have 2 named ranges and whatever is in cell O1 will determine which named range to lookup.

So if Cell O1 = "1" IT LOOKSUP NAMED RANGE "Rebate1" If O1 = "2" it looks up named range "Rebate2"

Can anyone help me expand the vlookup formula to add this condition?

Thanks in advance.

 

[jasonb75]

Maybe

=VLOOKUP(IF(P1=0,5,P1),IF(O1=1,Rebate1,Rebate2),2,FALSE)

 

[CRAIG20]

Thank you very much

 

[CRAIG20]

Maybe

=VLOOKUP(IF(P1=0,5,P1),IF(O1=1,Rebate1,Rebate2),2,FALSE)

Hi. Thanks for your help.

I have added a third named range Rebate3.

If O1 = 3 it looks up Rebate3

Can you help me with formula to include the 3 conditions?

Many thanks.

 

[jasonb75]

There are at least 3 ways that should work (I haven't tested the last one)

=VLOOKUP(IF(P1=0,5,P1),IF(O1=1,Rebate1,IF(O2=2,Rebate2,Rebate3)),2,FALSE)

=VLOOKUP(IF(P1=0,5,P1),CHOOSE(O1,Rebate1,Rebate2,Rebate3),2,FALSE)

=VLOOKUP(IF(P1=0,5,P1),INDEX((Rebate1,Rebate2,Rebate3),0,0,O1),2,FALSE)

A better way would be to take the second column of Rebate2 and add it to the right of Rebate1 (as column 3), then do the same with Rebate3 (as column 4 of Rebate1), then use

=VLOOKUP(IF(P1=0,5,P1),Rebate1,O1+1,FALSE)

Which would be much easier to manage if you wanted to add Rebate4, Rebate5, etc.

 

[CRAIG20]

There are at least 3 ways that should work (I haven't tested the last one)

=VLOOKUP(IF(P1=0,5,P1),IF(O1=1,Rebate1,IF(O2=2,Rebate2,Rebate3)),2,FALSE)

=VLOOKUP(IF(P1=0,5,P1),CHOOSE(O1,Rebate1,Rebate2,Rebate3),2,FALSE)

=VLOOKUP(IF(P1=0,5,P1),INDEX((Rebate1,Rebate2,Rebate3),0,0,O1),2,FALSE)

A better way would be to take the second column of Rebate2 and add it to the right of Rebate1 (as column 3), then do the same with Rebate3 (as column 4 of Rebate1), then use

=VLOOKUP(IF(P1=0,5,P1),Rebate1,O1+1,FALSE)

Which would be much easier to manage if you wanted to add Rebate4, Rebate5, etc.

Great. Thank you very much