Match 2 columns then return valu in the 3rd column

[StashSoup]

I have 3 columns of data in Sheet 1. Column A is SKU's, column B is WH's, and column C is a number. Column A has duplicate values, because a SKU exists in multiple WH's.

In Sheet 2 - Column A is the SKU and row 1 is the WH. I need the table poplated with the number from sheet 1.

I know there is aformula for this but i can't seem to get any of them to work. Not INDEX or MATCH or VLOOKUP.

I don't know how I can show a screen cap of the tables??!! So i hope i explained them perfectly.

 

[T. Valko]

I have 3 columns of data in Sheet 1. Column A is SKU's, column B is WH's, and column C is a number. Column A has duplicate values, because a SKU exists in multiple WH's.

In Sheet 2 - Column A is the SKU and row 1 is the WH. I need the table poplated with the number from sheet 1.

I know there is aformula for this but i can't seem to get any of them to work. Not INDEX or MATCH or VLOOKUP.

I don't know how I can show a screen cap of the tables??!! So i hope i explained them perfectly.

Will the combination of SKU + WH be unique?

What version of Excel are you using?

 

[texasalynn]

try this

Excel Workbook
	A	B	C
1	SKU	WH	Number
2	124K	5bc	5
3	125L	4ad	10
4	124K	5df	6
5
Sheet1

Excel 2003

Excel Workbook
	A	B	C
1	SKU	WH	Number
2	124K	5df	6
Sheet2

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[StashSoup]

#name? - yes the combination of SKU and WH will be unique. i am using office 2010

texaslynn - your suggestion does not work as you have the wrong setup for sheet 2. how did you show the row/column in your post?

 

[texasalynn]

take a look at this
http://www.mrexcel.com/forum/showthread.php?t=89356

 

[StashSoup]

sheet 2:
SKU WH1 WH2 WH3 WH4 WH5
SKU1
SKU1
SKU1
SKU2
SKU2
SKU2

 

[texasalynn]

so why would you have the SKx repeated on sheet2? The information would just duplicate those lines

 

[texasalynn]

Excel Workbook
	A	B	C
1	SKU	WH1	WH2
2	SKU1	5	10
3	SKU1	5	10
4	SKU1	5	10
Sheet2

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[T. Valko]

#name? - yes the combination of SKU and WH will be unique. i am using office 2010

Ok, try this...

With your data on Sheet1 in the range A2:C15.

Enter this formula on Sheet2 in cell B2:

=SUMIFS(Sheet1!$C$2:$C$15,Sheet1!$A$2:$A$15,$A2,Sheet1!$B$2:$B$15,B$1)

Copy across as needed then down as needed.

 

[StashSoup]

Works great for the first cell. but when i copy accross it changes the 1st criteria to the cell to the left....

thanks Biff!!!!