@mattmatt245 , this
does look like an assignment/interview/homework question, and most forums have guidelines against helping in such instances.
I see you might have posted a similar question here:
https://www.mrexcel.com/board/threads/batch-number-lookup.1233514/
where there's a video (youtube:
) about an SQL solution and a link to downloads (
https://techtfq.com/blog/lets-simplify-and-solve-a-complex-sql-interview-problem ) all in the public domain.
In the attached is a
Power Query solution at cell F22, and that taken a little further at cell J22.
For cross-checking, I've added a pivot table on
Sheet5 at cell J2.