Quite simply I'm having a lot of trouble attempting to resolve this issue. - However I think my issue is that I MAY be using the incorrect function.
I'm attempting to use a LOOKUP function more than once in a single cell.
Here's the forumla that currently works:
=IF(G21="N/A",0,IF(G21="Perf",LOOKUP(H21,R7:U7,IF(D7<=4572,R8:U8,IF(D7<=7620,R9:U9,IF(D7<=10668,R10:U10,R11:U11))))*(((D7*D8)+((D7*D9)*2)+(D8*D9))/1000000)))
Ie. If "N/A" is Selected, then returns a value of zero. (Works). If "Perf" is selected then the cell returns an answer based on a value using a LOOKUP function (Also Works!)
However, if I attempt to add another LOOKUP function in to that cell such as:
IF(G21="N/A",0,IF(G21="Perf",LOOKUP(H21,R7:U7,IF(D7<=4572,R8:U8,IF(D7<=7620,R9:U9,IF(D7<=10668,R10:U10,R11:U11))))*(((D7*D8)+((D7*D9)*2)+(D8*D9))/1000000))),IF(G21="Zintec",(LOOKUP(H21,R13:U13,IF(D7<=4572,R14:U14,IF(D7<=7620,R15:U15,IF(D7<=10668,R16:U16,R17:U17))))*(((D7*D8)+((D7*D9)*2)+(D8*D9))/1000000)))
All I seem to get is #VALUE! no matter what I choose in the drop down cell validation cell.
Is it not possible to use the LOOKUP function more than once in a cell?