CODEWITHSUNDEEP
excelexcel-formulavlookup
TomRGarr
TomRGarr

Reputation: 73

Vlookup inside a Vlookup looking for multiple values

I am trying to make a box shown below, all white boxes are for users to enter data, e.g. a start and end date then a tray end type, these will then return how much of that tray end has been used in the set dates in the cell H25.

This will then need to look at another table called product to find every time the selected tray end type appears, from this i should be able to find all products that use it. A sample of this table is shown below.

Then after this it would need to find all of those products in another table called Waste within the set dates (Date Packed column) and add up the Printed Amount column.

I already have a similar formula:

=SUMIFS(Waste[Cost],Waste[Date Packed],">=" & G12,Waste[Date Packed],"<=" & H12, Waste[Product], "=" &H13)

This one is used to add up the cost column for a set date range / product name. So I would guess it would be similar to this but just with an extra Vlookup in as it is needing to search from 2 tables where as the previous formula only uses one.

Thank You in advance for any help / support. - Tom

Edit:

https://www.dropbox.com/s/b6xgbk2zuklg5bp/Tray%20End%20Recording.xlsx?dl=0

I have attached a sample, essentially with the attached, you could insert the date 26/10/18 into the start and end date boxes, then insert Tesco into the tray end type box which would add the printed amount column for all products that use Tesco labels, in this case this should work out as 2,250.

Upvotes: 0

Views: 99

Answers (1)

godspeed23.carl
godspeed23.carl

Reputation: 101

If you cannot use power pivot, I would suggest adding a new column to your 'Waste' table, to look up the Label Type from 'Products'. If you cannot or do not want to update the 'Waste' table, then you could potentially create a copy, say 'Waste2' on a separate (hidden?) tab, by a direct reference to the Waste table and add a column there. Then you can do the 'sumif' using the new column.

Upvotes: 1

Related Questions