Excel Formula / VBA Caluclate No of Hours based on attribute criteria as one array or VBA is possible

Hi Experts

I hope you can help

I have an Excel sheet that generates data from our consignment values (Columns A to I – this is pasted in). This data ultimately breaks down, per hour, how may items from the shipment needs to be quality checked based on their attributes (Maternity Clothing, Kids clothing or Product)

Column K performs a lookup to the product file that assigns the attribute to the shipment based on the biggest percentage of product mix – this is abbreviated to PRO, MAT or KID) – the result of this is extracted to cell N4

Cell M15 downward extracts the unique purchase order references from the shipment and column N sums the quantity of items per PO.

To the right, in cells V9 to W18 contains the criteria on which the number of hours is calculated against the quantity on each PO

Finally, cells V2 to W4 contain the items per hour that our QC have informed us they are able to check

For the purpose of this request, I need a result in Column O (beginning in O15 against each PO line), that, based on the attribute in N4, picks the correct criteria and calculates the number of hours that is required so our resource planner can be updated

For example, in the uploaded file, I have included the desired result against the first PO using a nested formula on the assumption that the attribute is KID (it does not use N4 as a trigger just the criteria only), however, what I need is a formula or VBA that can encapsulate this result based on the attribute. Therefore, it should say if N4=KID and if the PO qty if less than or equal to 100, then divide 5 (in cell W16 - qty needed to check) by 105 in cell W2 (number of items that can be checked per hour) and so on and then if N4-PRO then use the criteria from V10 to W13 etc.

I don’t know how to assemble all this information into one formula. I’ve tried an array but could get this to work successfully but would ultimately want to avoid having separate columns, one for each attribute with the corresponding nested formula.

If you can help in any way I would be very grateful

Thanks
j
QC-Check-Calc_v2.xlsm