Multilple IF and LOOKUP statements

[Simon Jones]

HI

I am setting up a pricing grid and am having no luck doing multiple look ups and if statements.

My trouble is that I have 8 columns of different prices as each price depends on quantity of the job so I need to do 8 if statements and 8 lookups to match to find the correct price per volume.

First I need to check the cell D12 for the quantity and then that cell G21 is populated before doing a look up on the correct price.

I am using the following which works OK but when I try to add a third IF/Look-UP it does not work as there are too many statements;
=IF(D12<5001,IF(G21="*"," ",VLOOKUP(G21,Sheet1!$A$4:Sheet1!$J$29,3)),IF(D12<10001,IF(G21="*"," ",VLOOKUP(G21,Sheet1!$A$4:Sheet1!$J$29,4))))

Are there any other ways I can add so many statements – maybe other types of command?
Any ideas? Thanks!

[seasider89]

I don't know what you mean by too many statements? Are you getting some kind of error message?

[Simon Jones]

Sorry, the message is "You've entered too many arguments for this function." not too many statements

[Special-K]

Can you post a sample file, there's probably quick way of doing it in ONE statement

[Simon Jones]

I think that i have got around this by A) putting my arguements in the correct place inside the brackets!!!! and B) keeping the data for the look up on the same work sheet rather than a separate one so reducing the amount of code.

It now works ok having 8 IF / Lookup arguements as follows;

=IF($D$12<5001,VLOOKUP(G21,$A$4:$J$39,3),IF($D$12<10001,VLOOKUP(G21,$A$4:$J$39,4),IF($D$12<25001,VLOOKUP(G21,$A$4:$J$39,5),IF($D$12<50001,VLOOKUP(G21,$A$4:$J$39,6),IF($D$12<100001,VLOOKUP(G21,$A$4:$J$39,7),IF($D$12<200001,VLOOKUP(G21,$A$4:$J$39,8),IF($D$12<500001,VLOOKUP(G21,$A$4:$J$39,9),IF($D$12<1000001,VLOOKUP(G21,$A$4:$J$39,10)))))))))

[seasider89]

It's definitely the former rather than the latter. You've entered too many arguments for this function doesn't mean your formula is too long, it means your syntax is wrong.

So for example if you entered the following:

=SUMIF(A:A,D1,B:B,D2)

It would return an error. The formula asks for 3 pieces of information seperated by commas and you've given it four. I've never come across a limit for the length of the formula itself.

Hope this helps.