How do I populate a cell using another worksheet as a reference?

For example, if Sheet1 looks like this:

A B C
1 Part Color Price
2 Desk Black 200.00
3 Chair Black 100.00
4 Lamp Gold 50.00

Sheet2 looks like this:

A B C
1 Black Gold
2 Chair 100.00 150.00
3 Lamp 35.00 50.00
4 Desk 200.00 300.00

I want column C in Sheet1 to pull the price depending on the two crietria.

Any ideas?

Thanks.

Kevin

Enter the following formula on Sheet1 in cell C2 and then copy & paste it to
C2:C4...

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

"Kevin" wrote:

How do I populate a cell using another worksheet as a reference?

For example, if Sheet1 looks like this:

A B C
1 Part Color Price
2 Desk Black 200.00
3 Chair Black 100.00
4 Lamp Gold 50.00

Sheet2 looks like this:

A B C
1 Black Gold
2 Chair 100.00 150.00
3 Lamp 35.00 50.00
4 Desk 200.00 300.00

I want column C in Sheet1 to pull the price depending on the two crietria.

Any ideas?

Thanks.

Kevin

In B2 =SUMPRODUCT(--($A$1:$A$100=$A2),--(B1:100=B$1),$C$1:$C$100)

A2 = chair
B1 = Black



"Kevin" wrote:

How do I populate a cell using another worksheet as a reference?

For example, if Sheet1 looks like this:

A B C
1 Part Color Price
2 Desk Black 200.00
3 Chair Black 100.00
4 Lamp Gold 50.00

Sheet2 looks like this:

A B C
1 Black Gold
2 Chair 100.00 150.00
3 Lamp 35.00 50.00
4 Desk 200.00 300.00

I want column C in Sheet1 to pull the price depending on the two crietria.

Any ideas?

Thanks.

Kevin

Thanks so much MrAcquire! :)

"MrAcquire" wrote:

Enter the following formula on Sheet1 in cell C2 and then copy & paste it to
C2:C4...

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

"Kevin" wrote:

How do I populate a cell using another worksheet as a reference?

For example, if Sheet1 looks like this:

A B C
1 Part Color Price
2 Desk Black 200.00
3 Chair Black 100.00
4 Lamp Gold 50.00

Sheet2 looks like this:

A B C
1 Black Gold
2 Chair 100.00 150.00
3 Lamp 35.00 50.00
4 Desk 200.00 300.00

I want column C in Sheet1 to pull the price depending on the two crietria.

Any ideas?

Thanks.

Kevin

Oh, and what if the scenario involved pricing for different territories?
Let's say there are ten territories and each territory has its own worksheet.
For example, "Territory1," "Territory2," "Territory3," etc. The "part" and
"color" would be the same for each territory, but the "price" would vary.
How would the function change to accomodate this? Thanks.

"Kevin" wrote:

How do I populate a cell using another worksheet as a reference?

For example, if Sheet1 looks like this:

A B C
1 Part Color Price
2 Desk Black 200.00
3 Chair Black 100.00
4 Lamp Gold 50.00

Sheet2 looks like this:

A B C
1 Black Gold
2 Chair 100.00 150.00
3 Lamp 35.00 50.00
4 Desk 200.00 300.00

I want column C in Sheet1 to pull the price depending on the two crietria.

Any ideas?

Thanks.

Kevin