Vlookup Formula needed

[Johnboy28]

Hi,

Any help will be great - looking for a Vlookup that can match "Warehouse Locations" to location, and then match Code and return Count?

Example - Code 74208 should equal 6

Location	Code	Count		Warehouse Locations		Code	Count
CSB1	1	18		CSB1		74209	??
CSC1	2	87		CSC1		74208	??
CSB1	4	10
CSB1	5	17
CSB1	6	13
CSC1	74208	6
CSB1	16	10
CSB1	19	6

 

[arthurbr]

Hi Perhaps try the COUNTIFS function?

 

[Johnboy28]

Hi Perhaps try the COUNTIFS function?

Thanks for the reply. Can not use Countifs, I need the cell Value.

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H
2	Location	Code	Count		Warehouse Locations		Code	Count
3	CSB1	1	18		CSB1		5	17
4	CSC1	2	87		CSC1		74208	6
5	CSB1	4	10
6	CSB1	5	17
7	CSB1	6	13
8	CSC1	74208	6
9	CSB1	16	10
10	CSB1	19	6
Sheet2

Cell Formulas
Range	Formula
H3:H4	H3	=SUMPRODUCT(--($A$3:$A$10=E3),--($B$3:$B$10=G3),$C$3:$C$10)

 

[Peter_SSs]

Is this what you are after?

24 01 23.xlsm
	A	B	C	D	E	F	G	H
1	Location	Code	Count		Warehouse Locations		Code	Count
2	CSB1	1	18		CSB1		74209	Not Found
3	CSC1	2	87		CSC1		74208	6
4	CSB1	4	10
5	CSB1	5	17
6	CSB1	6	13
7	CSC1	74208	6
8	CSB1	16	10
9	CSB1	19	6
Lookup

Cell Formulas
Range	Formula
H2:H3	H2	=XLOOKUP(E2:E3&"|"&G2:G3,A2:A9&"|"&B2:B9,C2:C9,"Not Found")
Dynamic array formulas.

 

[Johnboy28]

try this

Book1
	A	B	C	D	E	F	G	H
2	Location	Code	Count		Warehouse Locations		Code	Count
3	CSB1	1	18		CSB1		5	17
4	CSC1	2	87		CSC1		74208	6
5	CSB1	4	10
6	CSB1	5	17
7	CSB1	6	13
8	CSC1	74208	6
9	CSB1	16	10
10	CSB1	19	6
Sheet2

Cell Formulas
Range	Formula
H3:H4	H3	=SUMPRODUCT(--($A$3:$A$10=E3),--($B$3:$B$10=G3),$C$3:$C$10)

Thank you for the reply. The sumproduct works good but I need the "all location" to be combined in the formula not calculated by single cell?

Location	Code	Count		All Locations		Code	Count
CSB1	1	18		CSC1		74209	0
CSB1	2	87		CSC4		74208	6
CSB1	4	10
CSB1	5	17
CSB1	6	13
CSC4	74208	6
CSB1	16	10
CSB1	19	6

 

[Peter_SSs]

but I need the "all location" to be combined in the formula

Not quite sure what you meant by that? Can you clarify that?

.. and did you try the post #5 formula?

 

[Johnboy28]

Thank you for the reply. The sumproduct works good but I need the "all location" to be combined in the formula not calculated by single cell?

Location	Code	Count		All Locations		Code	Count
CSB1	1	18		CSC1		74209	0
CSB1	2	87		CSC4		74208	6
CSB1	4	10
CSB1	5	17
CSB1	6	13
CSC4	74208	6
CSB1	16	10
CSB1	19	6

Table 1
Location	Code	Count
CSB1	1	18
CSB1	2	87
CSB1	74208	10
CSB1	5	17
CSB1	6	13
CSC4	74208	6
CSB1	16	10
CSB1	19	6

Table 2

All Locations

CSC1

CSC4

Table 3
Code	Count
74209	0
74208	6

Ultimately we need to get the sum count for the codes in table 3. Lookup the code from Table 3 in Table 1 but only include Locations in table 1 that match table 2. So using the example above, we should only have a count of 6 for code 74208.

I tried #5 formula? did work on my sheet.

 

[Peter_SSs]

I tried #5 formula? did work on my sheet.

From what I am understanding of tour added description/samples I think you may have meant "did not work on my sheet"?

See if this does what you want then.
If not, as well as giving any further explanation, please give a slightly larger sample including examples (if it is possible with your data) where a Code in column F in my layout exists more than once in column B but with more than one Location in column A.

24 01 23.xlsm
	A	B	C	D	E	F	G
1	Location	Code	Count		Warehouse Locations	Code	Count
2	CSB1	1	18		CSB1	74209	0
3	CSC1	2	87		CSC1	74208	6
4	CSB1	4	10
5	CSB1	5	17
6	CSB1	6	13
7	CSC1	74208	6
8	CSB1	16	10
9	CSB1	19	6
Lookup (2)

Cell Formulas
Range	Formula
G2:G3	G2	=BYROW(F2:F3,LAMBDA(r,SUM(FILTER(C2:C9,ISNUMBER(MATCH(A2:A9,E2:E3,0))*ISNUMBER(MATCH(B2:B9,r,0)),0))))
Dynamic array formulas.

 

[Johnboy28]

=BYROW(F2:F3,LAMBDA(r,SUM(FILTER(C2:C9,ISNUMBER(MATCH(A2:A9,E2:E3,0))*ISNUMBER(MATCH(B2:B9,r,0)),0))))

Thank you very much. That work perfect.