How to get Multiples Match in one cell

[lu1zuk]

Sobe_e_Desce_2.0.xlsx
	A	B		D	E	F	G	H	I		K	L	M	N	O	P	Q	R	S	T	U
2	Usuário	Tempo		Cargos	Separador		Horas	Separador	Qual a Equipe?		Ids	Qual a Equipe?								Equipe	ID
3	MGL229456993373519872	161 Horas 16 Minutos 49 Segundos		869353363609899042, 869353363622494315, 869353363635073066, 869353363635073071, 869353363647651886, 869353363647651889, 869353363656020030, 869353363656020033, 869353363656020035, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363677020219, 869353363714744337, 869353363794444395, 869353363807010852, 869353363815411754, 869353363815411755, 869353363832180797, 869353363844780126, 869353363861536791, 869353764727947324, 879479435332579329, 892270088286273586, 896811812132110336, 917381741084295198, 923768995981697114, 934915112270188555, 941733727610998804, 963086806784565328, 970862133929730138, 987429669550850118, 989523321014079488, 992073031348400138, 869353363609899038	|||	161	161	|||	Equipe Mov.Call		<@229456993373519872>	Equipe Mov.Call							869353363815411757	Equipe Eventos	869353363815411757
4	oyuric755287807337169017	141 Horas 33 Minutos 9 Segundos		869353363609899042, 869353363635073066, 869353363635073072, 869353363668598798, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363807010855, 869353363815411753, 869353363823816720, 869353363861536791, 869353764727947324, 879479406039531562, 892270088286273586, 896811812132110336, 934915112270188555, 970862133929730138, 978155147928674324, 869353363609899038	|||	141	141	|||	Equipe Registro		<@755287807337169017>	Equipe Registro							869353363815411758	Auxiliar Eventos	869353363815411758
5	lais bianchessi960299650810794004	133 Horas 28 Minutos 38 Segundos		869353363609899042, 869353363635073066, 869353363635073072, 869353363647651883, 869353363647651889, 869353363656020029, 869353363656020030, 869353363656020032, 869353363668598798, 869353363668598799, 869353363668598801, 869353363668598804, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353764727947324, 892270088286273586, 901201959556616212, 941733727610998804, 988969269536649246, 869353363609899038	|||	133	133	|||	Equipe Mov.Call		<@960299650810794004>	Equipe Mov.Call							869353363815411754	Equipe Mov.Call	869353363815411754
6	! Ryo𝐾en961772149956309079	127 Horas 41 Minutos 13 Segundos		869353363609899040, 869353363609899042, 869353363609899044, 869353363635073066, 869353363635073068, 869353363635073069, 869353363647651889, 869353363656020029, 869353363656020031, 869353363656020034, 869353363668598798, 869353363668598799, 869353363668598802, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363731533894, 869353363807010852, 869353363815411754, 869353363815411757, 869353363874131972, 869353363886710784, 869353764727947324, 879479435332579329, 892270088286273586, 933510183257272321, 934915112270188555, 940077814516953088, 963086806784565328, 973715342046146590, 973715650528833598, 988969269536649246, 869353363609899038	|||	127	127	|||	Equipe Eventos		<@961772149956309079>	Equipe Eventos							869353363815411756	Equipe Recrutamento	869353363815411756
7	Alves .мv858781483149819945	124 Horas 40 Minutos 17 Segundos		869353363609899040, 869353363635073068, 869353363647651880, 869353363656020033, 869353363668598799, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363794444395, 869353363807010852, 869353363815411758, 869353363832180800, 869353363886710784, 879479435332579329, 892270088286273586, 901201918481821697, 923768995981697114, 934915112270188555, 941733727610998804, 950958148557090836, 955288897632485427, 963086806784565328, 970862133929730138, 978155147928674324, 978779998502670416, 981030539815452672, 983118451012927518, 869353363609899038	|||	124	124	|||	Auxiliar Eventos		<@858781483149819945>	Auxiliar Eventos							869353363815411755	Equipe Divulgação	869353363815411755
8	Gaguinho.lz931988559970332704	122 Horas 10 Minutos 24 Segundos		869353363609899040, 869353363609899042, 869353363635073066, 869353363635073071, 869353363668598798, 869353363668598799, 869353363668598801, 869353363668598804, 869353363677020212, 869353363677020215, 869353363807010852, 869353363807010855, 869353363815411754, 869353764727947324, 879479406039531562, 892270088286273586, 896811812132110336, 901201955903406171, 934915112270188555, 973019878900531271, 992073031348400138, 869353363609899038	|||	122	122	|||	Equipe Mov.Call		<@931988559970332704>	Equipe Mov.Call							869353363815411752	Equipe CDC	869353363815411752
9	calango966333127716909118	122 Horas 6 Minutos 11 Segundos		869353363609899042, 869353363635073066, 869353363656020031, 869353363668598798, 869353363668598799, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411753, 869353363815411756, 869353363874131971, 869353764727947324, 892270088286273586, 934915112270188555, 963086806784565328, 973715650528833598, 978155147928674324, 991499849826578502, 869353363609899038	|||	122	122	|||	Equipe Recrutamento		<@966333127716909118>	Equipe Recrutamento							869353363815411753	Equipe Registro	869353363815411753
10	! HUNTER370612816291561474	121 Horas 7 Minutos 14 Segundos		869353363647651886, 869353363656020032, 869353363656020033, 869353363668598797, 869353363668598798, 869353363668598802, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020215, 869353363714744338, 869353363807010852, 869353363815411754, 869353363823816722, 869353363844780126, 879479412733665312, 892270088286273586, 923768995981697114, 933510165607632906, 963086806784565328, 970862133929730138, 988969269536649246, 989523321014079488, 992073031348400138, 869353363609899038	|||	121	121	|||	Equipe Mov.Call		<@370612816291561474>	Equipe Mov.Call							973715650528833598	Equipe Suporte	973715650528833598
11	! 𝘽𝙚𝙯𝙚𝙧𝙧𝙤👑906573722838761562	120 Horas 4 Minutos 11 Segundos		869353363609899042, 869353363635073066, 869353363656020031, 869353363656020035, 869353363656020037, 869353363668598797, 869353363668598798, 869353363668598799, 869353363668598802, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363794444395, 869353363807010852, 869353363815411754, 869353363815411759, 869353363823816720, 869353764727947324, 879479406039531562, 892270088286273586, 933510165607632906, 934915112270188555, 940077814516953088, 941733727610998804, 955288897632485427, 963086806784565328, 973715650528833598, 989523321014079488, 869353363609899038	|||	120	120	|||	Equipe Mov.Call		<@906573722838761562>	Equipe Mov.Call							869353363807010855	Equipe Design	869353363807010855
12	! Sr.Natsuzinho667095822101512194	118 Horas 41 Minutos 54 Segundos		869353363609899042, 869353363635073066, 869353363668598801, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020215, 869353363807010852, 869353363807010855, 869353363815411754, 869353363844780126, 869353764727947324, 886458500862054470, 892138408418377730, 892270088286273586, 896811812132110336, 901201926312566845, 934915112270188555, 941733727610998804, 945458390333784146, 945790336070983750, 955288897632485427, 962805646099640340, 963086806784565328, 970207907369078784, 970862133929730138, 977985735317655552, 978155147928674324, 989523321014079488, 992073031348400138, 869353363609899038	|||	118	118	|||	Equipe Mov.Call		<@667095822101512194>	Equipe Mov.Call							869353363807010854	Equipe Hot	869353363807010854
13	Guh ツ692180947990216725	112 Horas 7 Minutos 58 Segundos		869353363609899042, 869353363635073066, 869353363635073070, 869353363647651889, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353764727947324, 892270088286273586, 896811812132110336, 901201926312566845, 934915112270188555, 935385249389608991, 978779998502670416, 989523321014079488, 992073031348400138, 869353363609899038	|||	112	112	|||	Equipe Mov.Call		<@692180947990216725>	Equipe Mov.Call							896811812132110336	Equipe EXAFLIX	896811812132110336
14	! Tsuna 希787068655070347287	94 Horas 21 Minutos 39 Segundos		869353363609899040, 869353363609899042, 869353363622494314, 869353363622494315, 869353363635073066, 869353363647651883, 869353363647651886, 869353363647651889, 869353363656020029, 869353363668598798, 869353363668598799, 869353363668598802, 869353363677020210, 869353363677020212, 869353363677020217, 869353363731533902, 869353363794444395, 869353363807010852, 869353363832180800, 869353363886710784, 869353363886710791, 869353363895107672, 879479435332579329, 892270088286273586, 906937079194325092, 941733727610998804, 991499849826578502, 992073031348400138, 869353363609899038	|||	94	94	|||			<@787068655070347287>								962805646099640340	Equipe Jornalismo	962805646099640340
15	! bru 🍥795107916989857812	81 Horas 11 Minutos 54 Segundos		869353363668598801, 869353363677020210, 869353363677020212, 869353363677020217, 869353363807010852, 869353363815411753, 869353363832180797, 869353764727947324, 892270088286273586, 978155147928674324, 869353363609899038	|||	81	81	|||	Equipe Registro		<@795107916989857812>	Equipe Registro							993257880927076392	Equipe Games	993257880927076392
Planilha1

