I already figured out a problem with the folliowing function but now I have
another question:
A B C D
Code Item Size Price
SW001 Sweater Small 10
JK001 Jacket Small 30
PN001 Pants Small 25
SW001 Sweater Med 12
JK001 Jacket Med 35
PN001 Pants Med 30

Item Size Price
pants small ?

I got the formula to get the price as:
{INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))}

But now, what if I have the following and everytime I have different items
or sizes I need to find different prices too?
Code Item Size Price1 Price2
SW001 Sweater Small 10 5
JK001 Jacket Small 30 10
PN001 Pants Small 25 20
SW001 Sweater Med 12 30
JK001 Jacket Med 35 3
PN001 Pants Med 30 2

Item Size Price1 Price2
pants small ? ?

How would I get Price1 and Price2 with a formula without entering everytime?
Thanks!

Try this...

Array entered** in C10 and copied across to D10:

=INDEX(D$2:D$7,MATCH(1,($A10=$B$2:$B$7)*($B10=$C$2 :$C$7),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"cecilluen" wrote in message
...
I already figured out a problem with the folliowing function but now I have
another question:
A B C D
Code Item Size Price
SW001 Sweater Small 10
JK001 Jacket Small 30
PN001 Pants Small 25
SW001 Sweater Med 12
JK001 Jacket Med 35
PN001 Pants Med 30

Item Size Price
pants small ?

I got the formula to get the price as:
{INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))}

But now, what if I have the following and everytime I have different items
or sizes I need to find different prices too?
Code Item Size Price1 Price2
SW001 Sweater Small 10 5
JK001 Jacket Small 30 10
PN001 Pants Small 25 20
SW001 Sweater Med 12 30
JK001 Jacket Med 35 3
PN001 Pants Med 30 2

Item Size Price1 Price2
pants small ? ?

How would I get Price1 and Price2 with a formula without entering
everytime?
Thanks!

Hi,
for the 1st price you got already the formula, for the 2nd

{INDEX($e$2:$e$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))}

"cecilluen" wrote:

I already figured out a problem with the folliowing function but now I have
another question:
A B C D
Code Item Size Price
SW001 Sweater Small 10
JK001 Jacket Small 30
PN001 Pants Small 25
SW001 Sweater Med 12
JK001 Jacket Med 35
PN001 Pants Med 30

Item Size Price
pants small ?

I got the formula to get the price as:
{INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))}

But now, what if I have the following and everytime I have different items
or sizes I need to find different prices too?
Code Item Size Price1 Price2
SW001 Sweater Small 10 5
JK001 Jacket Small 30 10
PN001 Pants Small 25 20
SW001 Sweater Med 12 30
JK001 Jacket Med 35 3
PN001 Pants Med 30 2

Item Size Price1 Price2
pants small ? ?

How would I get Price1 and Price2 with a formula without entering everytime?
Thanks!