Checking if a value exists within a range of other values

[YorkiePud]

Good evening

I am trying to build a mechanism within a spreadsheet to cross check remaining stocks of a product against the original list of items that were booked in. The complexity comes from the fact that the products are purchased in a serialised range and the stock figures show which serial numbers are remaining (along with the quantities).

I'm trying to find a way to cross check the remaining serial numbers in stock (in the table on the right hand side of the example shown below) vs what was originally ordered so that I can populate the column in yellow. Does anyone know how I can do this?

 

[alansidman]

To make sure we understand, please mock up the yellow column with the expected results. Also, it would be beneficial if you reposted your tables using XL2BB. We cannot manipulate data in a picture. The current presentation requires us to re-enter all your data which is time consuming. Help us to help you by making it easy for us to get you a solved response.

 

[hagia_sofia]

Hello, not sure if I understand it correctly, so it is a trial and error:

=MAP(B2:B31,C2:C31,LAMBDA(x,y,SUM((K2:K15>=x)*(L2:L15<=y)*(M2:M15))))

 

[hagia_sofia]

Apologies for forgetting to address the numbers as text problem, please ignore #3:

=MAP(B2:B31*1,C2:C31*1,LAMBDA(x,y,SUM((K2:K15*1>=x)*(L2:L15*1<=y)*(M2:M15*1))))

 

[YorkiePud]

@hagia_sofia : thank you so much. That solution is incredible and works well. As @alansidman said, I didn't help by only providing you with a picture (apologies, this is my first post and didn't think it through) so I'm very grateful that you worked out a solution for this

​