Match 2 columns then return valu in the 3rd column

StashSoup

New Member
Joined
Jul 1, 2010
Messages
16
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this
Excel Workbook
ABC
1SKUWHNumber
2124K5bc5
3125L4ad10
4124K5df6
5
Sheet1
Excel 2003
Excel Workbook
ABC
1SKUWHNumber
2124K5df6
Sheet2
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

StashSoup

New Member
Joined
Jul 1, 2010
Messages
16
#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

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

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

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Excel Workbook
ABC
1SKUWH1WH2
2SKU1510
3SKU1510
4SKU1510
Sheet2
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
#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

New Member
Joined
Jul 1, 2010
Messages
16
Works great for the first cell. but when i copy accross it changes the 1st criteria to the cell to the left....

thanks Biff!!!!
 

Forum statistics

Threads
1,141,847
Messages
5,708,946
Members
421,601
Latest member
Garlo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top