How to choose a multiplier based on certain cell input in Excel?

Question

I need some assistance with how to write an Excel formula for the below:

In cell A28 one of the following will be entered: LIN, LOX, LAR, CO2, GH2, GN2 or GO2

Depending on which is entered in cell A28 will have a conversion value that needs to be multiplied by the value in cell D63 and that product to be placed in cell S63

Example:

IF A28=LIN,
THEN CELL D63 NEEDS TO BE MULTIPLIED BY 13.80
AND THAT PRODUCT PLACED IN CELL S63

LIN = 13.80  
LOX = 12.08  
LAR = 9.671  
CO2 = 1.0  
GH2 =1.0  
GN2 = 1.0  
GO2 = 1.0  

Any help is greatly appreciated!

Answer 1

You need to have a separate table with each of the possible values (LIN, LOX, etc.) in one column and the associated conversion factors (13.80, 12.08, etc.) in the next column. So, if this table is in B1:C7, you would enter the following in S63:

=D63*VLOOKUP(A28,B1:C7,2,FALSE)

If you would rather hard-code the values into the formula rather than creating a separate lookup table, you can use the following less elegant formula in S63:

=IF(ISBLANK(A28),"",IF(A28="LIN",13.80,IF(A28="LOX",12.08,IF(A28="LAR",9.671,1.0))))

Answer 2

Try entering this formula in S63

=D63*SUM(
(A28="LIN")*13.8,
(A28="LOX")*12.08,
(A28="LAR")*9.671,
(A28="CO2")*1,
(A28="GH2")*1,
(A28="GN2")*1,
(A28="GO2")*1)

How it evaluates if A28 contains "LOX":

If you're feeling a little adventurous, you can try these formulas in S63:

=D63*CHOOSE(MATCH(A28,{"LIN";"LOX";"LAR";"CO2";"GH2";"GN2";"GO2"},0),13.8,12.08,9.671,1,1,1,1)

or

=D63*VLOOKUP(A28,{"LIN",13.8;"LOX",12.08;"LAR",9.671;"CO2",1;"GH2",1;"GN2",1;"GO2",1},2,0)