I have another complex (in my mind) Excel formula that I can't figure out.
I first need to do a validation on one column (A) trying to validate if the "CAAA" option was selected...if it was, I need to multiply a VLookup value, multiply it by an exchange rate, and then lastly multiply it by another variable. IF anything other than CAAA is selected in Col A, I simply want to multiple the VLookup value by a separate variable.
My formula is:
=IFERROR(IF(A3="CAAA",VLOOKUP(B3,Table1,3,))*Setup!$B$10,0)*IF(C3=1,Setup!$G$2,IF(C3=2,Setup!$G$3,IF(C3=3,Setup!$G$4,VLOOKUP(B3,Table1,2,))*IF(C3=1,Setup!$F$2,IF(C3=2,Setup!$F$3,IF(C3=3,Setup!$F$4)))))
I know I am running into the issue with VLookup only being able to consider one value, but I'm not sure what other command to use in it's place...or how else to accomplish my task.
I have attached a sample of the file I am working on, which partially works...If CAAA is selected in Col A, the formula successfully runs as long as a value or 1 or 2 is selected for Col C (if a value of three is entered, there is an improper calculation made (probably because a new VLookup follows this option. IF anything other than CAAA is selected in Col A, the formula returns a "0" (I'm assuming a result of error checking).
Any help would be appreciated.
Thanks,
Bill
Bookmarks