TL;DR
=ARRAYFORMULA(IF(B2:B<>"", IFS(B2:B="Lightweight Trainers", "LW")&"-"&IFS(I2:I="Blue", 1, I2:I="Red", 2)&"-"&K2:K,))
Answer
What you want is basically:
<title>-<color number>-<shoe size>
To convert this to a function we can split it into each part and take it step by step:
Step 1: Title
For the first part -the title- we need to match the value with the shorthand. A simple list in an IFS is enough.
IFS(B2="Lightweight Trainers", "LW")
Obviously for now it only has a single value (Lightweight Trainers) but you could add more:
IFS(B2="Lightweight Trainers", "LW", B2="Heavyweight Trainers", "HW")
Step 2: color number
Similar to the previous step, it’s a mapping using ifs:
IFS(I2="Blue", "-1", I2="Red", "-2")
The dash is added so when adding everything it will only have it if
Step 3: shoe size
In this case we can simply get the value:
K2
Step 4: Adding everything together
We only need to add it with the dashes in between:
=IFS(B2="Lightweight Trainers", "LW")&"-"&IFS(I2="Blue", 1, I2="Red", 2)&"-"&K2
Step 5: Extending for the entire column automatically
We will use ARRAYFORMULA to add a single formula to the first cell and get it automatically extended to the entire column. We first add it to the formula we already have, and then extend the ranges to the entire column:
=ARRAYFORMULA(IFS(B2:B="Lightweight Trainers", "LW")&"-"&IFS(I2:I="Blue", 1, I2:I="Red", 2)&"-"&K2:K)
Remember to remove all the values in the column so array formula doesn’t override them (it would generate an error).
As you can see the formula generates errors for the rows that have no values. A good way of handling this case is to filter the rows without a title. In a single row would be:
=IF(B2<>"", [the entire formula],)
Notice the last comma.
So putting everything together and extending its range to the column, is:
=ARRAYFORMULA(IF(B2:B<>"", IFS(B2:B="Lightweight Trainers", "LW")&"-"&IFS(I2:I="Blue", 1, I2:I="Red", 2)&"-"&K2:K,))
Adding this to N2 should work.
Final notes
It seems that you use 150 when the size it’s not a whole number. If you want to keep that functionality you may use:
IF(K2-int(K2)=0, K2, 150)
On the last component and expand it the same way.
You may also want to prevent having two dashes when a value is missing (LW-5 instead of LW--5). To do so, I’d recommend adding it to each component instead of the formula that adds them together.
References
