Multiple If statements ..... any alternative

[lynxbci]

Hi,
I have an if statement in a cell which returns a value based on another cell, and without using a lookup table is there a simpler way to get the same result?
Current formula is :-
<code>
=IF(G20="PET",1,IF(G20="PVC",7,IF(G20="SAP",12,IF(G20="TTT",99,0))))
</code>
thanks in advance

 

[T. Valko]

Hi,
I have an if statement in a cell which returns a value based on another cell, and without using a lookup table is there a simpler way to get the same result?
Current formula is :-
<CODE>
=IF(G20="PET",1,IF(G20="PVC",7,IF(G20="SAP",12,IF(G20="TTT",99,0))))
</CODE>
thanks in advance

"Is there a simpler way" is a relative concept!

IMHO, what you already have is pretty simple.

 

[Jonmo1]

Not without either:
1. Using a lookup table
2. Hard Coding the lookup into the formula

Hard coding it in the formula

=LOOKUP(G20,{"PET",1;"PVC",7;"SAP",12;"TTT",99})

Note, the Text strings must be in alphabetical order left to right.

 

[T. Valko]

Not without either:
1. Using a lookup table
2. Hard Coding the lookup into the formula

Hard coding it in the formula

=LOOKUP(G20,{"PET",1;"PVC",7;"SAP",12;"TTT",99})

Note, the Text strings must be in alphabetical order left to right.

That leaves out the value_if_false result of 0 if the none of the values are found.

 

[Ruddles]

Alternatives might be:-

• use =LOOKUP where the array is defined in the formula but this is not good if the formula exists in multiple cells and there's a possibility that the lookup values might change in the future
• use a UDF: this will make the worksheet formula much simpler but it would add the requirement for macros to be enabled and involve an additional skill (understanding VBA)

You pays your money and you takes your choice!

 

[lynxbci]

Thanks for all your inputs