Formula for pulling the counts on items in i different locations but with same name

[svis89]

=XLOOKUP([@Item]&[@[Bin Number]],Item19&Bin_Number19,"@[Count19]","",1)
=XLOOKUP("[@Item]='R19'!@Item19*'Calculate sheet'!@[Bin Number]=@'R19'!A3:A68",Item19,Count19,"",1) this only pulled the first instance of the same name

I tried the above and a few other variations but could not get the result i want... i have a work book with 33 work sheets for a cycle count running through-out the year i am trying to create a catch all formula that will look up the item name and the locations and if they match my new sheet return the correct count right now im trying using define names for the data areas 2-33 the above shows for sheet 19 its saying look at the item and bin number match the result on page nineteen and if it does give me the count that was added for this cycle count.... it does not have to be xlookup that is just what im used to using since i just am not allowed to use macro's anymore lol
I will keep trying different things on my end i have always got good help here

 

[awoohaw]

Can you provide sample data from two of the worksheets (with row headers)? It looks like you have range names in your formulas that we also need to understand.
It may even be easier for all if you put a sanitized version on a share file site, like dropbox.

 

[svis89]

=XLOOKUP([@Item]&[@[Bin Number]],Item19&Bin_Number19,"@[Count19]","",1)
=XLOOKUP("[@Item]='R19'!@Item19*'Calculate sheet'!@[Bin Number]=@'R19'!A3:A68",Item19,Count19,"",1) this only pulled the first instance of the same name

I tried the above and a few other variations but could not get the result i want... i have a work book with 33 work sheets for a cycle count running through-out the year i am trying to create a catch all formula that will look up the item name and the locations and if they match my new sheet return the correct count right now im trying using define names for the data areas 2-33 the above shows for sheet 19 its saying look at the item and bin number match the result on page nineteen and if it does give me the count that was added for this cycle count.... it does not have to be xlookup that is just what im used to using since i just am not allowed to use macro's anymore lol
I will keep trying different things on my end i have always got good help here

Inventory Cycle count record with date.xlsx
	F
788	#VALUE!
Calculate sheet

Cell Formulas
Range	Formula
F788	F788	=XLOOKUP([@Item]&[@[Bin Number]],Item19&Bin_Number19,"@[Count19]","",1)

Named Ranges
Name	Refers To	Cells
Bin_Number19	='R19'!$A$3:$A$68	F788
Item19	='R19'!$B$3:$B$68	F788

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:F3188	Expression	="$E3:$E58<>$B3:$B58"	text	NO

 

[svis89]

Inventory Cycle count record with date.xlsx
	F
788	#VALUE!
Calculate sheet

Cell Formulas
Range	Formula
F788	F788	=XLOOKUP([@Item]&[@[Bin Number]],Item19&Bin_Number19,"@[Count19]","",1)

Named Ranges
Name	Refers To	Cells
Bin_Number19	='R19'!$A$3:$A$68	F788
Item19	='R19'!$B$3:$B$68	F788

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:F3188	Expression	="$E3:$E58<>$B3:$B58"	text	NO

Inventory Cycle count record with date.xlsx
	A	B	C	D		F
2	Bin Number	Item	Serial Number	On Hand		Count
3	R23-005-A-1	1336-B010-E0D-RC		1		0
13	R23-002-B-2	20BD077AAB-C2100NNAB-C0-RC		3		0
14	R23-002-B-2	20BD125A0-RC		1		0
15	R23-003-B-2	20F11ND027A-RC		10		0
16	R23-003-B-1	20F11ND034A-RC		3		0
17	R23-003-B-1	20F11ND052AA-FN		1		0
18	R23-002-B-1	20F11ND065A-RC		1		0
19	R23-003-B-1	20F11ND5P0AA-RC		2		0
20	R23-003-B-2	20G11NC030JA-RC		1		0
21	R23-003-D-2	20G11ND011AA-FN		2		0
22	R23-003-C-1	20G11ND011AA-RC		2		0
23	R23-003-C-1	20G11ND014-FN		4		0
24	R23-003-C-2	20G11ND022-FN		1		0
25	R23-003-C-2	20G11ND022-RC		6		0
26	R23-003-C-1	20G11ND034AA0N-RC		1		0
27	R23-003-C-2	20G11ND065-RC		1		0
28	R23-003-C-1	20G11ND2P1AA-RC		2		0
29	R23-003-C-1	20G11ND5P0AA-RC		4		0
30	R23-003-D-2	20G11ND8P0JA0NNNNN-FN		1		0
31	R23-001-C-2	25B-D017N104-FN		2		0
32	R23-001-C-2	25B-D024N104-FN		1		0
33	R23-001-C-2	25B-D030N104-FN		1		0
34	R23-001-C-2	25B-D043N114-FN		1		0
35	R23-001-C-2	25B-D2P3N104-FN		1		0
43	R23-005-D-2	AF-600FP-RC		1		0
44	R23-001-B-1	ATS22C32S6U-RC		1		0
45	R23-001-B-1	ATV61HD75N4-RC		1		0
46	R23-001-B-1	ATV61HU40N4-FN		1		0
47	R23-001-B-1	ATV61HU55N4-RC		2		0
Calculate sheet

Cell Formulas
Range	Formula
D3,D13:D35,D43:D47	D3	=XLOOKUP(B:B,'Pulled data'!A:A,'Pulled data'!P:P,"")
F3,F13:F35,F43:F47	F3	=XLOOKUP([@Item],Item23,Count23,"")

