Readers like you help support MUO. When you make a purchase using links on our site, we may earn an affiliate commission. Read More.
What formula would I use 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 sum to be placed in cell S63
Example: IF A28=LIN, THEN CELL D63 NEEDS TO BE MULTIPLIED BY 13.80 AND THAT SUM 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
Thanks in advance for the help!
2011-09-16 07:46:00
You can use vlookup. In a hidden sheet write down the codes that would go in A28 and the corresponding multiplication factors. Then just use vlookup to get the values. Something like S63 = D63*VLOOKUP(A28,sheet2!$A$1:$B$7,2)hope this helps
2011-09-16 13:04:00
The only thing I'd add to Sks3286's answer is that when you use a vlookup, the value that is entered in cell A28 has to be exact to the values listed on the hidden lookup table. If the values aren't exact, then the lookup won't work. One way to guarantee that the vlookup work is to make cell A28 a data validation cell, where the values can a) either be chosen from a drop-down list or b) give an error that will let you know that the value that was entered does not match any of the items on the table.I'd me more than happy to clarify...you can always direct message me on twitter @nmaldonado:twitter
2011-09-16 06:24:00
Lee, In cell S63, the following formula should work to give you the right answer based on cell A28: =IF(A28="LIN", D63*13.8, IF(A28="LOX", D63*12.08, IF(A28="LAR", D63*9.671, IF(A28="CO2", D63*1, IF(A28="GH2", D63*1, IF(A28="GN2", D63*1, IF(A28="GO2", D63*1))))))) There's probably an easier way to do this, and this does not account for if none of those values is entered in A28, so....