Index match funcion ignoring blank cells

[valmir]

Hi everyone!
I am using these formulas to rank names and the problem I am having is that they are returning blank cells:
=COUNTIF($BZ1:$BZ608;"<="&$BZ1:$BZ608)

=IFERROR(INDEX($BY$1:$CA$608;MATCH(BX1;$CJ$1:$CJ$608;0);1);"")
=IFERROR(INDEX($BZ$1:$BZ$608;MATCH(BX1;$CJ$1:$CJ$608;0);2);"")
=IFERROR(INDEX($BY$1:$BY$608;MATCH(BX1;$CJ$1:$CJ$608;0);3);"")

I have gone through all similar threads and I could not get anything to work
Please help!

Stats 2016.xlsx
	CK
38
STATS2

 

[jasonb75]

As you have office 365 you could probably make use of the SORT function here. You forgot to select your data range in your mini-sheet so we can't actually see anything to advise exactly what is needed.

 

[StephenCrump]

The second IFERROR() formula will error, and therefore return blank, because you are indexing column 2 of a single column (BZ)
Similarly, the third IFERROR() formula will error, and therefore return blank, because you are indexing column 3 of a single column (BY).

The first IFERROR may be erroring because the MATCH is failing. (What does =MATCH(BX1;$CJ$1:$CJ$608;0) return?)
Or the MATCH may be working, but the corresponding value in column BY could be blank. We can't tell without seeing your data.

Where does the COUNTIF fit in?

 

[valmir]

As you have office 365 you could probably make use of the SORT function here. You forgot to select your data range in your mini-sheet so we can't actually see anything to advise exactly what is needed.

Stats 2016.xlsx
	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ
