am in going in the right direction using XLOOKUP

[N0t Y0urs]

Hi All,

My husband struggles with Excel and finds G Sheets easier to work with. So this is my current situation. I am trying to create an inventory system of sorts. Overall I will not have more than 21 items but to create the final product I might need 3 items up to say 10 items. So what I was thinking of doing was creating a XLOOKUP table that has the final product with all the items required to create the item which I can then use to create my raw cossts.

But now saying it out-loud that doesn't seem to make sense.

For example lets say this

Pepper Oil (final product) - Cost would be total of all elements to produce

1 x 1/2 litre of oil
1 x 3/4 kilo of peppercorns
1 x Pepper Oil label
1 x 500ml bottle
1 x 500ml lid
1 x Admin fees
1 x fixed costs (cleaning cooking etc)

Listed above is just an example of how I am attacking this. All my raw costs are in bulk example 40 litres of Oil is $40 therefore my oil cost in this case is $0.50 if I extended that out then my total production cost might be $5.99. Is XLOOKUP the way to do this or am I looking for a solution in another direction?

Thanks In advance

 

[SunnyAlv]

hi @N0t Y0urs, Can you include a picture or table of what the data looks like to make it easier for someone to know the achievements you want further?

 

[N0t Y0urs]

Thanks, I did think about it earlier but got stuck on what you needed vs what meets upload file size criteria.

So currently I am working off a COGS Sheet for each product see below. Sorry missing columns but they are C,D,E,G,H

So from here I have another sheet (Resources) which is where I was thinking of the table to use for XLOOKUP. In the above image I would have column G report the cost for this particular product. My Resources would have my bulk costs that I could expand to bring it back to a single measurement level but I see that as slightly redundant

So this is currently the start of my Resource Sheet as I am trying just to get all costs on one sheet, then I can figure out look, groups and referencing (lookup stuff). Currently columns B-F

Hope this helps a little