Using the SEARCH function

  • Thread starter Thread starter Dan Wilson
  • Start date Start date
D

Dan Wilson

Good day. I am using Excel 2002.

Background:
A1 B1 C1 D1 E1
5 gal ? 640 oz

In the above reference, A1 is the quantity of the product
to be defined in B1. D1 will then show the quantity of
product as defined in E1. C1 will become the conversion
factor.

For instance, 5 gallons of product will convert to 640
ounces. If B1 contained "qt", then 5 quarts of product
would convert to 160 ounces.

I have been trying to use the SEARCH function using the
following formula;

=IF(C1="","",IF(SEARCH("gal",C1),(A1*128),A1))

The formula works IF C1 contains "gal", but other text
causes a VALUE fault. The formula shown is a simple
version of what must be created. I also have to be able
to search for and convert for "lbs", "pt", "qt". As a
sidebar to this problem, I have been able to make the
formula work with up to two SEARCH functions nested in the
same formula, but as soon as I try to insert a third
SEARCH function, it gives me an error about too many
arguments.

Any help? More details are available if needed.
Responses to my email address will be appreciated.

Thanks, Danno...
 
If you have the Analysis Toolpak Add-in installed, you could leave out
C1 and put this in D1 (works for "gal", "qt", "oz", "pt", "l", "ml",
"dl", etc.):

=CONVERT(A1,B1,E1)
 
One way:

Make a table with the conversion factors (say on sheet2, naming it
ConvTable):


A B C D E
1 gal qt oz ...
2 gal 1 4 128 ...
3 qt =1/C1 1 32 ...
4 oz =1/D1 =1/D2 1 ...
5 ...
etc.

then the conversion factor in C1 is

=VLOOKUP(B1,ConvTable,MATCH(E1,OFFSET(ConvTable,,,1,),FALSE),FALSE)
 
Good day JE,

Thanks for the quick response. Your suggestion below will
work until I can experiment with the Analysis Toolpak.

Thanks, Danno...
 
Good day Peter,

Thanks for the response. I will try your suggestion.

Thanks, Danno...
 
Back
Top