Making a list from a table

[Zacariah171]

I have a "table" of data and I'm trying to make a list from that table. The first column has the location name of where the equipment was used and the other columns list which equipment was used. I'm trying to take the data from there and put all the equipment into one column and the location they were on in the second column. I'm using the formula,

{=IFERROR(INDEX($CG$7:$CG24,SMALL(IF($CH$7:$CL24=$CV10,ROW($CH$7:$CL24)-ROW(INDEX($CH$7:CL24,1,1))+1),$CU10)), IFNA(INDEX($CG$7:$CG24, MATCH(1, MMULT(--($CH$7:$CL24=$CV10), TRANSPOSE(COLUMN($CH$7:$CL24)^0)), 0)), "??????"))}

but I have an issue when the same Equipment ID is used more than once on the same row because the last part of my formula is returning a 2 for cells CW10 and CW11. I'm using Excel 365. I hope I've given enough information but please let me know if you need more. Thank you in advance!

So, the question is, how do I turn this...

into this, where cells CW10 and CW11 are showing the correct location name?

 

[DanteAmor]

The following could be an alternative. (While someone helps us with a functions of version 365).

Dante Amor
	CG	CH	CI	CJ	CK	CL	CM								CU	CV	CW
6	Location	Box	Box	Box	Box	Box										Equip ID	Location
7	Cue 17A	555	556	501											1	302	Cue 17B
8	Cue 17B	302	355	355											2	302	Cue 17D
9	Cue 17D	302	339												3	339	Cue 17D
10	Cue 17C	801	805	810											4	355	Cue 17B
11															5	355	Cue 17B
12															6	501	Cue 17A
13															7	555	Cue 17A
14															8	556	Cue 17A
15															9	801	Cue 17C
16															10	805	Cue 17C
17															11	810	Cue 17C
18															12	700	???
Hoja19

Cell Formulas
Range	Formula
CW7:CW18	CW7	=IFERROR(INDIRECT(TEXT(INT(SUMPRODUCT(SMALL(IF($CH$7:$CL$11=CV7,ROW($CH$7:$CL$11)*1000+COLUMN($CH$7:$CL$11)),COUNTIF(CV$7:CV7,CV7)))/100),"f000"&"C085"),0),"???")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CA_Punit]

I tried but not able to solve..Thanks @DanteAmor for your help.
Instead i solved for Equipment Id from the List .

@DanteAmor Kindly help with a shorter formula for Equipment ID

Book1
	CG	CH	CI	CJ	CK	CL
6	Location	1st Box	2nd Box	3rd Box	4th Box	5th Box
7	Cue 17A	555	556	501
8	Cue 17B	302	355	355
9	Cue 17D	302	339
10	Cue 17C	801	805	810
11
12
13
14		302
15		302
16		339
17		355
18		355
19		501
20		555
21		556
22		801
23		805
24		810
25
Sheet4

Cell Formulas
Range	Formula
CH14:CH25	CH14	=SORT(IFERROR(INDEX(INDEX($CH$7:$CL$10,INT((ROW($CG$7:$CG$26)-ROW($CG$7))/5)+1,MOD(ROW($CG$7:$CG$26)-ROW($CG$7),5)+1),AGGREGATE(15,6,IFERROR(MATCH(INDEX($CH$7:$CL$10,INT((ROW($CG$7:$CG$26)-ROW($CG$7))/5)+1,MOD(ROW($CG$7:$CG$26)-ROW($CG$7),5)+1),INDEX($CH$7:$CL$10,INT((ROW($CG$7:$CG$26)-ROW($CG$7))/5)+1,MOD(ROW($CG$7:$CG$26)-ROW($CG$7),5)+1),0),FALSE),ROW($A$1:A12))),""))
Dynamic array formulas.

 

[DanteAmor]

I changed the INDIRECT function to be volatile.
I observed that the formula does not require the column, I only need to obtain the data from the CG column.
Try the following:

Dante Amor
	CG	CH	CI	CJ	CK	CL	CM								CU	CV	CW
6	Location	Box	Box	Box	Box	Box										Equip ID	Location
7	Cue 17A	555	556	501											1	302	Cue 17B
8	Cue 17B	302	355	355											2	302	Cue 17D
9	Cue 17D	302	339	355											3	339	Cue 17D
10	Cue 17C	801	805	810											4	355	Cue 17B
11															5	355	Cue 17B
12															6	355	Cue 17D
13															7	501	Cue 17A
14															8	555	Cue 17A
15															9	556	Cue 17A
16															10	801	Cue 17C
17															11	805	Cue 17C
18															12	810	Cue 17C
19															13	900	???
Hoja19

Cell Formulas
Range	Formula
CW7:CW19	CW7	=IFERROR(INDEX($CG$1:$CG$24,INT(SUMPRODUCT(SMALL(IF($CH$7:$CL$24=CV7,ROW($CH$7:$CL$24)*1000+COLUMN($CH$7:$CL$24)),COUNTIF(CV$7:CV7,CV7)))/1000)),"???")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Amit Tandon]

26Aug19.xlsx
	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW
1	Location	Box	Box	Box	Box	Box										Equip ID	Location
2	Cue 17A	555	556	501												302	Cue 17B
3	Cue 17B	302	355	355												302	Cue 17D
4	Cue 17D	302	339													339	Cue 17D
5	Cue 17C	801	805	810												355	Cue 17B
6																355	Cue 17B
7																501	Cue 17A
8																555	Cue 17A
9																556	Cue 17A
10																801	Cue 17C
11																805	Cue 17C
12																810	Cue 17C
Sheet54

Cell Formulas
Range	Formula
CW2:CW12	CW2	=INDEX(CG$2:CG$5,AGGREGATE(15,6,(ROW(CH$2:CL$5)-ROW(CH$2)+1)/(CH$2:CL$5=CV2),COUNTIF(CV$2:CV2,CV2)))

 

[Zacariah171]

Thank you @Amit Tandon and @DanteAmor! I tried both of your answers and they both work great! Thank you for the help and the quick responses!

 

[DanteAmor]

Glad we could help & thanks for the feedback