"If" statements

[Guest]

Hi all,

I have a cell with the following "If" statements. Im aware that you can only
have 7 "IF" statements in a cell but how would i change what i have to a
working formula? I believe "Lookup" is an option but never used it before?

IF(AND('Order Data'!C25="G",'Order Data'!C7=5),'Order Data'!K38,
IF(AND('Order Data'!C25="T",'Order Data'!C7=5),'Order Data'!K11,
IF(AND('Order Data'!C25="GC",'Order Data'!C7=5),'Order Data'!K29,
IF(AND('Order Data'!C25="I",'Order Data'!C7=5),'Order Data'!K20,
IF(AND('Order Data'!C25="G",'Order Data'!C7=17),'Order Data'!L38,
IF(AND('Order Data'!C25="T",'Order Data'!C7=17),'Order Data'!L11,
IF(AND('Order Data'!C25="GC",'Order Data'!C7=17),'Order Data'!L29,
IF(AND('Order Data'!C25="I",'Order Data'!C7=17),'Order Data'!L20,
IF(AND('Order Data'!C25="G",'Order Data'!C7=27),'Order Data'!M38,
IF(AND('Order Data'!C25="T",'Order Data'!C7=27),'Order Data'!M11,
IF(AND('Order Data'!C25="GC",'Order Data'!C7=27),'Order Data'!M29,
IF(AND('Order Data'!C25="I",'Order Data'!C7=27),'Order
Data'!M20,""))))))))))))

Cheers

 

[Guest]

In A1 thru B12 put:

G5 'Order Data'!K38
T5 'Order Data'!K11
GC5 'Order Data'!K29
I5 'Order Data'!K20
G17 'Order Data'!L38
T17 'Order Data'!L11
GC17 'Order Data'!L29
I17 'Order Data'!L20
G27 'Order Data'!M38
T27 'Order Data'!M11
GC27 'Order Data'!M29
I27 'Order Data'!M20


and then

=INDIRECT(VLOOKUP(C25&C7,A1:B12,2,0))

 

[Guest]

Hi:

I used two vlookups to get the row and the column the formula is quite long
but shorter than ifs but you need to tables:

=IF(ISNA(((VLOOKUP('Order Data'!C7,$B$23:$C$25,2,FALSE)&VLOOKUP('Order
Data'!C25,$B$17:$C$20,2,FALSE)))),"",INDIRECT((VLOOKUP('Order
Data'!C7,$B$23:$C$25,2,FALSE)&VLOOKUP('Order Data'!C25,$B$17:$C$20,2,FALSE))))

the tables were on a sheet at B17:C20 for the row:

G 38
T 11
GC 29
I 20

and B23:C25 for the column

5 K
17 L
27 M

I then used indirect to calculate the address and give me the value.

I put an if (isna()) to pick up bad values.

 

[Guest]

=indirect("'Order Data'!"&if('Order Data'!C7=5,"K",if(Order
Data'!C7=17,"L","M"))&if(Order Data'!C25="G","38",if(Order
Data'!C25="T","11","29")))
or
=indirect("'Order Data'"&Vlookup('Order
Data'!C7,{5,17,27},{"K","L","M"},2,0)&Vlookup('Order
Data'!C5,{"G","T"."GC"},{"38","11","29"},2,0)

The second one is easier to add more options.

 

[Guest]

bj

Looks a good formula but does not work !! Comes up with #Ref error. Is it me
or should i be adding something to the formula to make it work??

chuckee

 

[Guest]

Martin,

Thanks, holding back on using this option at the moment but thanks for the mo

chuckee

 

[Guest]

Gary, Thanks for replying. Holding back on this option for the moment. May
use later

Thanks
chuckee

 

[Guest]

try instead for the second equation


=indirect("'Order Data'!"&lookup('Order
Data'!C7,{5,17,27},{"K","L","M"})&lookup('Order
Data'!C5,{"G","T"."GC"},{"38","11","29"})

I had left out a "!" and I did something wrong for the vlookup, but haven't
figured it out yet

 

[Guest]

bj thanks
There was a further error, a . before "GC" but changed for a , and a bracket
at the end of equation. Otherwise it works thanks

chuckee

 

[Guest]

bj ...... a problem !!

When the value in C7 is changed from 5 to 17 or 27 the result = 0
I can only assume that the formula is not identifying the change in C7

chuckee