1	1	4AB	Borra			3	3			PET	Abdul	António Nzayinawo	61
2	2	4AB	Cadiata			21				MAI	Abegá	Manuel Pedro Pacavira	77
3	3	4AB	Campos	António Campos Calei	1	24		72		PLS	Abel	Abel Lukango	80
4	4	4AB	Chara ?			2	1			INT	Abianda 1	Yann Junior Abianda 1	98
5	5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	6	26	1	8		KAB	Abianda 2	Yann Junior Abianda 2	123
6	6	4AB	Dário Amorim	Dário Félix Amorim			3			LIB	Adilson Kivão	Adilson Ernesto Kivão	126
7	7	4AB	Dudú Jack	Domingos Monteiro Jack	1	8	4	73		CAA	Adilson Manuel	Adilson Joaquim Manuel	157
8	8	4AB	Edgard	Edgard Arnaud Afane		18				ACA	Adó Pena	António Joaquim Barros Pena	160
9	9	4AB	Edi			1				ASA	Ady ?		161
10	10	4AB	Geúda	Gelson L. da S. Teles		8	7			BEN	Ady Paulo	Agostinho Domingos Paulo	216
11	11	4AB	Gui	Manuel Porfírio Pompílio de Matos		6				PLS	Agoya	Augusto Makanja	224
12	12	4AB	Hippi	Domingos Paulo João José	1	15	1	74		ACA	Aires	Ludgero Aires Cachicote da Rocha	231
13	13	4AB	Ivanilson				5			MAI	Alex	Alexandre G. Mário Miguel	238
14	14	4AB	Jairzinho	Jair Sebastião de Castro		8	5			POR	Alexandre	Alexandre Domingos Cristóvão M'Futila	243
15	15	4AB	Jesse				3			DES	Ali	Abengea Mambani Ali	247
16	16	4AB	Kandú	Déo Kanda	3	14	9	26		PRO	Almeida	José Xavier Fernando	268
17	17	4AB	Kizombé	Alberto dos Santos Domingos		16	1			KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1	279
18	18	4AB	Lara ?				1			ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2	286
19	19	4AB	Lelo	José Lelo Sevo Barros	1	14		75		BEN	Amaro	Amândio Manuel Filipe da Costa	294
20	20	4AB	Manucho Muquengue	Mariano Simão Muquengue	2	15	11	46		BEN	Amido Baldé	Amido Baldé	329
21	21	4AB	Matamba	Matamba Paulino Sousa	1	27		76		PRO	Anastácio 1	Anastácio Manuel da Costa 1	338
22	22	4AB	Paito	Manuel Gaspar Fernandes		1	5			ASA	Anastácio 2	Anastácio Manuel da Costa 2	398
23	23	4AB	Palucho	Paulo Pereira da Silva	1	6	13	77		BEN	Anderson		402
24	24	4AB	Pilola	José Olívio Andrade Pereira	3	11		27		INT	António	Tomás Chilombo Cambuque Sapalo	418
25	25	4AB	Prazeres	Prazeres Chijica M. Dala		3	1			SAG	Ary Calvete	Helmut Ariadne Figueiredo Calvete	423
26	26	4AB	Rochana	Panda Marcelo José		23				PET	Ary Oliveira	Ariclene Assunção Oliveira	432
27	27	4AB	Rui ?			8	2			PRI	Ary Papel	Manuel David Afonso	437
28	28	4AB	Tony Augusto	António Pedro Augusto		7				PLS	Atunako	Eboue Manzambi Atunako	461
29	29	4AB	Vander	Vander Ramos Pinto		19				PRO	Avex 1	Avelino Eduardo António Craque 1	479
30	30	4AB	Xavier	Xavier Eduardo Vicente Vunge	1	23		78		DES	Avex 2	Avelino Eduardo António Craque 2	489
31	31	4AB								SAG	Ayala	Mário Álvaro Agostinho	107
32	32	4AB								PET	Azulão	Tiago Lima Leal	107
33	33	4AB								ASA	Babacar	Babacar Fall	107
34	34	4AB								BEN	Baby	Valdemar Denso António	107
35	35	4AB								PLS	Badrick	Badrick Paulo Madadi	107
36	36	4AB								PET	Balacai	Evaristo Maurício Pascoal	107
37	37	4AB								PLS	Barrezó	Joveth Adão	107
38	38	4AB								INT	Bartolo	Bartolomeu Nguia Baptista	107
39	39	ACA	Adó Pena	António Joaquim Barros Pena	1	13	2	79		INT	Bebé 1	Odimir Abreu Gabriel Breganha 1	8
40	40	ACA	Aires	Ludgero Aires Cachicote da Rocha		1				ASA	Bebé 2	Odimir Abreu Gabriel Breganha 2	12
41	41	ACA	Borges 1	Cláudio Ricardo Cunha Borges 1	1	8	1	80		PET	Bebo	Manuel João Miguel da Costa	59
42	42	ACA	Cabibi Abreu 1	Mário Rui de Abreu 1			1			DES	Bebucho	Márcio Sakuala Ramalho Henriques	73
43	43	ACA	Camota	Júlio Camota Marcelino Tito		2	1			PRO	Belito ?		79
44	44	ACA	Chabalala	Gaspar Necas Fortunato	3	4	6	28		DES	Belito Socola	Abelardo Gomes Socola	97
45	45	ACA	Chiló	Francisco Ananias Orlando		11	5			BEN	Bena	Diveluca Simão Nascimento	105
46	46	ACA	Cláudio	Cláudio Américo Loi Sozinho	1	18	2	81		SAG	Beni	Beni Kimbi Fua	114
47	47	ACA	Day Day	Zaldivar Doval Augusto Cambinda			1			PRO	Benvindo Garcia	Benvindo Regresso Pontes Garcia	133
48	48	ACA	Djo	Martins Geovety P. Gervásio		8				PET	Benvindo Nsianfumu	Benvindo Afonso Nsianfumu	150
49	49	ACA	Elton	Elton Ernesto de Carvalho			4			CAA	Beny	Teodoro Edvaldo Rita Tchissingui	171
50	50	ACA	Fani	José Afonso dos Santos Fernando		21				MAI	Bernardo	Patrick Kifu Apataki	182
STATS2

