IF formula for 13 plus variables
 

I am building a price sheet that I need to come up with a formula that can
figure a material coat if the part is one of 13 different types of material.
I want to be able to update my material pricing on sheet 2 andd have the
sheet recalculate it automaticly for each part.
I have "A" column as Material type(abreviation like SS for stainless steel),
"B" column as weight of the part, and Sheet 2 column "A" as the price for the
material. My formula (column C")looks like : =IF(A1="SS",B1*sheet2!A1,.....)
and so forth for each of the 13 types of material. I can get it to work for
the first eight types of material but anything past that it gives me an
error. I also would like to display "error" in the cell if someone does not
enter the material abbreviation correctly .
Any help would be greatly appreciated. Thanks.

IF formula for 13 plus variables
 

Here is what I would suggest:

On Sheet 2, list all of your materials in Column A and corresponding prices
in column B.

Now, for your formula in Column C of Sheet 1:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),"Error",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1)

I just assumed 100 rows in this example, you may have more or less. Adjust
accordingly.

HTH,
Elkar

"N Dependable" wrote:

I am building a price sheet that I need to come up with a formula that can
figure a material coat if the part is one of 13 different types of material.
I want to be able to update my material pricing on sheet 2 andd have the
sheet recalculate it automaticly for each part.
I have "A" column as Material type(abreviation like SS for stainless steel),
"B" column as weight of the part, and Sheet 2 column "A" as the price for the
material. My formula (column C")looks like : =IF(A1="SS",B1*sheet2!A1,.....)
and so forth for each of the 13 types of material. I can get it to work for
the first eight types of material but anything past that it gives me an
error. I also would like to display "error" in the cell if someone does not
enter the material abbreviation correctly .
Any help would be greatly appreciated. Thanks.

IF formula for 13 plus variables
 

Hi!

Here's another way to write that formula:

=IF(COUNTIF(Sheet2!A$1:A$100,A1),VLOOKUP(A1,Sheet2 !A$1:B$100,2,0)*B1),"Error")

Biff

"Elkar" wrote in message
...
Here is what I would suggest:

On Sheet 2, list all of your materials in Column A and corresponding
prices
in column B.

Now, for your formula in Column C of Sheet 1:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),"Error",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1)

I just assumed 100 rows in this example, you may have more or less.
Adjust
accordingly.

HTH,
Elkar

"N Dependable" wrote:

I am building a price sheet that I need to come up with a formula that
can
figure a material coat if the part is one of 13 different types of
material.
I want to be able to update my material pricing on sheet 2 andd have the
sheet recalculate it automaticly for each part.
I have "A" column as Material type(abreviation like SS for stainless
steel),
"B" column as weight of the part, and Sheet 2 column "A" as the price for
the
material. My formula (column C")looks like :
=IF(A1="SS",B1*sheet2!A1,.....)
and so forth for each of the 13 types of material. I can get it to work
for
the first eight types of material but anything past that it gives me an
error. I also would like to display "error" in the cell if someone does
not
enter the material abbreviation correctly .
Any help would be greatly appreciated. Thanks.

IF formula for 13 plus variables
 

Thank you Thank you Thank you!!!!!!!!
--
Nathan Hovious


"Elkar" wrote:

Here is what I would suggest:

On Sheet 2, list all of your materials in Column A and corresponding prices
in column B.

Now, for your formula in Column C of Sheet 1:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),"Error",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1)

I just assumed 100 rows in this example, you may have more or less. Adjust
accordingly.

HTH,
Elkar

"N Dependable" wrote:

I am building a price sheet that I need to come up with a formula that can
figure a material coat if the part is one of 13 different types of material.
I want to be able to update my material pricing on sheet 2 andd have the
sheet recalculate it automaticly for each part.
I have "A" column as Material type(abreviation like SS for stainless steel),
"B" column as weight of the part, and Sheet 2 column "A" as the price for the
material. My formula (column C")looks like : =IF(A1="SS",B1*sheet2!A1,.....)
and so forth for each of the 13 types of material. I can get it to work for
the first eight types of material but anything past that it gives me an
error. I also would like to display "error" in the cell if someone does not
enter the material abbreviation correctly .
Any help would be greatly appreciated. Thanks.