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

svis89

Board Regular
Joined
Oct 19, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
=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
 
Okay so tell me if you see a problem with the following Con and Con_23 are the columns i used text join on
=TEXTJOIN("-",TRUE,Table2[@[Bin Number]],Table2[@Item]) to combine to two criteria and then
=XLOOKUP(@Con,Con_23,Count23,,1)
now i think this would do what i want looking up both the specific bin location and the item name so it should only pull the count for that particular item
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Okay so tell me if you see a problem with the following Con and Con_23 are the columns i used text join on
=TEXTJOIN("-",TRUE,Table2[@[Bin Number]],Table2[@Item]) to combine to two criteria and then
=XLOOKUP(@Con,Con_23,Count23,,1)
now i think this would do what i want looking up both the specific bin location and the item name so it should only pull the count for that particular item
i know it isnt exactly what i asked for but it is the closest i can come up with unless you can create a formula that would be able to search columns a and b in a way that would treat it as the text join does i cant find a good way to do it
 
Upvote 0
Solution

Forum statistics

Threads
1,223,885
Messages
6,175,181
Members
452,615
Latest member
bogeys2birdies

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
Back
Top