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

#### Pulsar3000

##### New Member
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

##### New Member
Try first row formula and drag it down.
Book1.xlsm
NSTYZAAABAC
1
25070, 25, 55252
36030, 35, 40302
4200150, 100, 1201002
5120100, 90, 80801.5
6
Sheet3
Cell Formulas
RangeFormula
Z2:Z5Z2=IF(ISNUMBER(FIND(N2/2,T2)),N2/2,IF(ISNUMBER(FIND(N2/1.5,T2)),N2/1.5,""))
AA2:AA5AA2=N2/Z2

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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Replies
13
Views
290
Replies
4
Views
48
Replies
2
Views
39
Replies
6
Views
126
Replies
2
Views
236

1,132,976
Messages
5,656,189
Members
418,288
Latest member
reba557

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back