Cell Formulas
Range	Formula
BZ1	BZ1	=INDIRECT("4AB!B5")
CA1	CA1	=INDIRECT("4AB!C5")
CB1	CB1	=INDIRECT("4AB!E5")
CC1	CC1	=INDIRECT("4AB!F5")
CD1	CD1	=INDIRECT("4AB!G5")
CE1:CE50	CE1	=IF($CB1=0,"",IF($CB1>0,RANK(CB1,$CB$1:$CB$608)+COUNTIF($CB$1:CB1,CB1)-1))
BZ2	BZ2	=INDIRECT("4AB!B6")
CA2	CA2	=INDIRECT("4AB!C6")
CB2	CB2	=INDIRECT("4AB!E6")
CC2	CC2	=INDIRECT("4AB!F6")
CD2	CD2	=INDIRECT("4AB!G6")
BZ3	BZ3	=INDIRECT("4AB!B7")
CA3	CA3	=INDIRECT("4AB!C7")
CB3	CB3	=INDIRECT("4AB!E7")
CC3	CC3	=INDIRECT("4AB!F7")
CD3	CD3	=INDIRECT("4AB!G7")
BZ4	BZ4	=INDIRECT("4AB!B8")
CA4	CA4	=INDIRECT("4AB!C8")
CB4	CB4	=INDIRECT("4AB!E8")
CC4	CC4	=INDIRECT("4AB!F8")
CD4	CD4	=INDIRECT("4AB!G8")
BZ5	BZ5	=INDIRECT("4AB!B9")
CA5	CA5	=INDIRECT("4AB!C9")
CB5	CB5	=INDIRECT("4AB!E9")
CC5	CC5	=INDIRECT("4AB!F9")
CD5	CD5	=INDIRECT("4AB!G9")
BZ6	BZ6	=INDIRECT("4AB!B10")
CA6	CA6	=INDIRECT("4AB!C10")
CB6	CB6	=INDIRECT("4AB!E10")
CC6	CC6	=INDIRECT("4AB!F10")
CD6	CD6	=INDIRECT("4AB!G10")
BZ7	BZ7	=INDIRECT("4AB!B11")
CA7	CA7	=INDIRECT("4AB!C11")
CB7	CB7	=INDIRECT("4AB!E11")
CC7	CC7	=INDIRECT("4AB!F11")
CD7	CD7	=INDIRECT("4AB!G11")
BZ8	BZ8	=INDIRECT("4AB!B12")
CA8	CA8	=INDIRECT("4AB!C12")
CB8	CB8	=INDIRECT("4AB!E12")
CC8	CC8	=INDIRECT("4AB!F12")
CD8	CD8	=INDIRECT("4AB!G12")
BZ9	BZ9	=INDIRECT("4AB!B13")
CA9	CA9	=INDIRECT("4AB!C13")
CB9	CB9	=INDIRECT("4AB!E13")
CC9	CC9	=INDIRECT("4AB!F13")
CD9	CD9	=INDIRECT("4AB!G13")
BZ10	BZ10	=INDIRECT("4AB!B14")
CA10	CA10	=INDIRECT("4AB!C14")
CB10	CB10	=INDIRECT("4AB!E14")
CC10	CC10	=INDIRECT("4AB!F14")
CD10	CD10	=INDIRECT("4AB!G14")
BZ11	BZ11	=INDIRECT("4AB!B15")
CA11	CA11	=INDIRECT("4AB!C15")
CB11	CB11	=INDIRECT("4AB!E15")
CC11	CC11	=INDIRECT("4AB!F15")
CD11	CD11	=INDIRECT("4AB!G15")
BZ12	BZ12	=INDIRECT("4AB!B16")
CA12	CA12	=INDIRECT("4AB!C16")
CB12	CB12	=INDIRECT("4AB!E16")
CC12	CC12	=INDIRECT("4AB!F16")
CD12	CD12	=INDIRECT("4AB!G16")
BZ13	BZ13	=INDIRECT("4AB!B17")
CA13	CA13	=INDIRECT("4AB!C17")
CB13	CB13	=INDIRECT("4AB!E17")
CC13	CC13	=INDIRECT("4AB!F17")
CD13	CD13	=INDIRECT("4AB!G17")
BZ14	BZ14	=INDIRECT("4AB!B18")
CA14	CA14	=INDIRECT("4AB!C18")
CB14	CB14	=INDIRECT("4AB!E18")
CC14	CC14	=INDIRECT("4AB!F18")
CD14	CD14	=INDIRECT("4AB!G18")
BZ15	BZ15	=INDIRECT("4AB!B19")
CA15	CA15	=INDIRECT("4AB!C19")
CB15	CB15	=INDIRECT("4AB!E19")
CC15	CC15	=INDIRECT("4AB!F19")
CD15	CD15	=INDIRECT("4AB!G19")
BZ16	BZ16	=INDIRECT("4AB!B20")
CA16	CA16	=INDIRECT("4AB!C20")
CB16	CB16	=INDIRECT("4AB!E20")
CC16	CC16	=INDIRECT("4AB!F20")
CD16	CD16	=INDIRECT("4AB!G20")
BZ17	BZ17	=INDIRECT("4AB!B21")
CA17	CA17	=INDIRECT("4AB!C21")
CB17	CB17	=INDIRECT("4AB!E21")
CC17	CC17	=INDIRECT("4AB!F21")
CD17	CD17	=INDIRECT("4AB!G21")
BZ18	BZ18	=INDIRECT("4AB!B22")
CA18	CA18	=INDIRECT("4AB!C22")
CB18	CB18	=INDIRECT("4AB!E22")
CC18	CC18	=INDIRECT("4AB!F22")
CD18	CD18	=INDIRECT("4AB!G22")
BZ19	BZ19	=INDIRECT("4AB!B23")
CA19	CA19	=INDIRECT("4AB!C23")
CB19	CB19	=INDIRECT("4AB!E23")
CC19	CC19	=INDIRECT("4AB!F23")
CD19	CD19	=INDIRECT("4AB!G23")
BZ20	BZ20	=INDIRECT("4AB!B24")
CA20	CA20	=INDIRECT("4AB!C24")
CB20	CB20	=INDIRECT("4AB!E24")
CC20	CC20	=INDIRECT("4AB!F24")
CD20	CD20	=INDIRECT("4AB!G24")
BZ21	BZ21	=INDIRECT("4AB!B25")
CA21	CA21	=INDIRECT("4AB!C25")
CB21	CB21	=INDIRECT("4AB!E25")
CC21	CC21	=INDIRECT("4AB!F25")
CD21	CD21	=INDIRECT("4AB!G25")
BZ22	BZ22	=INDIRECT("4AB!B26")
CA22	CA22	=INDIRECT("4AB!C26")
CB22	CB22	=INDIRECT("4AB!E26")
CC22	CC22	=INDIRECT("4AB!F26")
CD22	CD22	=INDIRECT("4AB!G26")
BZ23	BZ23	=INDIRECT("4AB!B27")
CA23	CA23	=INDIRECT("4AB!C27")
CB23	CB23	=INDIRECT("4AB!E27")
CC23	CC23	=INDIRECT("4AB!F27")
CD23	CD23	=INDIRECT("4AB!G27")
BZ24	BZ24	=INDIRECT("4AB!B28")
CA24	CA24	=INDIRECT("4AB!C28")
CB24	CB24	=INDIRECT("4AB!E28")
CC24	CC24	=INDIRECT("4AB!F28")
CD24	CD24	=INDIRECT("4AB!G28")
BZ25	BZ25	=INDIRECT("4AB!B29")
CA25	CA25	=INDIRECT("4AB!C29")
CB25	CB25	=INDIRECT("4AB!E29")
CC25	CC25	=INDIRECT("4AB!F29")
CD25	CD25	=INDIRECT("4AB!G29")
BZ26	BZ26	=INDIRECT("4AB!B30")
CA26	CA26	=INDIRECT("4AB!C30")
CB26	CB26	=INDIRECT("4AB!E30")
CC26	CC26	=INDIRECT("4AB!F30")
CD26	CD26	=INDIRECT("4AB!G30")
BZ27	BZ27	=INDIRECT("4AB!B31")
CA27	CA27	=INDIRECT("4AB!C31")
CB27	CB27	=INDIRECT("4AB!E31")
CC27	CC27	=INDIRECT("4AB!F31")
CD27	CD27	=INDIRECT("4AB!G31")
BZ28	BZ28	=INDIRECT("4AB!B32")
CA28	CA28	=INDIRECT("4AB!C32")
CB28	CB28	=INDIRECT("4AB!E32")
CC28	CC28	=INDIRECT("4AB!F32")
CD28	CD28	=INDIRECT("4AB!G32")
BZ29	BZ29	=INDIRECT("4AB!B33")
CA29	CA29	=INDIRECT("4AB!C33")
CB29	CB29	=INDIRECT("4AB!E33")
CC29	CC29	=INDIRECT("4AB!F33")
CD29	CD29	=INDIRECT("4AB!G33")
BZ30	BZ30	=INDIRECT("4AB!B34")
CA30	CA30	=INDIRECT("4AB!C34")
CB30	CB30	=INDIRECT("4AB!E34")
CC30	CC30	=INDIRECT("4AB!F34")
CD30	CD30	=INDIRECT("4AB!G34")
BZ31	BZ31	=INDIRECT("4AB!B35")
CA31	CA31	=INDIRECT("4AB!C35")
CB31	CB31	=INDIRECT("4AB!E35")
CC31	CC31	=INDIRECT("4AB!F35")
CD31	CD31	=INDIRECT("4AB!G35")
BZ32	BZ32	=INDIRECT("4AB!B36")
CA32	CA32	=INDIRECT("4AB!C36")
CB32	CB32	=INDIRECT("4AB!E36")
CC32	CC32	=INDIRECT("4AB!F36")
CD32	CD32	=INDIRECT("4AB!G36")
BZ33	BZ33	=INDIRECT("4AB!B37")
CA33	CA33	=INDIRECT("4AB!C37")
CB33	CB33	=INDIRECT("4AB!E37")
CC33	CC33	=INDIRECT("4AB!F37")
CD33	CD33	=INDIRECT("4AB!G37")
BZ34	BZ34	=INDIRECT("4AB!B38")
CA34	CA34	=INDIRECT("4AB!C38")
CB34	CB34	=INDIRECT("4AB!E38")
CC34	CC34	=INDIRECT("4AB!F38")
CD34	CD34	=INDIRECT("4AB!G38")
BZ35	BZ35	=INDIRECT("4AB!B39")
CA35	CA35	=INDIRECT("4AB!C39")
CB35	CB35	=INDIRECT("4AB!E39")
CC35	CC35	=INDIRECT("4AB!F39")
CD35	CD35	=INDIRECT("4AB!G39")
BZ36	BZ36	=INDIRECT("4AB!B40")
CA36	CA36	=INDIRECT("4AB!C40")
CB36	CB36	=INDIRECT("4AB!E40")
CC36	CC36	=INDIRECT("4AB!F40")
CD36	CD36	=INDIRECT("4AB!G40")
BZ37	BZ37	=INDIRECT("4AB!B41")
CA37	CA37	=INDIRECT("4AB!C41")
CB37	CB37	=INDIRECT("4AB!E41")
CC37	CC37	=INDIRECT("4AB!F41")
CD37	CD37	=INDIRECT("4AB!G41")
BZ38	BZ38	=INDIRECT("4AB!B42")
CA38	CA38	=INDIRECT("4AB!C42")
CB38	CB38	=INDIRECT("4AB!E42")
CC38	CC38	=INDIRECT("4AB!F42")
CD38	CD38	=INDIRECT("4AB!G42")
BZ39	BZ39	=INDIRECT("ACA!B5")
CA39	CA39	=INDIRECT("ACA!C5")
CB39	CB39	=INDIRECT("ACA!E5")
CC39	CC39	=INDIRECT("ACA!F5")
CD39	CD39	=INDIRECT("ACA!G5")
BZ40	BZ40	=INDIRECT("ACA!B6")
CA40	CA40	=INDIRECT("ACA!C6")
CB40	CB40	=INDIRECT("ACA!E6")
CC40	CC40	=INDIRECT("ACA!F6")
CD40	CD40	=INDIRECT("ACA!G6")
BZ41	BZ41	=INDIRECT("ACA!B7")
CA41	CA41	=INDIRECT("ACA!C7")
CB41	CB41	=INDIRECT("ACA!E7")
CC41	CC41	=INDIRECT("ACA!F7")
CD41	CD41	=INDIRECT("ACA!G7")
BZ42	BZ42	=INDIRECT("ACA!B8")
CA42	CA42	=INDIRECT("ACA!C8")
CB42	CB42	=INDIRECT("ACA!E8")
CC42	CC42	=INDIRECT("ACA!F8")
CD42	CD42	=INDIRECT("ACA!G8")
BZ43	BZ43	=INDIRECT("ACA!B9")
CA43	CA43	=INDIRECT("ACA!C9")
CB43	CB43	=INDIRECT("ACA!E9")
CC43	CC43	=INDIRECT("ACA!F9")
CD43	CD43	=INDIRECT("ACA!G9")
BZ44	BZ44	=INDIRECT("ACA!B10")
CA44	CA44	=INDIRECT("ACA!C10")
CB44	CB44	=INDIRECT("ACA!E10")
CC44	CC44	=INDIRECT("ACA!F10")
CD44	CD44	=INDIRECT("ACA!G10")
BZ45	BZ45	=INDIRECT("ACA!B11")
CA45	CA45	=INDIRECT("ACA!C11")
CB45	CB45	=INDIRECT("ACA!E11")
CC45	CC45	=INDIRECT("ACA!F11")
CD45	CD45	=INDIRECT("ACA!G11")
BZ46	BZ46	=INDIRECT("ACA!B12")
CA46	CA46	=INDIRECT("ACA!C12")
CB46	CB46	=INDIRECT("ACA!E12")
CC46	CC46	=INDIRECT("ACA!F12")
CD46	CD46	=INDIRECT("ACA!G12")
BZ47	BZ47	=INDIRECT("ACA!B13")
CA47	CA47	=INDIRECT("ACA!C13")
CB47	CB47	=INDIRECT("ACA!E13")
CC47	CC47	=INDIRECT("ACA!F13")
CD47	CD47	=INDIRECT("ACA!G13")
BZ48	BZ48	=INDIRECT("ACA!B14")
CA48	CA48	=INDIRECT("ACA!C14")
CB48	CB48	=INDIRECT("ACA!E14")
CC48	CC48	=INDIRECT("ACA!F14")
CD48	CD48	=INDIRECT("ACA!G14")
BZ49	BZ49	=INDIRECT("ACA!B15")
CA49	CA49	=INDIRECT("ACA!C15")
CB49	CB49	=INDIRECT("ACA!E15")
CC49	CC49	=INDIRECT("ACA!F15")
CD49	CD49	=INDIRECT("ACA!G15")
BZ50	BZ50	=INDIRECT("ACA!B16")
CA50	CA50	=INDIRECT("ACA!C16")
CB50	CB50	=INDIRECT("ACA!E16")
CC50	CC50	=INDIRECT("ACA!F16")
CD50	CD50	=INDIRECT("ACA!G16")
CG1:CG50	CG1	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX1,$CJ$1:$CJ$608,0),1),"")
CH1:CH50	CH1	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX1,$CJ$1:$CJ$608,0),2),"")
CI1:CI50	CI1	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX1,$CJ$1:$CJ$608,0),3),"")
CJ1:CJ608	CJ1	=COUNTIF($BZ1:$BZ608,"<="&$BZ1:$BZ608)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BY1	Expression	=BZ1=0	text	NO
BX1	Expression	=BZ1=0	text	NO
BZ2:BZ608	Expression	=CA2=0	text	NO
BZ1	Expression	=CA1=0	text	NO
CA1:CA608	Cell Value	=0	text	NO
CI1:CI608	Cell Value	=0	text	NO
CH1:CH608	Expression	=CI1=0	text	NO
CB1:CD608	Cell Value	=0	text	NO

 

