Assign value to multiple rows based on rules and values from another column

Hi all,

I have a difficult challenge I'm trying to overcome (at least difficult for me) in that I'm trying to create a new column that assigns a value to a set of rows (serial numbers all from a single production lot #) based on evaluating values in another column. Here's the breakdown:

Each lot produces different SKUs depending on certain factors in production so we might have Lot 123ABC with S/Ns 123ABC-001, 002, and 003 being SKU 123456, while S/Ns 004-006 are a different SKU (456789). Each SKU has a value called a "sub-driver" that I have pulled over to my main table using LOOKUPVALUE, but for each Lot I need to assign an overall "Primary Driver" value which is based on looking at the "sub-driver".

For example, if Lot 123ABC has SKUs that contain sub-drivers SD1 and SD2, and the primary driver in this case is SD1 (this is based on rules), I need to assign the value "PD1" to all of the rows in that lot (all of the S/Ns).

Basically what I'm looking for is a way to search the relevant S/Ns associated with the overall Lot # and then assign the Primary Driver value to all of the S/Ns in that lot based on rules when looking at the "sub-driver" column. I've copied a simple version of what I'm talking about as an example below. In the sample the rules should be that for Lot 123ABC if the SD1 sub-driver is present, then the Primary Driver should be PD1, in Lot 234BCD if the SD3 sub-driver is present then the Primary Driver should be PD3.

Let me know if any additional clarification is needed. Thank you all very much in advance.