Vlookup with Nested If statement

[Talmeida13]

Hi,
I am trying to determine commission %'s among 3 different group of products. Each group has a different rate of commission % based on their Gross Margin %.
I have 3 arrays with the different GM%'s and the commission values and i cannot seem to get this to work.
Here is the formula:
=IF(K2="Prod A",VLOOKUP(S2,Chart!$E$1:$F$7,2,TRUE),IF(K2="Prod B",VLOOKUP(S2,Chart!$I$1:$J$7,2,TRUE),IF(K2="Prod C",VLOOKUP(S2,Chart!$N$1:$N$7,2,TRUE))))
This formula works for one product group and returns the correct value, however when i add the rest of the groups it fails.
=IF(K2="Prod A",VLOOKUP(S2,Chart!$E$1:$F$7,2,TRUE)

Any help would be great.
Thanks.

 

[richh]

The last if statement doesn't have a false clause. Also, the second column reference is different. All other columns in the other VLOOKUPS are Column X:1 to COLUMN X+1:7. The third is COLUMN X:1 to COLUMN X:7

 

[richh]

You may also want to use FALSE instead of TRUE. Exact matches are better than approx. Then, ensure your entries are devoid of leading/trailing spaces in both your entry and reference tables. I just confirmed that the code above works once the third VLOOKUP is corrected to reference two columns instead of one.

=IFERROR(IF(Z1965="Prod A",VLOOKUP(AA1965,Z1969:AA1971,2,FALSE),IF(Z1965="Prod B",VLOOKUP(AA1965,Z1974:AA1976,2,FALSE),IF(Z1965="Prod C",VLOOKUP(AA1965,Z1978:AA1980,2,FALSE)))), "I done Goofed")

Prod A	Apple	0.3​
Prod B	Banana	0.6​
Prod C	Cactus	0.9​
Orange	0.1​
Zerbra	0.2​
Apple	0.3​
Laptop	0.4​
Yellowstone	0.5​
Banana	0.6​
Ol' Jeb	0.7​
Mr. Excel	0.8​
Cactus	0.9​