Index & Match Trim Right Question

[rex759]

Hello,

I am wondering if I can accomplish what I want to do without adding a helper column. I have the formula in cells I4 to M4 and I9 to M9 showing the Area number from column C if the dates match and there is #2 in Column B. Then in the next row (I5 to M5 and so on), I want to show the event type in column D only if there is #2 in column B. If an extra person is assigned in column F, I want to show that as well. There is where my challenge begins.

I want to shorten the name in Column F showing the last name and whatever letter follows the name in the format of Letter First then the last name. Example is in L11. I added a helper column with a trim, right Substitute formula but wondering if there was a way to do without it.

Audit Ex.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Type	Area	Event	Primary	Extra	Helper
2	10/4/2021	1	111	Zoom Call	Kim
3	10/4/2021	1	112	Zoom Call					10/4/21	10/8/21	10/12/21	10/13/21	10/14/21
4	10/8/2021	2	115	(Audit P)						115	117		119
5	10/12/2021	1	116	Zoom Call						P - Kim	CC Audit		P - Kim
6	10/12/2021	2	117	CC Audit
7	10/13/2021	1	118	Zoom Call
8	10/14/2021	2	119	(Audit P)					10/15/21	10/18/21	10/19/21	10/20/21	10/21/21
9	10/14/2021	1	120	Zoom Call					122		123	127
10	10/14/2021	1	121	Zoom Call					S - Kim		S - Kim	S - Kim Chris Smith (P)
11	10/15/2021	2	122	(Audit S)								S-Kim, P- Smith
12	10/18/2021	1	122	3 Week Call
13	10/19/2021	2	123	(Audit S)
14	10/19/2021	1	124	Zoom Call
15	10/20/2021	2	127	(Audit S)		Chris Smith (P)	Smith
Sheet3

Cell Formulas
Range	Formula
I9:M9,I4:M4	I4	=IFERROR(INDEX($C$2:$C$15,MATCH(1,INDEX((I3=$A$2:$A$15)*(2=$B$2:$B$15),0),0)),"")
I5,M5	I5	=IFERROR(IF(INDEX($D$2:$D$15,MATCH(I4,$C$2:$C$15,0),0)="(Audit P)","P - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(I4,$C$2:$C$15,0),0),IF(INDEX($D$2:$D$15,MATCH(I4,$C$2:$C$15,0),0)="Inventory (Secondary)","S - "&I4&" "&INDEX($F$2:$F$15,MATCH(I4,$C$2:$C$15,0),0),"CC Audit")),"")
J5:L5	J5	=IFERROR(IF(INDEX($D$2:$D$15,MATCH(J4,$C$2:$C$15,0),0)="(Audit P)","P - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(J4,$C$2:$C$15,0),0),IF(INDEX($D$2:$D$15,MATCH(J4,$C$2:$C$15,0),0)="Inventory (Secondary)","S - "&J4&" "&INDEX($F$2:$F$15,MATCH(J4,$C$2:$C$15,0),0),"CC Audit")),"")
I10:M10	I10	=IFERROR(IF(INDEX($D$2:$D$15,MATCH(I9,$C$2:$C$15,0),0)="(Audit P)","P - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(I9,$C$2:$C$15,0),0),IF(INDEX($D$2:$D$15,MATCH(I9,$C$2:$C$15,0),0)="(Audit S)","S - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(I9,$C$2:$C$15,0),0),"CC Audit")),"")
L13	L13	=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(I23)," ",REPT(" ",60)),120),60))
G2:G15	G2	=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(F2)," ",REPT(" ",60)),120),60))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rex759]

I decided to use the helper column. Thanks anyway