Cell Formulas
Range	Formula
F3:F15	F3	=LEFT(B3,3)
G3:G15	G3	=--SUBSTITUTE(F3,"H","")
I3:I15	I3	=IFERROR(@INDEX(T$3:T$15,AGGREGATE(15,6,(ROW(T$3:T$15)-ROW(U$3)+1)/ISNUMBER(SEARCH(U$3:U$15,D3)),1)),"")
K3:K15	K3	=CONCAT("<@",J3,">")
L3:L15	L3	=IFERROR(@INDEX(T$3:T$15,AGGREGATE(15,6,(ROW(T$3:T$15)-ROW(T$3)+1)/ISNUMBER(SEARCH(U$3:U$15,D3)),1)),"")
U3:U15	U3	=RIGHT(S3,18)

Tryind to use INDEX - MATCH and IFERROR to find in a location X results. The results im trying to find is " ID " that will result in " Equipe " and it will match from "Cargos" , but i dont know how to make it show multiple matchs, so how can i do it? tryed googling but i dont know the right terms for in english and in my language (pt-br) it doesnt has that much info

 

[lu1zuk]

I'm using the spreadsheet like this here

Sobe_e_Desce_2.1.xlsm
	A	B		D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
2	Usuário	Tempo		Cargos		Separador	Horas	Separador		Id		Ids	Horas	Cargo	Situaçao	Qual a Equipe?					Equipe	ID	Ids checagem dupla	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe	Tags Equipe
3	oyuric755287807337169017	49 Horas 40 Minutos 18 Segundos		869353363609899040, 869353363609899042, 869353363635073066, 869353363635073072, 869353363668598798, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363807010855, 869353363815411753, 869353363823816722, 869353363861536791, 869353764727947324, 879479406039531562, 892270088286273586, 934915112270188555, 970862133929730138, 978155147928674324, 978779998502670416, 993257880927076392, 869353363609899038		|||	49	|||		755287807337169017		<@755287807337169017>	49h	Coronel	Mantem	<@&869353363815411753>, <@&869353363807010855>, <@&993257880927076392>				869353363815411757	<@&869353363815411757>	869353363815411757	<@755287807337169017>	<@&869353363815411753>	<@&869353363807010855>	<@&993257880927076392>
4	lais bianchessi960299650810794004	47 Horas 46 Minutos 14 Segundos		869353363609899042, 869353363635073066, 869353363635073072, 869353363647651883, 869353363647651889, 869353363656020029, 869353363656020030, 869353363656020032, 869353363668598798, 869353363668598799, 869353363668598801, 869353363668598804, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353764727947324, 892270088286273586, 901201955903406171, 941733727610998804, 988969269536649246, 993269461572395088, 869353363609899038		|||	47	|||		960299650810794004		<@960299650810794004>	47h	Cabo	Mantem	<@&869353363815411754>				869353363815411758	<@&869353363815411758>	869353363815411758	<@960299650810794004>	<@&869353363815411754>
5	! 𝘽𝙚𝙯𝙚𝙧𝙧𝙤👑906573722838761562	45 Horas 19 Minutos 39 Segundos		869353363609899042, 869353363635073066, 869353363656020031, 869353363656020035, 869353363656020037, 869353363668598797, 869353363668598798, 869353363668598799, 869353363668598802, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363794444395, 869353363807010852, 869353363815411754, 869353363815411759, 869353363832180800, 869353363844780126, 869353764727947324, 879479406039531562, 892270088286273586, 901201936555069510, 933510165607632906, 934915112270188555, 940077814516953088, 941733727610998804, 955288897632485427, 963086806784565328, 973715650528833598, 989523321014079488, 993269461572395088, 869353363609899038		|||	45	|||		906573722838761562		<@906573722838761562>	45h	Sub Tenente	Mantem	<@&869353363815411754>, <@&973715650528833598>				869353363815411754	<@&869353363815411754>	869353363815411754	<@906573722838761562>	<@&869353363815411754>	<@&973715650528833598>
6	H A S H I R A M A ⛩584794668227887104	42 Horas 41 Minutos 39 Segundos		869353363677020214, 869353363714744338, 869353363807010852, 869353363815411756, 869353363832180800, 869353363861536798, 869353363886710784, 892270088286273586, 901201918481821697, 934915112270188555, 941733727610998804, 970207907369078784, 981030539815452672, 983104269186134056, 989523321014079488, 992073031348400138, 993269461572395088, 869353363609899038		|||	42	|||		584794668227887104		<@584794668227887104>	42h	Major Brigadeiro	Mantem	<@&869353363815411756>				869353363815411756	<@&869353363815411756>	869353363815411756	<@584794668227887104>	<@&869353363815411756>
7	Sr. Zerefinho668600077060931608	40 Horas 58 Minutos 17 Segundos		869353363635073068, 869353363635073072, 869353363668598798, 869353363668598799, 869353363668598802, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363714744337, 869353363731533902, 869353363807010852, 869353363815411757, 869353363886710791, 879479435332579329, 890717397156266045, 892270088286273586, 896811812132110336, 906299427193499649, 945790336070983750, 970207907369078784, 973716553935769620, 980093439502864425, 989523321014079488, 993257880927076392, 869353363609899038		|||	40	|||		668600077060931608		<@668600077060931608>	40h	Hokage Ultimate	Mantem	<@&869353363815411757>, <@&896811812132110336>, <@&993257880927076392>				869353363815411755	<@&869353363815411755>	869353363815411755	<@668600077060931608>	<@&869353363815411757>	<@&896811812132110336>	<@&993257880927076392>
8	Guh ツ692180947990216725	40 Horas 41 Minutos 16 Segundos		869353363609899042, 869353363635073066, 869353363635073070, 869353363647651889, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353363844780126, 869353764727947324, 892270088286273586, 901201926312566845, 934915112270188555, 935385249389608991, 989523321014079488, 992073031348400138, 869353363609899038		|||	40	|||		692180947990216725		<@692180947990216725>	40h	Guarda Marinha	Mantem	<@&869353363815411754>				869353363815411752	<@&869353363815411752>	869353363815411752	<@692180947990216725>	<@&869353363815411754>
9	° Pit do RJ 🤑🥵885662280036016248	40 Horas 21 Minutos 40 Segundos		869353363609899040, 869353363635073071, 869353363656020031, 869353363668598798, 869353363668598799, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411753, 879479406039531562, 892270088286273586, 901201962765279232, 963086806784565328, 973715650528833598, 993269461572395088, 869353363609899038		|||	40	|||		885662280036016248		<@885662280036016248>	40h	Soldado	Mantem	<@&869353363815411753>, <@&973715650528833598>				869353363815411753	<@&869353363815411753>	869353363815411753	<@885662280036016248>	<@&869353363815411753>	<@&973715650528833598>
10	! 𝙰𝚕𝚒𝚌𝚢'曉 mv931644263199023134	40 Horas 8 Minutos 8 Segundos		869353363635073073, 869353363647651889, 869353363656020029, 869353363656020031, 869353363656020034, 869353363668598798, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020216, 869353363807010852, 869353363815411754, 869353363832180805, 879479435332579329, 892270088286273586, 923768995981697114, 950958148557090836, 978155147928674324, 993269461572395088, 869353363609899038		|||	40	|||		931644263199023134		<@931644263199023134>	40h	Comandante de exercito	Mantem	<@&869353363815411754>				973715650528833598	<@&973715650528833598>	973715650528833598	<@931644263199023134>	<@&869353363815411754>
11	' Sebastian ☁675931948991905804	40 Horas 2 Minutos 56 Segundos		869353363609899042, 869353363635073066, 869353363647651880, 869353363677020210, 869353363677020214, 869353363807010852, 869353363815411754, 869353363815411757, 869353363844780126, 869353363874131972, 869353363886710784, 869353764727947324, 892270088286273586, 941733727610998804, 970862133929730138, 973019878900531271, 978155147928674324, 978779998502670416, 993269461572395088, 869353363609899038		|||	40	|||		675931948991905804		<@675931948991905804>	40h	Tenente Brigadeiro	Mantem	<@&869353363815411757>, <@&869353363815411754>				869353363807010855	<@&869353363807010855>	869353363807010855	<@675931948991905804>	<@&869353363815411757>	<@&869353363815411754>
12	MGL229456993373519872	39 Horas 48 Minutos 45 Segundos		869353363609899042, 869353363622494315, 869353363635073066, 869353363635073071, 869353363647651886, 869353363647651889, 869353363656020030, 869353363656020033, 869353363656020035, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363677020219, 869353363714744337, 869353363794444395, 869353363807010852, 869353363815411754, 869353363815411755, 869353363832180799, 869353363844780126, 869353363861536791, 869353764727947324, 879479435332579329, 892270088286273586, 896811812132110336, 917381741084295198, 923768995981697114, 934915112270188555, 941733727610998804, 963086806784565328, 970862133929730138, 987429669550850118, 989523321014079488, 991499849826578502, 992073031348400138, 869353363609899038		|||	39	|||		229456993373519872		<@229456993373519872>	39h	General de Divisao	Mantem	<@&869353363815411754>, <@&869353363815411755>, <@&896811812132110336>				869353363807010854	<@&869353363807010854>	869353363807010854	<@229456993373519872>	<@&869353363815411754>	<@&869353363815411755>	<@&896811812132110336>
13	Mayli ❤972926068732354581	39 Horas 30 Minutos 19 Segundos		869353363609899040, 869353363635073073, 869353363668598798, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020217, 879479406039531562, 892270088286273586, 945790336070983750, 970862133929730138, 989523321014079488, 992073031348400138, 869353363609899038		|||	39	|||		972926068732354581		<@972926068732354581>	39h	Não é mais staff.	#N/D	Não tem tag de equipe				896811812132110336	<@&896811812132110336>	896811812132110336	<@972926068732354581>	Não tem tag de equipe
14	! HUNTER370612816291561474	39 Horas 18 Minutos 42 Segundos		869353363647651886, 869353363656020032, 869353363656020033, 869353363668598797, 869353363668598798, 869353363668598802, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020215, 869353363714744338, 869353363807010852, 869353363815411754, 869353363844780126, 879479412733665312, 892270088286273586, 901201926312566845, 923768995981697114, 933510165607632906, 963086806784565328, 970862133929730138, 988969269536649246, 989523321014079488, 992073031348400138, 993269461572395088, 869353363609899038		|||	39	|||		370612816291561474		<@370612816291561474>	39h	Guarda Marinha	Mantem	<@&869353363815411754>				962805646099640340	<@&962805646099640340>	962805646099640340	<@370612816291561474>	<@&869353363815411754>
15	! yDinoZNT937407324857966624	39 Horas 18 Minutos 16 Segundos		869353363635073073, 869353363668598798, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353363823816715, 869353363832180806, 869353363844780126, 879479412733665312, 892270088286273586, 970862133929730138, 983118451012927518, 988969269536649246, 989523321014079488, 993269461572395088, 869353363609899038		|||	39	|||		937407324857966624		<@937407324857966624>	39h	Capitao	Mantem	<@&869353363815411754>				993257880927076392	<@&993257880927076392>	993257880927076392	<@937407324857966624>	<@&869353363815411754>
Planilha1

