Multiple Values in Single Cell: How can I apply formulas?

[Pulsar3000]

Hello Excel Gurus!

Here's what's going on. I'm dealing with regular and overtime hourly rates.

The item after the bolded word function below yields results in a single cell as shown in the yellow cell of my image. Since multiple values are in a single cell, the cell is text. I know the Value function, multiplying by 1 or adding a 0 can fix the number saved as text to an individual number so some version of this I presume will be imbedded in the potential solution.

Question: Can the gold and blue box be accomplished strictly using some function(s) in cells Z2 and AA2 not involving VBA or Text to Columns?

Function: T2=(IF(OR(R2="OT Hours",R2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$16=A2)*($D$2:$D$16=D2)*($F$2:$F$16=F2)*(@$I$2:$I$16=I2)*($K$2:$K$16<>0),$N$2:$N$16,""))),0))

Functions/Descriptions of Cells Referenced in Above Function:
R2=INDEX($K$1:$M$1,MATCH(TRUE,INDEX(K4:M4<>0,),0))
A2=Name of place that is just text
D2=Name of person that is just text
F2=INDEX(Type!$B$2:$B$45,MATCH(E2,Type!$A$2:$A$45,0))
I2=INDEX(Rates!$C$2:$C$6,MATCH(1,((Rates!$B$2:$B$6=A2)*(Rates!$C$2:$C$6<=G2)*(Rates!$D$2:$D$6>=G2)),0))
K2=Amount of Hours hardcoded
N2=Rate hardcoded

 

[Pulsar3000]

Try first row formula and drag it down.

Book1.xlsm
	N					S	T					Y	Z	AA	AB	AC
1
2	50						70, 25, 55						25	2
3	60						30, 35, 40						30	2
4	200						150, 100, 120						100	2
5	120						100, 90, 80						80	1.5
6
Sheet3

Cell Formulas
Range	Formula
Z2:Z5	Z2	=IF(ISNUMBER(FIND(N2/2,T2)),N2/2,IF(ISNUMBER(FIND(N2/1.5,T2)),N2/1.5,""))
AA2:AA5	AA2	=N2/Z2

@maabadi

Thanks for you assistance maabadi! I really appreciate you lending a hand.