Formula help

[tispivey]

Hello all,

I have a couple issues I need help with. I am attempting to populate data into Column D and G in the attached spreadsheet.

The formulas I have, pulls the code from A, compares it to H, and returns what is in I into C. When I attempt to pull the rows that have multiple codes, and populate into D, it gives me an error because there are spaces at the end of the code in A. If I manually remove them, it works, but when I attempt to add TRIM, it doesn't do anything, so I get an error.

The same thing is happening when I attempt to do the same for column G for the ones with three codes. There are a couple in row B that have 4 different codes that I am going to work on that once I get this under control.

Any help you all can provide would be greatly appreciated.

I am hoping this sheet has enough data for you guys, because there are almost 300 lines, and the addon wouldn't let me add them all.

The picture I attached is what I see, as when I uploaded it, it doesnt look like you got the same format.

Test1234.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	TC REASON	COMPL CDE	REASON DESCPT	REASON DESCPT 2	FIX CD	FIX CD 2	FIX CD 3	REASON KEY		Charge Back Code	Charge Back Description
2	WN	151	WIFI NOT WKG		RE-AUTH WIFI			TB	BUS TBL CALL	165	-48 V FAILURE
3	WN	151	WIFI NOT WKG		RE-AUTH WIFI			1T	1 TV	906	A/B SWITCH
4	CVIN	419	CNV/DCT PROBLEM		REPL TIVO MINI			EO	ECHO	904	A/O INSTALLED
5	CVIN	419	CNV/DCT PROBLEM		REPL TIVO MINI			SV	SAVE PROGRAM	905	A/O RECONNECTED
6	DP	453	DVR PROJECT		RPL INT PASSIVE			SD	SATELLITE DISH	312	ADD HSE AMP
7	DP	453	DVR PROJECT		RPL INT PASSIVE			PV	PPV NOT WORKING	434	ADD/REM TRAP
8	MA	354450439	MDM NOT ACQUIRE		CHNG ROUTER LOC	UNAUTH HOOK UP$		MS	MODEM SLW SPEED	404	ADJUST TV SET
9	EN	435	EMTA NO BLK SNC		RPL EXT CORD			OM	ONE MOMENT PLS	405	ADJUST VCR
10	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			NP	NO PICTURE	311	ANIMAL CHEW
11	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			HC	HSD NEW CONNECT	503	AUDIT ERROR
12	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			TO	TIVO	114	CABLE SUCK OUT
13	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			TH	HSD TROUBLE CALL	12	CANCEL @ PRECLL
14	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			PC	PHONE NEW CONNECT	5	CANCEL AT DOOR
15	OM	429	ONE MOMENT PLS		TIVO SVC ACCT			QI	MUST-DO TC HSD DTA	34	CANCEL AT DOOR$
16	MA	432	MDM NOT ACQUIRE		RPL EXT PASSIVE			7T	7 TVS	11	CANCEL BY OFFCE
17	AO	050	ADDITIONAL		REF TO INSTALL			DC	DAMAGED CABLE	7	CANCEL BY OFFIC
18	TO	428	TIVO		REPR TIVO BDBND			VO	VOD NOT WORKING	8	CANCEL@PRECALL
19	MA	051439	MDM NOT ACQUIRE		REF TO MAINT			DT	DTA PROBLEM	10	CANCELLD BY SUB
20	IM	151	INTR MOD NO ACQ		RE-AUTH WIFI			EI	EMTA INT BL SNC	6	CANCELLD BY SUB
21	IM	151	INTR MOD NO ACQ		RE-AUTH WIFI			BP	BUS PHONE	354	CHNG ROUTER LOC
22	MA	439	MDM NOT ACQUIRE		RPL GROUND BLCK			3T	3 TVS	166	CMTS
23	MA	431451	MDM NOT ACQUIRE		REPAIR EXT DRP	NO PROBLEM FND		GH	GHOSTING	134	COMM POWER
24	MA	151	MDM NOT ACQUIRE		RE-AUTH WIFI			H5	HM CAMERA	102	COMML PWR FAIL
25	MA	438	MDM NOT ACQUIRE		DISCO IN ERROR			SI	SELF INSTALLATION	350	CONF WLESS CST$
26	MA	438	MDM NOT ACQUIRE		DISCO IN ERROR			8T	8 TVS	117	CRACKED SHIELD
27	MA	151	MDM NOT ACQUIRE		RE-AUTH WIFI			DD	DWN OR LOW DROP	38	CUST EDUCATION$
28	MA	430	MDM NOT ACQUIRE		REPLACE EXT DRP			VP	VCR PROBLEM	36	CUST INSTAL EQ$
29	MA	430	MDM NOT ACQUIRE		REPLACE EXT DRP			RC	REMOTE PROBLEM	407	CUST INSTALD EQ
30	MA	051	MDM NOT ACQUIRE		REF TO MAINT			IP	INTERMITTENT PRB	218	CUST PHONE PROB
31	MA	051	MDM NOT ACQUIRE		REF TO MAINT			SG	SIGNAL LEAK - HOME	400	CUSTOMER EDUCAT
32	MA	D30	MDM NOT ACQUIRE					WN	WIFI NOT WKG	146	CUT/DAMAGED FIB
33	SC	051	SNGL CHANL OUT		REF TO MAINT			LW	LOW CHAN PROB	120	CUT/DMGD FIBER
34	DP	050	DVR PROJECT		REF TO INSTALL			CM	CUSTOMER IMPACT MAINT	D30	D3 SWAP
35	DP	050	DVR PROJECT		REF TO INSTALL			AU	FIELD AUDIT	406	DEFECTVE A/B SW
36	MA	051453	MDM NOT ACQUIRE		REF TO MAINT	NO ADULT		CV	CNV/DCT PROBLEM	306	DIRECT P/U DROP
37	IM	434432	INTR MOD NO ACQ		ADD/REM TRAP	OK ON TEST SET		CI	CLI	402	DIRECTP/USUB EQ
38	AO	437	ADDITIONAL		RPL EXT F CONN			BI	BUS INTERNET	910	DISCO AT TAP
39	OM	417	ONE MOMENT PLS		REPL TIVO Q			BS	BUS SITE SURVEY	500	DISCO IN ERROR
40	AO	437	ADDITIONAL		RPL EXT F CONN			CE	CUSTOMER EDUC	438	DISCO IN ERROR
41	OM	417	ONE MOMENT PLS		REPL TIVO Q			BD	BURY DROP	911	DISCONNECT A/O
42	IM	051434082	INTR MOD NO ACQ		REF TO MAINT	REPL HM CAMERA		9T	9 TVS	902	DROP RECONNECT
43	MA	432434431	MDM NOT ACQUIRE		RPL EXT PASSIVE			MC	MUST-DO TC VIDEO	304	DROP PASSIVE
44	MA	051438432	MDM NOT ACQUIRE		REF TO MAINT			DA	DISASTER	903	DROP RELOCATE
45	NP		NO PICTURE					TP	TAP DISCONNECT	301	DROP/GNDBLK-TV
46	MA	039	MDM NOT ACQUIRE		TEL/HC NOT HOME			PR	PHONE RECONNECT	300	DROP/TAP-GNDBLK
47	MA		MDM NOT ACQUIRE					MR	MDVR PROBLEM	108	FEEDER ACTIVES
48	IM	434437	INTR MOD NO ACQ		ADD/REM TRAP	CANCEL BY OFFIC		MA	MDM NOT ACQUIRE	110	FEEDER CABLE
49	MS	437	MODEM SLW SPEED		RPL EXT F CONN			LS	LST/DIST SPEECH	109	FEEDER PASSIVES
50	MA		MDM NOT ACQUIRE					IN	INSTALLFOLLOWUP	111	FEEDERCONNECTOR
Sheet1

Cell Formulas
Range	Formula
E2:E50	E2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(LEFT(B2,3)),J:K,2,0)),"")
F2:F50	F2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(MID(B2,6,3)),J:K,2,0)),"")
G2:G50	G2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(RIGHT(B2,3)),J:K,2,0)),"")
C2:C50	C2	=VLOOKUP(LEFT(A2,2),H:I,2,0)

 

[etaf]

to be honest i don't know , as i'm not sure what you are trying to do yet,

if you could pick some examples where the code does not match and explain what you expect to happen
I suspect it maybe you say CODES , plural , and that maybe more than i 2 character code is enter

Highlighted in column A

CVIN
MAWN
AOTO
etc

should these be 2 codes
CV
IN
MA
WN
AO
TO

and is that the issue - NOT the spaces

 

[tispivey]

I finally figured it out. I had to completely rework my formula.

Thank you for the help.

This is the formula that worked, just had a ton of WTF moments getting there.

=IFERROR(IF(LEN(TRIM(B2))>=9,VLOOKUP(VALUE(TRIM(MID(B2,7,3))),M:N,2,0),""),"")

 

[etaf]

glad you are now sorted, and thanks for taking the time to show a solution, for other people searching for a similar situation