Cell Formulas
Range	Formula
L3:L15	L3	=CONCAT("<@",J3,">")
M3:M15	M3	=CONCAT(G3,"h")
N3:N15	N3	=IFERROR(@INDEX(AP$3:AP$36,AGGREGATE(15,6,(ROW(AP$3:AP$36)-ROW(AP$3)+1)/ISNUMBER(SEARCH(AQ$3:AQ$36,D3)),1)),"Não é mais staff.")
O3:O15	O3	=IF(INDEX(AO$3:AO$36,MATCH(N3,AP$3:AP$36,0))<=M3,"Mantem","Rebaixar")
P3:P15	P3	=aconcat(X3:AL3,", ")
R3	R3	=IFERROR(INDEX($V$3:$V$15,AGGREGATE(15,6,(ROW($V$3:$V$15)-ROW($V$3)+1)/ISNUMBER(SEARCH(LEFT($W$3:$W$15,15),$D3)),IF(COLUMNS($X3:X3)<=SUM(--ISNUMBER(SEARCH(LEFT($W$3:$W$15,15),$D3))),COLUMNS($X3:X3),""))),"")
V3:V15	V3	=RIGHT(T3,18)
W3:W15	W3	=L3
X3:X15	X3	=IFERROR(INDEX($U$3:$U$15,AGGREGATE(15,6,(ROW($U$3:$U$15)-ROW($U$3)+1)/ISNUMBER(SEARCH(LEFT($V$3:$V$15,18),$D3)),IF(COLUMNS($X3:X3)<=SUM(--ISNUMBER(SEARCH(LEFT($V$3:$V$15,15),$D3))),COLUMNS($X3:X3),""))),"Não tem tag de equipe")
Y3:AF4,Y5:AE15,AG3:AL15	Y3	=IFERROR(INDEX($U$3:$U$15,AGGREGATE(15,6,(ROW($U$3:$U$15)-ROW($U$3)+1)/ISNUMBER(SEARCH(LEFT($V$3:$V$15,18),$D3)),IF(COLUMNS($X3:Y3)<=SUM(--ISNUMBER(SEARCH(LEFT($V$3:$V$15,15),$D3))),COLUMNS($X3:Y3),""))),"")
G3:G15	G3	=--SUBSTITUTE(LEFT(B3,3),"H","")
J3:J15	J3	=RIGHT(A3,18)

 

