Can Excel find a value in a table based on 2 Criteria

[Paul at GTS]

Hi,
I have a product that is sold by its size. The width runs along the top of the table and the height down the side. This then forms a price matrix.

EG
100 200 300
100 1.00 1.50 2.00
200 1.75 2.50 2.75
300 4.00 4.75 5.25

So if i have a product that is 200x100 the price would be 1.50 or a 100x300 would be 4.00

My problem is that I need to get this into a few columns so I can import the individual products into our pricing system

So my prodcts are listed in size order in Cols A & B and I need to populate Col C with prices as below.

Col A Col B Col C
Width Height Price
100 100 1.00
100 200 1.50
100 300 2.00
200 100 1.75
200 200 2.50
200 300 2.75
300 100 4.00
300 200 4.75
300 300 5.25

Any ideas ?

Thanks

 

[steve the fish]

Ok so your table is pasted into A1 of Sheet1 and your results in A1 of Sheet2. Then try this in C2 of your results:

=INDEX(Sheet1!$A$1:$D$4,MATCH(B2,Sheet1!$A$1:$A$4,0),MATCH(A2,Sheet1!$A$1:$A$4,0))

 

[Paul at GTS]

Works a treat thanks