Can you nest an IF - Vlookup?"

[sbarber04]

Good Morning,


I am having issues with the commissions file template provided; we have two different commissions structures based on tenure, each structure has a tiered value system.




I have tried

=IF(G5="New",VLOOKUP(K5,$A$7:$C$11,3,TRUE),VLOOKUP(K5,$A$15:$C$20,3,TRUE))

=IF(G5="New",VLOOKUP(K5,$A$6:$C$11,3,1),VLOOKUP(K5,$A$15:$C$20,3,1))


However it does not work consistently, attached in the sample file is the data; in column O is the formula above but as you can see in column P the data does not match Column L 100% of the time. Is there a better formula to use or is there a different formula that I should try?

the errors occur with the wrong tier for payout

Thanks for your help!
~S

 

[GraH - Guido]

Hi,
Your cell G5 contains the value "New " (with a space), not "New" as in your formula. So the test in the IF(G5="New" returns false, where you'd expect true.

I notice that in the column "L" you only do 1 type of vlookup (in the first table), that is also why you have "does not work" in your column P.

 

[sbarber04]

that is exactly what fixed it! Thank you! Pesky extra space