What is the formula to lookup quantity of an item code by different location sorting?

[iMailMan]

Here is the table of the data for your reference:

Location	Item Code	Qty
A	123	1000
B	456	700
A	789	2000
B	123	500
A	456	1200
B	789	3500

<tbody>
</tbody>

Here is the table for data entry:

	Qty	Qty
Item Code	Location A	Location B
123
456
789

<tbody>
</tbody>

I have try many formula like Vlookup+Hlookup or Index+Match or Vlookup+Match still not successful to get the data entry on the above table mentioned.
Please help and educate me.


Thanks.

 

[asad]

Try Sumproduct

 

[iMailMan]

Can i say Sumproduct is a function to sum up the quantity? If yes, this is not i want.
I actually want to fill the quantity of an item code by location.

 

[asad]

you are right, but in your case, it will give the quantity of item by location.

 

[pvr928]

Hi iMailMan

Firstly, suggest you don't start another thread - combine any additional information into the original thread.

If your data body range is in range A2:C7, and your results table is in F3, insert the following formula into F3 and copy it across the range F3:G5:

=SUMPRODUCT(--($A$2:$A$7=RIGHT(F$2,1)),--($B$2:$B$7=$E3),$C$2:$C$7)

Cheers

pvr928

 

[asad]

Have a look:

A	123	1000
B	456	700
A	789	2000
B	123	500
A	456	1200
B	789	3500
Qty	Qty
Item Code	Location A	Location B
123	1000	500
456	1200	700
789	2000	3500

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

The formula I used was

=SUMPRODUCT(--($A$1:$A$6=RIGHT(B$11,1)),--($B$1:$B$6=$A12),$C$1:$C$6)

 

[asad]

pvr928 beat me to it

 

[Peter_SSs]

Welcome to the MrExcel board!

If the combinations of location & Code in the lookup table are unique (which I expect they are) and you have a reasonably recent version of Excel & have the SUMIFS function then you could also use this.
(I have removed the "Location" text from row 11 to make the formulas simpler though it could be done without this removal if needed.)

Formula in B12 is copied across and down.

Excel Workbook
	A	B	C
1	Location	Item Code	Qty
2	A	123	1000
3	B	456	700
4	A	789	2000
5	B	123	500
6	A	456	1200
7	B	789	3500
8
9
10		Qty	Qty
11	Item Code	A	B
12	123	1000	500
13	456	1200	700
14	789	2000	3500
Qty

 

[pvr928]

... nice. But for the cell references we're the exactly the same

 

[iMailMan]

Thanks for your guide.
I got it and it really works well for my table.

Again, appreciated every above master for your kind assistance.