Compare two cells and return certain value in third cell

H

hurairah

Dear Collagues,

Really needs your helps.

My situation is I have three cell. The first cell is Product and the
second cell is Hardness. My problem is I don't know how to put
automatically value in third cell based on the comparison of first and
second cell. As example, if the Product is MD700 and Hardness is 30,
then the value in the third cell is 3.7. If Product is MD700 and
Hardness is 40 then the value in the third cell is 3.9. Could anyone
please help me? Please take 13 products and 3 hardness as your
reference.

Really need your help and with my kindest regards,
Hurairah
 
S

Special-K

Need to know what the range of data is, ie will the result only ever be
3.7 and 3.9? Will the product only ever be MD700 (in which case its
irrelevant)? Or if the product is something else what will the result
be in that case? With the limited examples youve given this should
work

=IF(AND(A1="MD700",B1=30),3.7,IF(AND(A1="MD700",B1=40),3.9,"UNKNOWN))

Regards
Special-K
 
T

Toppers

Create table of product vs hardness as illustrated below (shown with purely
random data!); top left of table is cell A1

In formula below, F3 contains product and G3 hardness. Insert this formula
into your third cell (change ranges/cell references to suit)

=INDEX($B$2:$D$14,MATCH(F3,$A$2:$A$14,0),MATCH(G3,$B$1:$D$1,0))

F3=MD703, G3=40 then result is 0.18

To allow for error conditions i.e. invalid product and/or hardness use:

=IF(ISNA(INDEX($B$2:$D$14,MATCH(F3,$A$2:$A$14,0),MATCH(G3,$B$1:$D$1,0))),"",INDEX($B$2:$D$14,MATCH(F3,$A$2:$A$14,0),MATCH(G3,$B$1:$D$1,0)))

30 40 50
MD700 3.76 2.10 3.16
MD701 1.44 2.24 3.97
MD702 0.90 0.94 1.58
MD703 1.79 0.18 3.52
MD704 2.11 2.36 2.07
MD705 0.25 3.41 3.72
MD706 1.20 3.88 3.34
MD707 1.68 3.88 2.61
MD708 2.15 2.12 3.67
MD709 3.16 0.81 0.89
MD710 0.25 0.25 1.13
MD711 0.42 2.92 0.82
MD712 2.16 2.15 3.49

HTH
 
H

hurairah

Toppers said:
Create table of product vs hardness as illustrated below (shown with
purely
random data!); top left of table is cell A1

In formula below, F3 contains product and G3 hardness. Insert this
formula
into your third cell (change ranges/cell references to suit)

=INDEX($B$2:$D$14,MATCH(F3,$A$2:$A$14,0),MATCH(G3,$B$1:$D$1,0))

F3=MD703, G3=40 then result is 0.18

To allow for error conditions i.e. invalid product and/or hardness
use:

=IF(ISNA(INDEX($B$2:$D$14,MATCH(F3,$A$2:$A$14,0),MATCH(G3,$B$1:$D$1,0))),"",INDEX($B$2:$D$14,MATCH(F3,$A$2:$A$14,0),MATCH(G3,$B$1:$D$1,0)))

30 40 50
MD700 3.76 2.10 3.16
MD701 1.44 2.24 3.97
MD702 0.90 0.94 1.58
MD703 1.79 0.18 3.52
MD704 2.11 2.36 2.07
MD705 0.25 3.41 3.72
MD706 1.20 3.88 3.34
MD707 1.68 3.88 2.61
MD708 2.15 2.12 3.67
MD709 3.16 0.81 0.89
MD710 0.25 0.25 1.13
MD711 0.42 2.92 0.82
MD712 2.16 2.15 3.49

HTH

:
-

Dear Collagues,

Really needs your helps.

My situation is I have three cell. The first cell is Product and the
second cell is Hardness. My problem is I don't know how to put
automatically value in third cell based on the comparison of first
and
second cell. As example, if the Product is MD700 and Hardness is 30,
then the value in the third cell is 3.7. If Product is MD700 and
Hardness is 40 then the value in the third cell is 3.9. Could anyone
please help me? Please take 13 products and 3 hardness as your
reference.

Really need your help and with my kindest regards,
Hurairah
Click to expand...

Toppers,

Many thanks for release my headache. It works!
 
Top