An Alternative Approach

[dwgnome]

Looking to see if there is a more efficient formula for the following:

=IF(F22="pint",CONVERT(E22,"pt","gal"),IF(F22="quart",CONVERT(E22,"qt","gal"),IF(F22="liter",CONVERT(E22,"l","gal"),IF(F22="fluid",CONVERT(E22,"oz","gal"),IF(F22="cc",CONVERT(E22,"ml","gal"),CONVERT(E22,F22,"gal"))))))

Looking to expand to include additional conversions so naturally I may run into nested IF maximum.

Excel 2010.

 

[Jonmo1]

You could make a lookup table with the full words in one column, and the conversion versions in another.

Try something like this

Unknown
	E	F	G	H	I	J	K
22	5	quart	1.25			pint	pt
23						quart	qt
24						liter	l
25						fluid	oz
26						cc	ml
Sheet1

Cell Formulas
Range	Formula
G22	=CONVERT(E22,IFERROR(VLOOKUP(F22,$J$22:$K$26,2,0),F22),"gal")

 

[Rick Rothstein]

If you did not want to maintain a table on a worksheet, you could embed the table inside your formula...

=CONVERT(E22,IFERROR(INDEX({"pt","qt","l","oz","ml"},MATCH(F22,{"pint","quart","liter","fluid","cc"},0)),F22),"gal")

 

[dwgnome]

Thank you both for your responses. They both worked perfectly - much cleaner and more versatile.

 

[Jonmo1]

You're welcome.