Named Ranges
Name	Refers To	Cells
Count23	='R23'!$F$3:$F$50	F43:F47, F13:F35, F3
Item23	='R23'!$B$3:$B$50	F43:F47, F13:F35, F3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:F3188	Expression	="$E3:$E58<>$B3:$B58"	text	NO

 

[svis89]

Inventory Cycle count record with date.xlsx
	F
788	#VALUE!
Calculate sheet

Cell Formulas
Range	Formula
F788	F788	=XLOOKUP([@Item]&[@[Bin Number]],Item19&Bin_Number19,"@[Count19]","",1)

Named Ranges
Name	Refers To	Cells
Bin_Number19	='R19'!$A$3:$A$68	F788
Item19	='R19'!$B$3:$B$68	F788

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:F3188	Expression	="$E3:$E58<>$B3:$B58"	text	NO

Inventory Cycle count record with date.xlsx
	A	B	C	D		F
2	Bin Number	Item	Serial Number	On Hand		Count
3	R23-005-A-1	1336-B010-E0D-RC		1		0
13	R23-002-B-2	20BD077AAB-C2100NNAB-C0-RC		3		0
14	R23-002-B-2	20BD125A0-RC		1		0
15	R23-003-B-2	20F11ND027A-RC		10		0
16	R23-003-B-1	20F11ND034A-RC		3		0
17	R23-003-B-1	20F11ND052AA-FN		1		0
18	R23-002-B-1	20F11ND065A-RC		1		0
19	R23-003-B-1	20F11ND5P0AA-RC		2		0
20	R23-003-B-2	20G11NC030JA-RC		1		0
21	R23-003-D-2	20G11ND011AA-FN		2		0
22	R23-003-C-1	20G11ND011AA-RC		2		0
23	R23-003-C-1	20G11ND014-FN		4		0
24	R23-003-C-2	20G11ND022-FN		1		0
25	R23-003-C-2	20G11ND022-RC		6		0
26	R23-003-C-1	20G11ND034AA0N-RC		1		0
27	R23-003-C-2	20G11ND065-RC		1		0
28	R23-003-C-1	20G11ND2P1AA-RC		2		0
29	R23-003-C-1	20G11ND5P0AA-RC		4		0
30	R23-003-D-2	20G11ND8P0JA0NNNNN-FN		1		0
31	R23-001-C-2	25B-D017N104-FN		2		0
32	R23-001-C-2	25B-D024N104-FN		1		0
33	R23-001-C-2	25B-D030N104-FN		1		0
34	R23-001-C-2	25B-D043N114-FN		1		0
35	R23-001-C-2	25B-D2P3N104-FN		1		0
43	R23-005-D-2	AF-600FP-RC		1		0
44	R23-001-B-1	ATS22C32S6U-RC		1		0
45	R23-001-B-1	ATV61HD75N4-RC		1		0
46	R23-001-B-1	ATV61HU40N4-FN		1		0
47	R23-001-B-1	ATV61HU55N4-RC		2		0
Calculate sheet

Cell Formulas
Range	Formula
D3,D13:D35,D43:D47	D3	=XLOOKUP(B:B,'Pulled data'!A:A,'Pulled data'!P:P,"")
F3,F13:F35,F43:F47	F3	=XLOOKUP([@Item],Item23,Count23,"")

Named Ranges
Name	Refers To	Cells
Count23	='R23'!$F$3:$F$50	F43:F47, F13:F35, F3
Item23	='R23'!$B$3:$B$50	F43:F47, F13:F35, F3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:F3188	Expression	="$E3:$E58<>$B3:$B58"	text	NO

it is telling me that there is a naming error on the first minisheet how would i find that

 

[svis89]

Can you provide sample data from two of the worksheets (with row headers)? It looks like you have range names in your formulas that we also need to understand.
It may even be easier for all if you put a sanitized version on a share file site, like dropbox.

Inventory Cycle count record with date.xlsx

 

[awoohaw]

I can't tell what column on the calculations sheet is the column your using to find worksheets for.

 

[svis89]

I can't tell what column on the calculations sheet is the column your using to find worksheets for.

I am trying to match both the bin number and the item number so that it will pull the count for that location off of the appropriate worksheet Column A and B

 

[svis89]

I am trying to match both the bin number and the item number so that it will pull the count for that location off of the appropriate worksheet Column A and B

I have been manually changing the formula to match the worksheet designation i do not know a way to tell it to look for this page to pull the correct locations i from the worksheets i used =XLOOKUP([@[Serial Number]],'B02-002 2C'!C:C,'B02-002 2C'!A:A,"") for items that had a serial number but with items without serial numbers i cant do that i have to do a multi criteria search or xlookup and i just cant get it to work right

 

[svis89]

I can't tell what column on the calculations sheet is the column your using to find worksheets for.

Hopefully your doing better than i am trying different variations of formula to get it to work is most recent i also tried going back to the old Index match type formula with no luck
=XLOOKUP(1, ([Item] =Item23) * ([Bin Number] =Bin_Number23),Count23,"NO")
=INDEX('R23'!E3:E50, MATCH(1, ([@Item] =@ 'R23'!B2:B113) * ([@[Bin Number]] =@'R23'!A2:A113), 0))