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

 

[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 can't get this formula to work:
=T(SORT(BY1:CA608;3;1))
I am getting "#SPILL!" in all cells

 

[valmir]

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

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.

I am sorry. I was able to get the formula to work now but I only noticed now that in your example the data is sorted based on BY, but it should be sorted based on BZ with the corresponding matches of BY and CA. I hope you have understood correctly now.

 

[jasonb75]

I only noticed now that in your example the data is sorted based on BY

It's actually sorted on CA, not BY. To sort on BZ, change 3 to 2.

BY=1 (1st column)
BZ=2 (2nd column)
CA=3 (3rd column)

 

[valmir]

It's actually sorted on CA, not BY. To sort on BZ, change 3 to 2.

BY=1 (1st column)
BZ=2 (2nd column)
CA=3 (3rd column)

Thanks, it works. There is just one problem. It starts populating from row 108. All matching empty cells of BY appear on top (from row 1 to row 107). I don't mind if the unpopulated rows appear on the bottom.

 

[jasonb75]

Could you post a small example showing the problem please, rows 100 to 115 should be adequate.

 

[valmir]

Could you post a small example showing the problem please, rows 100 to 115 should be adequate.

Stats 2016.xlsx
	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO
100	100	ASA	Minguito	Domingos dos Santos Fernandes	4	19	8	20		PRI			359		BEN	Cláudio Ricardo Cunha Borges 2	Borges 2	125
101	101	ASA	Miro	Almiro Edson Daniel Lobo	1	23		87		PRI			362		DES	Cláudio Segunda Adão	Tchitchi	29
102	102	ASA	Nanga	Nanga João Manuel		1				PRO			374		INT	Cristiano Bernardo Quitembo 1	Cristiano 1	372
103	103	ASA	Nelito Tavares	Nelione José Tavares	7	23	1	6		PRO			383		POR	Cristiano Bernardo Quitembo 2	Cristiano 2	376
104	104	ASA	Paizinho Cadete	Magnusson Cadete						PRO			399		ACA	Cristóvão Segunda Palanga Simão	Ndulo	315
105	105	ASA	Reginó	Mukendi Mbyia Regino		14	1			SAG			428		INT	Dadi Kitondo Kiakanda 1	Dax 1	370
106	106	ASA	Rui Miguel	Jorge Honésimo Miguel						SAG			438		KAB	Dadi Kitondo Kiakanda 2	Dax 2	257
107	107	ASA	Sérge	Seko Atsou Serge	1	12	1	88		SAG			442		PET	Daniel João Zongo Macuenho	Mira	426
108	108	ASA	Sucami	João Sucami Nzongo		1				PET	Abdul	António Nzayinawo	451		KAB	Daniel Mabata	Mpele Mpele	247
109	109	ASA	Traoré	Seydou Ba Traoré						MAI	Abegá	Manuel Pedro Pacavira	465		4AB	Daniel Mendes Ribeiro	Dany Ribeiro	433
110	110	ASA	Tucho ?							PLS	Abel	Abel Lukango	469		POR	Daniel Sebastião	Dani Sebastião	130
111	111	ASA								INT	Abianda 1	Yann Junior Abianda 1	107		CAA	Daniel Xavier	Vitinho	130
112	112	ASA								KAB	Abianda 2	Yann Junior Abianda 2	107		DES	Danilson do Carmo Josefino Traça	Dany Traça	130
113	113	ASA								LIB	Adilson Kivão	Adilson Ernesto Kivão	107		LIB	Dário de Sousa Borges Cardoso	Dário Cardoso	130
114	114	ASA								CAA	Adilson Manuel	Adilson Joaquim Manuel	107		4AB	Dário Félix Amorim	Dário Amorim	130
115	115	BEN	Ady Paulo	Agostinho Domingos Paulo		5	5			ACA	Adó Pena	António Joaquim Barros Pena	10		ASA	David Dinis Magalhães 1	David 1	18
STATS2

Cell Formulas
Range	Formula
BZ100	BZ100	=INDIRECT("ASA!B28")
CA100	CA100	=INDIRECT("ASA!C28")
CB100	CB100	=INDIRECT("ASA!E28")
CC100	CC100	=INDIRECT("ASA!F28")
CD100	CD100	=INDIRECT("ASA!G28")
CE100:CE115	CE100	=IF($CB100=0,"",IF($CB100>0,RANK(CB100,$CB$1:$CB$608)+COUNTIF($CB$1:CB100,CB100)-1))
BZ101	BZ101	=INDIRECT("ASA!B29")
CA101	CA101	=INDIRECT("ASA!C29")
CB101	CB101	=INDIRECT("ASA!E29")
CC101	CC101	=INDIRECT("ASA!F29")
CD101	CD101	=INDIRECT("ASA!G29")
BZ102	BZ102	=INDIRECT("ASA!B30")
CA102	CA102	=INDIRECT("ASA!C30")
CB102	CB102	=INDIRECT("ASA!E30")
CC102	CC102	=INDIRECT("ASA!F30")
CD102	CD102	=INDIRECT("ASA!G30")
BZ103	BZ103	=INDIRECT("ASA!B31")
CA103	CA103	=INDIRECT("ASA!C31")
CB103	CB103	=INDIRECT("ASA!E31")
CC103	CC103	=INDIRECT("ASA!F31")
CD103	CD103	=INDIRECT("ASA!G31")
BZ104	BZ104	=INDIRECT("ASA!B32")
CA104	CA104	=INDIRECT("ASA!C32")
CB104	CB104	=INDIRECT("ASA!E32")
CC104	CC104	=INDIRECT("ASA!F32")
CD104	CD104	=INDIRECT("ASA!G32")
BZ105	BZ105	=INDIRECT("ASA!B33")
CA105	CA105	=INDIRECT("ASA!C33")
CB105	CB105	=INDIRECT("ASA!E33")
CC105	CC105	=INDIRECT("ASA!F33")
CD105	CD105	=INDIRECT("ASA!G33")
BZ106	BZ106	=INDIRECT("ASA!B34")
CA106	CA106	=INDIRECT("ASA!C34")
CB106	CB106	=INDIRECT("ASA!E34")
CC106	CC106	=INDIRECT("ASA!F34")
CD106	CD106	=INDIRECT("ASA!G34")
BZ107	BZ107	=INDIRECT("ASA!B35")
CA107	CA107	=INDIRECT("ASA!C35")
CB107	CB107	=INDIRECT("ASA!E35")
CC107	CC107	=INDIRECT("ASA!F35")
CD107	CD107	=INDIRECT("ASA!G35")
BZ108	BZ108	=INDIRECT("ASA!B36")
CA108	CA108	=INDIRECT("ASA!C36")
CB108	CB108	=INDIRECT("ASA!E36")
CC108	CC108	=INDIRECT("ASA!F36")
CD108	CD108	=INDIRECT("ASA!G36")
BZ109	BZ109	=INDIRECT("ASA!B37")
CA109	CA109	=INDIRECT("ASA!C37")
CB109	CB109	=INDIRECT("ASA!E37")
CC109	CC109	=INDIRECT("ASA!F37")
CD109	CD109	=INDIRECT("ASA!G37")
BZ110	BZ110	=INDIRECT("ASA!B38")
CA110	CA110	=INDIRECT("ASA!C38")
CB110	CB110	=INDIRECT("ASA!E38")
CC110	CC110	=INDIRECT("ASA!F38")
CD110	CD110	=INDIRECT("ASA!G38")
BZ111	BZ111	=INDIRECT("ASA!B39")
CA111	CA111	=INDIRECT("ASA!C39")
CB111	CB111	=INDIRECT("ASA!E39")
CC111	CC111	=INDIRECT("ASA!F39")
CD111	CD111	=INDIRECT("ASA!G39")
BZ112	BZ112	=INDIRECT("ASA!B40")
CA112	CA112	=INDIRECT("ASA!C40")
CB112	CB112	=INDIRECT("ASA!E40")
CC112	CC112	=INDIRECT("ASA!F40")
CD112	CD112	=INDIRECT("ASA!G40")
BZ113	BZ113	=INDIRECT("ASA!B41")
CA113	CA113	=INDIRECT("ASA!C41")
CB113	CB113	=INDIRECT("ASA!E41")
CC113	CC113	=INDIRECT("ASA!F41")
CD113	CD113	=INDIRECT("ASA!G41")
BZ114	BZ114	=INDIRECT("ASA!B42")
CA114	CA114	=INDIRECT("ASA!C42")
CB114	CB114	=INDIRECT("ASA!E42")
CC114	CC114	=INDIRECT("ASA!F42")
CD114	CD114	=INDIRECT("ASA!G42")
BZ115	BZ115	=INDIRECT("BEN!B5")
CA115	CA115	=INDIRECT("BEN!C5")
CB115	CB115	=INDIRECT("BEN!E5")
CC115	CC115	=INDIRECT("BEN!F5")
CD115	CD115	=INDIRECT("BEN!G5")
CL100:CL115	CL100	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX100,$CO$1:$CO$608,0),1),"")
CM100:CM115	CM100	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX100,$CO$1:$CO$608,0),3),"")
CN100:CN115	CN100	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX100,$CO$1:$CO$608,0),2),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BZ2:BZ608	Expression	=CA2=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]

Each team (represented by BY) has a slot for a total of 38 players. Some players may be added in the future.

 

[jasonb75]

It looks like some of your blanks might actually be zeros that are being masked by conditional formatting, see if this corrects it.

=SORT(FILTER(BY1:CA10,CA1:CA10<>0),2,1)

 

[valmir]

It looks like some of your blanks might actually be zeros that are being masked by conditional formatting, see if this corrects it.

=SORT(FILTER(BY1:CA10,CA1:CA10<>0),2,1)

This is perfect Sir! Thank you so much for your time and patience! You just saved my day! ?

 

[valmir]

It looks like some of your blanks might actually be zeros that are being masked by conditional formatting, see if this corrects it.

=SORT(FILTER(BY1:CA10,CA1:CA10<>0),2,1)

Just one more thing. In columns CL, CM and CN, I want to achieve the same result the only difference being sorted now based on CA but in this order: BY, CA and BZ (in this order). The formula I am using here is
=COUNTIF($CA1:$CA608;"<="&$CA1:$CA608)
I tried just changing the formula to =SORT(FILTER(BY1:CA608;CA1:CA608<>0);3;1) and it actually worked and CL is where is supposed to be but CA appears on CN and BZ appears on CM (there should be a swap here)