[valmir]

As you have office 365 you could probably make use of the SORT function here. You forgot to select your data range in your mini-sheet so we can't actually see anything to advise exactly what is needed.

Sorry my first time with xl2bb. I have selected first 50 rows only because there is a limit of 3000 cells

 

[jasonb75]

Using a smaller example (10-20 rows is adequate, we don't need everything), is this what you are trying to do?

sumif weeknum (version 1).xlsb
	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI
1	1	4AB	Borra			3	3			4AB	Campos	António Campos Calei
2	2	4AB	Cadiata			21				4AB	Dany Ribeiro	Daniel Mendes Ribeiro
3	3	4AB	Campos	António Campos Calei	1	24		72		4AB	Dário Amorim	Dário Félix Amorim
4	4	4AB	Chara ?			2	1			4AB	Dudú Jack	Domingos Monteiro Jack
5	5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	6	26	1	8		4AB	Edgard	Edgard Arnaud Afane
6	6	4AB	Dário Amorim	Dário Félix Amorim			3			4AB	Geúda	Gelson L. da S. Teles
7	7	4AB	Dudú Jack	Domingos Monteiro Jack	1	8	4	73		4AB	Borra
8	8	4AB	Edgard	Edgard Arnaud Afane		18				4AB	Cadiata
9	9	4AB	Edi			1				4AB	Chara ?
10	10	4AB	Geúda	Gelson L. da S. Teles		8	7			4AB	Edi
Sheet3

Cell Formulas
Range	Formula
CG1:CI10	CG1	=T(SORT(BY1:CA10,3,1))
Dynamic array formulas.

 

[valmir]

Using a smaller example (10-20 rows is adequate, we don't need everything), is this what you are trying to do?

sumif weeknum (version 1).xlsb
	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI
1	1	4AB	Borra			3	3			4AB	Campos	António Campos Calei
2	2	4AB	Cadiata			21				4AB	Dany Ribeiro	Daniel Mendes Ribeiro
3	3	4AB	Campos	António Campos Calei	1	24		72		4AB	Dário Amorim	Dário Félix Amorim
4	4	4AB	Chara ?			2	1			4AB	Dudú Jack	Domingos Monteiro Jack
5	5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	6	26	1	8		4AB	Edgard	Edgard Arnaud Afane
6	6	4AB	Dário Amorim	Dário Félix Amorim			3			4AB	Geúda	Gelson L. da S. Teles
7	7	4AB	Dudú Jack	Domingos Monteiro Jack	1	8	4	73		4AB	Borra
8	8	4AB	Edgard	Edgard Arnaud Afane		18				4AB	Cadiata
9	9	4AB	Edi			1				4AB	Chara ?
10	10	4AB	Geúda	Gelson L. da S. Teles		8	7			4AB	Edi
Sheet3

Cell Formulas
Range	Formula
CG1:CI10	CG1	=T(SORT(BY1:CA10,3,1))
Dynamic array formulas.

Yes that's exactly what I am trying to accomplish. Can you please explain the formula?

 

[jasonb75]

The SORT function is explained in the excel help file. T() may not be necessary, the blanks were showing as 0 when I copied your example to excel so I used that to hide them.

 

[valmir]

The SORT function is explained in the excel help file. T() may not be necessary, the blanks were showing as 0 when I copied your example to excel so I used that to hide them.I

 

[valmir]

I can't get this formula to work:
=T(SORT(BY1:CA608;3;1))
I am getting "#SPILL!" in all cells