View previous topic :: View next topic 
Author 
Message 
RichardM Newbie
Joined: 03 Aug 2008 Posts: 2

Posted: Sun Aug 03, 2008 3:50 pm Post subject: I have far too many IF's.. 


For a small project I'm trying to multiply numbers with their base multipliers. I have 38 different items, and the script compares the name of the current item with the list, and when it hits a match of names, moves over to the corresponding base number and uses the multiplier on it to work out the new number, thus placing it in the cell.
There are roughly 2500 lines of multipliers, so understandably I don't really want to go down them and do this one by one..
Thanks for your help in advance, and here is the rather long IF that I tried. It should work, but I get error 512, which either means too many IF's or too much typing, I don't know which.
Code:  =IF(A2="G$1";PRODUCT(D3;H$1);IF(A2="G$2";PRODUCT(D3;H$2);IF(A2="G$3";PRODUCT(D3;H$3);IF(A2="G$4";PRODUCT(D3;H$4);IF(A2="G$5";PRODUCT(D3;H$5);IF(A2="G$6";PRODUCT(D3;H$6);IF(A2="G$7";PRODUCT(D3;H$7);IF(A2="G$8";PRODUCT(D3;H$8);IF(A2="G$9";PRODUCT(D3;H$9);IF(A2="G$10";PRODUCT(D3;H$10);IF(A2="G$11";PRODUCT(D3;H$11);IF(A2="G$12";PRODUCT(D3;H$12);IF(A2="G$13";PRODUCT(D3;H$13);IF(A2="G$14";PRODUCT(D3;H$14);IF(A2="G$15";PRODUCT(D3;H$15);IF(A2="G$16";PRODUCT(D3;H$16);IF(A2="G$17";PRODUCT(D3;H$17);IF(A2="G$18";PRODUCT(D3;H$18);IF(A2="G$19";PRODUCT(D3;H$19);IF(A2="G$20";PRODUCT(D3;H$20);IF(A2="G$21";PRODUCT(D3;H$21);IF(A2="G$22";PRODUCT(D3;H$22);IF(A2="G$23";PRODUCT(D3;H$23);IF(A2="G$24";PRODUCT(D3;H$24);IF(A2="G$25";PRODUCT(D3;H$25);IF(A2="G$26";PRODUCT(D3;H$26);IF(A2="G$27";PRODUCT(D3;H$27);IF(A2="G$28";PRODUCT(D3;H$28);IF(A2="G$29";PRODUCT(D3;H$29);IF(A2="G$30";PRODUCT(D3;H$30);IF(A2="G$31";PRODUCT(D3;H$31);IF(A2="G$32";PRODUCT(D3;H$32);IF(A2="G$33";PRODUCT(D3;H$33);IF(A2="G$34";PRODUCT(D3;H$34);IF(A2="G$35";PRODUCT(D3;H$35);IF(A2="G$36";PRODUCT(D3;H$36);IF(A2="G$37";PRODUCT(D3;H$37);IF(A2="G$38";PRODUCT(D3;H$38)))))))))))))))))))))))))))))))))))))) 


Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Sun Aug 03, 2008 4:53 pm Post subject: 


Have you thought of using VLOOKUP() for example?
Enter [type] a value that you would want from the list in column G into A2. Then, in the cell where this formula is to appear put this:
=D3*VLOOKUP(A2;G1:H38;2;0)
David. 

Back to top 


RichardM Newbie
Joined: 03 Aug 2008 Posts: 2

Posted: Mon Aug 04, 2008 9:23 am Post subject: 


Thanks David, that worked brilliantly. I had looked at VLOOKUP but wasn't sure what I was doing with the index, though now I know how to use it, so thanks for that too 

Back to top 