[KRice]

Thank you…that explains the issue. Your actual lookup table is different from what was posted. None of the ID’s in columns T and V end in 000. I’ll look at this when I get back to my computer. I appreciate the clarification.

 

[lu1zuk]

Thank you…that explains the issue. Your actual lookup table is different from what was posted. None of the ID’s in columns T and V end in 000. I’ll look at this when I get back to my computer. I appreciate the clarification.

If with lookup table you mean D3 (CargosColumn) then this one is different from the previous one because I was testing with different people (people are composed of User, Time and Positions) but if lookup table you mean where is the Equipe-ID (V-D) (I just changed the location of the cells because it was finished and it's the way I want it) but to help you with more clarification I'm explaining better / and asking too,
Regarding the change in that part V(Equipe), I just changed a "detail" because it has to do with the purpose of the spreadsheet.

I use discord, in discord the tags have id, and with id you can mention a tag, so I just changed it to the 18-character set by adding "<@&" and ">" so it will mention the tag from team automatically when I copy paste from excel to discord chat

 

[KRice]

When I referred to "lookup table" I meant the common table you look at...in this latest example, that is in columns T:V. But what I said about that is not quite correct. Your posts showed the lookup values correctly, but when I copied the mini-sheet created by XL2BB and pasted it into my worksheet, Excel automatically represented those 18-character text strings (consisting only of numerals) as numbers, and in doing so, Excel dropped the last three characters, replacing them with 000...and I did not notice the change. You were correct about the rounding...for very large numbers, Excel limits their representation to no more than 15 digits, so the last 3 critical characters are lost. I'm sorry for the confusion.

Are you provided the Equipe tag numbers in this format?... <@&869353363815411757>
If so, I would consider avoiding the intermediate number/text string manipulations in columns T and V. Doing so would eliminate redundancy in the table. You can extract the 18-digit text from this tag expression inside the AGGREGATE function...and then you don't have to worry about Excel's treatment of the number. That is a more compelling reason to simply leave the text string (for example, <@&869353363815411757>) in text format. This formula shows how that is done using the MID function and then eliminating the need for the T and V columns.

Now I understand why you changed the 15 to 18, but do it in both places shown in red here:
=IFERROR(INDEX($U$3:$U$15,AGGREGATE(15,6,(ROW($U$3:$U$15)-ROW($U$3)+1)/ISNUMBER(SEARCH(MID($U$3:$U$15,4,18),$D3)),IF(COLUMNS($X3:X3)<=SUM(--ISNUMBER(SEARCH(MID($U$3:$U$15,4,18),$D3))),COLUMNS($X3:X3),""))),"")
...this is necessary so that you will account for all of the matches.

I would recommend leaving the tag-matching formula in column X and beyond with the IFERROR returning a blank rather than the text "Não tem tag de equipe". These are helper columns that you do not regularly want to see, so the message isn't meaningful here. Additionally, the aconcat function used to concatenate all of the helper columns on a row is adjusted to ignore blank cells...and if you place a message in those cells, you will get a long repeating list of the same message produced by aconcat. Instead, I suggest moving the "Não tem tag de equipe" message to the cell where aconcat is found, and if no matches are found, the message will be delivered:

=IF(AND(X3:AL3=""),"Não tem tag de equipe",aconcat(X3:AL3,", "))

You could also reduce redundancy by doing the formula for columns G and M (Horas) in one step, unless there is some reason that the intermediate step is needed.

MrExcel_20220704f.xlsm
	A	B	C	D	E	F	G	H				L	M	N	O	P				T	U	V	W	X
1
2	Usuário	Tempo		Cargos		Separador	Horas	Separador				Ids	Horas	Cargo	Situaçao	Qual a Equipe?					Equipe		Ids checagem dupla	Tags Equipe
3	oyuric755287807337169017	49 Horas 40 Minutos 18 Segundos		869353363609899040, 869353363609899042, 869353363635073066, 869353363635073072, 869353363668598798, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363807010855, 869353363815411753, 869353363823816722, 869353363861536791, 869353764727947324, 879479406039531562, 892270088286273586, 934915112270188555, 970862133929730138, 978155147928674324, 978779998502670416, 993257880927076392, 869353363609899038		|||	49	|||				<@755287807337169017>	49h	0	#N/A	<@&869353363815411753>, <@&869353363807010855>, <@&993257880927076392>					<@&869353363815411757>		<@755287807337169017>	<@&869353363815411753>
4	lais bianchessi960299650810794004	47 Horas 46 Minutos 14 Segundos		869353363609899042, 869353363635073066, 869353363635073072, 869353363647651883, 869353363647651889, 869353363656020029, 869353363656020030, 869353363656020032, 869353363668598798, 869353363668598799, 869353363668598801, 869353363668598804, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353764727947324, 892270088286273586, 901201955903406171, 941733727610998804, 988969269536649246, 993269461572395088, 869353363609899038		|||	47	|||				<@960299650810794004>	47h	0	#N/A	<@&869353363815411754>					<@&869353363815411758>		<@960299650810794004>	<@&869353363815411754>
5	! 𝘽𝙚𝙯𝙚𝙧𝙧𝙤👑906573722838761562	45 Horas 19 Minutos 39 Segundos		869353363609899042, 869353363635073066, 869353363656020031, 869353363656020035, 869353363656020037, 869353363668598797, 869353363668598798, 869353363668598799, 869353363668598802, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363794444395, 869353363807010852, 869353363815411754, 869353363815411759, 869353363832180800, 869353363844780126, 869353764727947324, 879479406039531562, 892270088286273586, 901201936555069510, 933510165607632906, 934915112270188555, 940077814516953088, 941733727610998804, 955288897632485427, 963086806784565328, 973715650528833598, 989523321014079488, 993269461572395088, 869353363609899038		|||	45	|||				<@906573722838761562>	45h	0	#N/A	<@&869353363815411754>, <@&973715650528833598>					<@&869353363815411754>		<@906573722838761562>	<@&869353363815411754>
6	H A S H I R A M A ⛩584794668227887104	42 Horas 41 Minutos 39 Segundos		869353363677020214, 869353363714744338, 869353363807010852, 869353363815411756, 869353363832180800, 869353363861536798, 869353363886710784, 892270088286273586, 901201918481821697, 934915112270188555, 941733727610998804, 970207907369078784, 981030539815452672, 983104269186134056, 989523321014079488, 992073031348400138, 993269461572395088, 869353363609899038		|||	42	|||				<@584794668227887104>	42h	0	#N/A	<@&869353363815411756>					<@&869353363815411756>		<@584794668227887104>	<@&869353363815411756>
7	Sr. Zerefinho668600077060931608	40 Horas 58 Minutos 17 Segundos		869353363635073068, 869353363635073072, 869353363668598798, 869353363668598799, 869353363668598802, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020215, 869353363714744337, 869353363731533902, 869353363807010852, 869353363815411757, 869353363886710791, 879479435332579329, 890717397156266045, 892270088286273586, 896811812132110336, 906299427193499649, 945790336070983750, 970207907369078784, 973716553935769620, 980093439502864425, 989523321014079488, 993257880927076392, 869353363609899038		|||	40	|||				<@668600077060931608>	40h	0	#N/A	<@&869353363815411757>, <@&896811812132110336>, <@&993257880927076392>					<@&869353363815411755>		<@668600077060931608>	<@&869353363815411757>
8	Guh ツ692180947990216725	40 Horas 41 Minutos 16 Segundos		869353363609899042, 869353363635073066, 869353363635073070, 869353363647651889, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353363844780126, 869353764727947324, 892270088286273586, 901201926312566845, 934915112270188555, 935385249389608991, 989523321014079488, 992073031348400138, 869353363609899038		|||	40	|||				<@692180947990216725>	40h	0	#N/A	<@&869353363815411754>					<@&869353363815411752>		<@692180947990216725>	<@&869353363815411754>
9	° Pit do RJ 🤑🥵885662280036016248	40 Horas 21 Minutos 40 Segundos		869353363609899040, 869353363635073071, 869353363656020031, 869353363668598798, 869353363668598799, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411753, 879479406039531562, 892270088286273586, 901201962765279232, 963086806784565328, 973715650528833598, 993269461572395088, 869353363609899038		|||	40	|||				<@885662280036016248>	40h	0	#N/A	<@&869353363815411753>, <@&973715650528833598>					<@&869353363815411753>		<@885662280036016248>	<@&869353363815411753>
10	! 𝙰𝚕𝚒𝚌𝚢'曉 mv931644263199023134	40 Horas 8 Minutos 8 Segundos		869353363635073073, 869353363647651889, 869353363656020029, 869353363656020031, 869353363656020034, 869353363668598798, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020216, 869353363807010852, 869353363815411754, 869353363832180805, 879479435332579329, 892270088286273586, 923768995981697114, 950958148557090836, 978155147928674324, 993269461572395088, 869353363609899038		|||	40	|||				<@931644263199023134>	40h	0	#N/A	<@&869353363815411754>					<@&973715650528833598>		<@931644263199023134>	<@&869353363815411754>
11	' Sebastian ☁675931948991905804	40 Horas 2 Minutos 56 Segundos		869353363609899042, 869353363635073066, 869353363647651880, 869353363677020210, 869353363677020214, 869353363807010852, 869353363815411754, 869353363815411757, 869353363844780126, 869353363874131972, 869353363886710784, 869353764727947324, 892270088286273586, 941733727610998804, 970862133929730138, 973019878900531271, 978155147928674324, 978779998502670416, 993269461572395088, 869353363609899038		|||	40	|||				<@675931948991905804>	40h	0	#N/A	<@&869353363815411757>, <@&869353363815411754>					<@&869353363807010855>		<@675931948991905804>	<@&869353363815411757>
12	MGL229456993373519872	39 Horas 48 Minutos 45 Segundos		869353363609899042, 869353363622494315, 869353363635073066, 869353363635073071, 869353363647651886, 869353363647651889, 869353363656020030, 869353363656020033, 869353363656020035, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363677020219, 869353363714744337, 869353363794444395, 869353363807010852, 869353363815411754, 869353363815411755, 869353363832180799, 869353363844780126, 869353363861536791, 869353764727947324, 879479435332579329, 892270088286273586, 896811812132110336, 917381741084295198, 923768995981697114, 934915112270188555, 941733727610998804, 963086806784565328, 970862133929730138, 987429669550850118, 989523321014079488, 991499849826578502, 992073031348400138, 869353363609899038		|||	39	|||				<@229456993373519872>	39h	0	#N/A	<@&869353363815411754>, <@&869353363815411755>, <@&896811812132110336>					<@&869353363807010854>		<@229456993373519872>	<@&869353363815411754>
13	Mayli ❤972926068732354581	39 Horas 30 Minutos 19 Segundos		869353363609899040, 869353363635073073, 869353363668598798, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020214, 869353363677020217, 879479406039531562, 892270088286273586, 945790336070983750, 970862133929730138, 989523321014079488, 992073031348400138, 869353363609899038		|||	39	|||				<@972926068732354581>	39h	0	#N/A	Não tem tag de equipe					<@&896811812132110336>		<@972926068732354581>
14	! HUNTER370612816291561474	39 Horas 18 Minutos 42 Segundos		869353363647651886, 869353363656020032, 869353363656020033, 869353363668598797, 869353363668598798, 869353363668598802, 869353363668598804, 869353363677020212, 869353363677020214, 869353363677020215, 869353363714744338, 869353363807010852, 869353363815411754, 869353363844780126, 879479412733665312, 892270088286273586, 901201926312566845, 923768995981697114, 933510165607632906, 963086806784565328, 970862133929730138, 988969269536649246, 989523321014079488, 992073031348400138, 993269461572395088, 869353363609899038		|||	39	|||				<@370612816291561474>	39h	0	#N/A	<@&869353363815411754>					<@&962805646099640340>		<@370612816291561474>	<@&869353363815411754>
15	! yDinoZNT937407324857966624	39 Horas 18 Minutos 16 Segundos		869353363635073073, 869353363668598798, 869353363668598799, 869353363668598801, 869353363677020210, 869353363677020212, 869353363677020215, 869353363807010852, 869353363815411754, 869353363823816715, 869353363832180806, 869353363844780126, 879479412733665312, 892270088286273586, 970862133929730138, 983118451012927518, 988969269536649246, 989523321014079488, 993269461572395088, 869353363609899038		|||	39	|||				<@937407324857966624>	39h	0	#N/A	<@&869353363815411754>					<@&993257880927076392>		<@937407324857966624>	<@&869353363815411754>
Planilha1

Cell Formulas
Range	Formula
L3:L15	L3	=CONCAT("<@",RIGHT(A3,18),">")
M3:M15	M3	=CONCAT(G3,"h")
N3:N15	N3	=IFERROR(@INDEX(AP$3:AP$36,AGGREGATE(15,6,(ROW(AP$3:AP$36)-ROW(AP$3)+1)/ISNUMBER(SEARCH(AQ$3:AQ$36,D3)),1)),"Não é mais staff.")
O3:O15	O3	=IF(INDEX(AO$3:AO$36,MATCH(N3,AP$3:AP$36,0))<=M3,"Mantem","Rebaixar")
P3:P15	P3	=IF(AND(X3:AL3=""),"Não tem tag de equipe",aconcat(X3:AL3,", "))
W3:W15	W3	=L3
X3:X15	X3	=IFERROR(INDEX($U$3:$U$15,AGGREGATE(15,6,(ROW($U$3:$U$15)-ROW($U$3)+1)/ISNUMBER(SEARCH(MID($U$3:$U$15,4,18),$D3)),IF(COLUMNS($X3:X3)<=SUM(--ISNUMBER(SEARCH(MID($U$3:$U$15,4,18),$D3))),COLUMNS($X3:X3),""))),"")
G3:G15	G3	=--SUBSTITUTE(LEFT(B3,3),"H","")

 

[lu1zuk]

Are you provided the Equipe tag numbers in this format?... <@&869353363815411757>
If so, I would consider avoiding the intermediate number/text string manipulations in columns T and V. Doing so would eliminate redundancy in the table. You can extract the 18-digit text from this tag expression inside the AGGREGATE function...and then you don't have to worry about Excel's treatment of the number. That is a more compelling reason to simply leave the text string (for example, <@&869353363815411757>) in text format. This formula shows how that is done using the MID function and then eliminating the need for the T and V columns.

In fact I can get the id of the position (pair of 18 numbers) and for me to make this id be transformed into a tag, I prefer to format it like this <@&id> so that it is tagged in the chat, if I send it only the 18 numbers, there are only 18 numbers, but if I send with the formation <@&869353363815411757> I will show an example:

And yes i did the MID formula, thanks about it, yes it saves fully a column hehe

Sobe_e_Desce_2.1.xlsm
	U	V
2	Equipe	ID
3	<@&869353363815411757>	869353363815411757
4	<@&869353363815411758>	869353363815411758
5	<@&869353363815411754>	869353363815411754
6	<@&869353363815411756>	869353363815411756
7	<@&869353363815411755>	869353363815411755
8	<@&869353363815411752>	869353363815411752
9	<@&869353363815411753>	869353363815411753
10	<@&973715650528833598>	973715650528833598
11	<@&869353363807010855>	869353363807010855
12	<@&869353363807010854>	869353363807010854
13	<@&896811812132110336>	896811812132110336
14	<@&962805646099640340>	962805646099640340
15	<@&993257880927076392>	993257880927076392
Planilha1

Cell Formulas
Range	Formula
V3:V15	V3	=MID(U3,4,18)

Now I understand why you changed the 15 to 18, but do it in both places shown in red here:
=IFERROR(INDEX($U$3:$U$15,AGGREGATE(15,6,(ROW($U$3:$U$15)-ROW($U$3)+1)/ISNUMBER(SEARCH(MID($U$3:$U$15,4,18),$D3)),IF(COLUMNS($X3:X3)<=SUM(--ISNUMBER(SEARCH(MID($U$3:$U$15,4,18),$D3))),COLUMNS($X3:X3),""))),"")
...this is necessary so that you will account for all of the matches.

Changed it in here, thanks again!

I would recommend leaving the tag-matching formula in column X and beyond with the IFERROR returning a blank rather than the text "Não tem tag de equipe". These are helper columns that you do not regularly want to see, so the message isn't meaningful here. Additionally, the aconcat function used to concatenate all of the helper columns on a row is adjusted to ignore blank cells...and if you place a message in those cells, you will get a long repeating list of the same message produced by aconcat. Instead, I suggest moving the "Não tem tag de equipe" message to the cell where aconcat is found, and if no matches are found, the message will be delivered:

Oh i see, changed it too, thanks!

You could also reduce redundancy by doing the formula for columns G and M (Horas) in one step, unless there is some reason that the intermediate step is needed.

at first I didn't understand it very well but then I realized that you meant that you can use concat together in G, and that's what I did and it worked! Thanks!

Sobe_e_Desce_2.1.xlsm
	G
3	49h
4	47h
5	45h
Planilha1

Cell Formulas
Range	Formula
G3:G5	G3	=CONCAT(--SUBSTITUTE(LEFT(B3,3),"H",""),"h")

Now that I believe we've sorted out the main reason for all of this, and I think we've cleared it all up, could you help me with another detail that came up?
In the last line of column O it's giving #N/D and I imagine it's because it doesn't have a parameter for "no longer staff" I tried to put a parameter, I tried to change it directly in the if but I'm kinda lost, I just want it instead of #N/D that the message appears again "no longer staff"

Sobe_e_Desce_2.1.xlsm
	L	M	N	O	P
2	Ids	Horas	Cargo	Situaçao	Qual a Equipe?
3	<@755287807337169017>	49h	Coronel	Mantem	<@&869353363815411753>, <@&869353363807010855>, <@&993257880927076392>
4	<@960299650810794004>	47h	Cabo	Mantem	<@&869353363815411754>
5	<@906573722838761562>	45h	Sub Tenente	Mantem	<@&869353363815411754>, <@&973715650528833598>
6	<@584794668227887104>	42h	Major Brigadeiro	Mantem	<@&869353363815411756>
7	<@668600077060931608>	40h	Hokage Ultimate	Mantem	<@&869353363815411757>, <@&896811812132110336>, <@&993257880927076392>
8	<@692180947990216725>	40h	Guarda Marinha	Mantem	<@&869353363815411754>
9	<@885662280036016248>	40h	Soldado	Mantem	<@&869353363815411753>, <@&973715650528833598>
10	<@931644263199023134>	40h	Comandante de exercito	Mantem	<@&869353363815411754>
11	<@675931948991905804>	40h	Tenente Brigadeiro	Mantem	<@&869353363815411757>, <@&869353363815411754>
12	<@229456993373519872>	39h	General de Divisao	Mantem	<@&869353363815411754>, <@&869353363815411755>, <@&896811812132110336>
13	<@972926068732354581>	39h	Não é mais staff.	#N/D	Não tem tag de equipe
Planilha1

Cell Formulas
Range	Formula
L3:L13	L3	=CONCAT("<@",J3,">")
M3:M13	M3	=G3
N3:N13	N3	=IFERROR(@INDEX(AP$3:AP$36,AGGREGATE(15,6,(ROW(AP$3:AP$36)-ROW(AP$3)+1)/ISNUMBER(SEARCH(AQ$3:AQ$36,D3)),1)),"Não é mais staff.")
O3	O3	=IF(INDEX(AO$3:AO$37,MATCH(N3,AP$3:AP$37,0))<=M3,"Mantem","Rebaixar")
P3:P13	P3	=IF(AND(X3:AL3=""),"Não tem tag de equipe",aconcat(X3:AL3,", "))
O4:O13	O4	=IF(INDEX(AO$3:AO$36,MATCH(N4,AP$3:AP$36,0))<=M4,"Mantem","Rebaixar")

And this below is the lookup table for the O column

Sobe_e_Desce_2.1.xlsm
	AO	AP
3	30	Aspirante
4	30	2 Tenente
5	30	1 Tenente
6	30	Soldado
7	30	Taifero
8	30	Cabo
9	30	Capitao
10	30	Capitao tenente
11	30	Capitao de Corveta
12	30	Capitao de fragata
13	30	Capitao de Mar e Guerra
14	30	Major
15	30	Sub Tenente
16	30	Tenente Coronel
17	30	Sub Oficial
18	30	Coronel
19	30	Guarda Marinha
20	30	General de Brigada
21	35	General de Divisao
22	35	General de Exercito
23	35	Comandante de exercito
24	35	Contra Almirante
25	35	Tenente Brigadeiro
26	35	Vice Almirante
27	35	Major Brigadeiro
28	35	Almirante de Esquadra
29	35	Fuzileiro Naval
30	35	Almirante
31	35	Sentinela
32	35	Marechal Sub-Dono
33	35	Marechal Dono
34	35	Hokage
35	35	Hokage Supremo
36	35	Hokage Ultimate
Planilha1

Cell Formulas
Range	Formula
AP13:AP14	AP13	=LEFT(AN13,FIND(8,AN13)-2)

 

[KRice]

It sounds as if you have successfully made revisions and almost everything is working as intended. I believe you can even delete column V (ID) since the same formula that produces the V content is already embedded in the AGGREGATE function.

About your other question, the #N/D message is an error code in your native language (which I know as the #N/A error). I suspect you are correct...the MATCH function is not finding an exact match, so it produces this error, and that error carries through to the final output. The easiest solution is to wrap the function with an IFERROR and deliver your preferred message when this scenario occurs:
=IFERROR(IF(INDEX(AO$3:AO$36,MATCH(N4,AP$3:AP$36,0))<=M4,"Mantem","Rebaixar"),"no longer staff")
Let me know if that works.

 

[lu1zuk]

Yes , it worked fine! Thank you!

It sounds as if you have successfully made revisions and almost everything is working as intended. I believe you can even delete column V (ID) since the same formula that produces the V content is already embedded in the AGGREGATE function.

About your other question, the #N/D message is an error code in your native language (which I know as the #N/A error). I suspect you are correct...the MATCH function is not finding an exact match, so it produces this error, and that error carries through to the final output. The easiest solution is to wrap the function with an IFERROR and deliver your preferred message when this scenario occurs:
=IFERROR(IF(INDEX(AO$3:AO$36,MATCH(N4,AP$3:AP$36,0))<=M4,"Mantem","Rebaixar"),"no longer staff")
Let me know if that works.

What should i mark as the ANSWER? lmao, im lost hehe

 

[KRice]

That’s great…thanks for the update. I’m happy to help.