Vlookup, if and concatenate matching criteria/designator

Post a sample workbook instead of the above and that might help.

What would your expected outcome be?

If you dont want to/cant use VBA, then try this using 2 helper columns (I used I and J...

	G	H	I	J
36	Designator	Box Serials
37	1	KVZV, XNHM, QJFW, GFVN,	KVZV, XNHM, QJFW, GFVN,	0
38	1	PBMG, BXQD, LLRZ, CGJNL,	KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL,	0
39	2	ZDTN, WPLS, MZGC, HKDT,	KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL,	0 ZDTN, WPLS, MZGC, HKDT,
40	1	QFXB, BMVV, VGHF, VXPK,	KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK,	0 ZDTN, WPLS, MZGC, HKDT,
41	2	ZFTN, RCJD, DCFV, GXRX,	KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK,	0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX,
42	1	SXDX, CPSM, KXTK, PHCX,	KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, SXDX, CPSM, KXTK, PHCX,	0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX,
43	2	QVJT, LWCK, TRWL, LPZQ,	KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, SXDX, CPSM, KXTK, PHCX,	0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX, QVJT, LWCK, TRWL, LPZQ,
44	1	PTRX, CPKD, LXJC, KTNV,	KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, SXDX, CPSM, KXTK, PHCX, PTRX, CPKD, LXJC, KTNV,	0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX, QVJT, LWCK, TRWL, LPZQ,

I37=IF(G37=1,I36&" "&H37,I36)
J37=IF(G37=2,J36&" "&H37,J36)

both copied down. You would then reference the last entry in each column for your answer