Vlookup and IF or Lookup with same item codes different region

[maverickbenz]

I have Sheet 1 that has my Region and Item Code that I want to pull the dollar amount over from Sheet 2 that has my Region, Code and Dollar Amount.

I have different Regions with the same Item Codes and obviously different Dollar amounts by each region.

Sheet 1 looks like this:

	A	B	C
1	REGION	CODE	DOLLAR AMOUNT
2	North	1000
3	North	1200
4	West	1000
5	West	1200
6	West	1300
7	Central	800
8	Central	1000
9	East	1000
10	East	1300

<tbody>
</tbody>

Sheet 2 looks something like this:

	A	B	C
1	REGION	CODE	DOLLAR AMOUNT
2	North	1200	4500.29
3	East	1000	1675.54
4	West	1300	7542.35
5	Central	1000	4831.25
6	Central	800	12.54
7	East	1300	158.45
8	West	1000	16783.35
9	North	1000	457.55
10	West	1200	3482.37

<tbody>
</tbody>

I've tried with Lookup followed by Vlookup but it's giving me the wrong region values, so I think I'm missing something really simple here...

My challenge is that I have the same code in multiple regions.

 

[svendiamond]

=INDEX(Sheet2!C:C,MATCH(B2,IF(Sheet2!A:A=A2,Sheet2!B:B),0))

CTRL+SHIFT+ENTER

 

[maverickbenz]

works great, thank you.

 

[maverickbenz]

I find it really lags my computer.
Any other formula that could work for something like this?

 

[svendiamond]

I didn't know how far down your data goes so I just referred to the entire column (to make it easier to read/understand)... which is looking through 1,048,576 cells. Just adjust it for however large your data is. If it were only ten rows you could use:

=INDEX(Sheet2!$C$1:$C$10,MATCH(B2,IF(Sheet2!$A$1:$A$10=A2,Sheet2!$B$1:$B$10),0))

 

[svendiamond]

Also there is another pretty cool way to do this with a non-array formula:

=INDEX(Sheet2!C:C,MATCH(1,INDEX((Sheet2!B:B=B2)*(Sheet2!A:A=A2),0,1),0))

Again you'll want to refer to your range instead of the entire columns... I only do this to make it easier to read and understand the formula.