Index match funcion ignoring blank cells

V

valmir

Board Regular
Joined
Feb 10, 2021
Messages
235
Office Version
  1. 365
Platform
  1. Windows
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
 
Sort by date Sort by votes
jasonb75 said:
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.
Click to expand...
I can't get this formula to work:
=T(SORT(BY1:CA608;3;1))
I am getting "#SPILL!" in all cells
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
valmir said:
Yes that's exactly what I am trying to accomplish. Can you please explain the formula?
Click to expand...

jasonb75 said:
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
BXBYBZCACBCCCDCECFCGCHCI
114ABBorra334ABCamposAntónio Campos Calei
224ABCadiata214ABDany RibeiroDaniel Mendes Ribeiro
334ABCamposAntónio Campos Calei124724ABDário AmorimDário Félix Amorim
444ABChara ?214ABDudú JackDomingos Monteiro Jack
554ABDany RibeiroDaniel Mendes Ribeiro626184ABEdgardEdgard Arnaud Afane
664ABDário AmorimDário Félix Amorim34ABGeúdaGelson L. da S. Teles
774ABDudú JackDomingos Monteiro Jack184734ABBorra
884ABEdgardEdgard Arnaud Afane184ABCadiata
994ABEdi14ABChara ?
10104ABGeúdaGelson L. da S. Teles874ABEdi
Sheet3
Cell Formulas
RangeFormula
CG1:CI10CG1=T(SORT(BY1:CA10,3,1))
Dynamic array formulas.
Click to expand...
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.
 
Upvote 0
valmir said:
I only noticed now that in your example the data is sorted based on BY
Click to expand...
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)
 
Upvote 0
jasonb75 said:
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)
Click to expand...
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.
 
Upvote 0
Could you post a small example showing the problem please, rows 100 to 115 should be adequate.
 
Upvote 0
jasonb75 said:
Could you post a small example showing the problem please, rows 100 to 115 should be adequate.
Click to expand...
Stats 2016.xlsx
BXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCO
100100ASAMinguitoDomingos dos Santos Fernandes419820PRI359BENCláudio Ricardo Cunha Borges 2Borges 2125
101101ASAMiroAlmiro Edson Daniel Lobo123 87PRI362DESCláudio Segunda AdãoTchitchi29
102102ASANangaNanga João Manuel 1  PRO374INTCristiano Bernardo Quitembo 1Cristiano 1372
103103ASANelito TavaresNelione José Tavares72316PRO383PORCristiano Bernardo Quitembo 2Cristiano 2376
104104ASAPaizinho CadeteMagnusson Cadete    PRO399ACACristóvão Segunda Palanga SimãoNdulo315
105105ASAReginóMukendi Mbyia Regino 141 SAG428INTDadi Kitondo Kiakanda 1Dax 1370
106106ASARui MiguelJorge Honésimo Miguel    SAG438KABDadi Kitondo Kiakanda 2Dax 2257
107107ASASérgeSeko Atsou Serge112188SAG442PETDaniel João Zongo MacuenhoMira426
108108ASASucamiJoão Sucami Nzongo 1  PETAbdulAntónio Nzayinawo451KABDaniel MabataMpele Mpele247
109109ASATraoréSeydou Ba Traoré    MAIAbegáManuel Pedro Pacavira4654ABDaniel Mendes RibeiroDany Ribeiro433
110110ASATucho ?     PLSAbelAbel Lukango469PORDaniel SebastiãoDani Sebastião130
111111ASA      INTAbianda 1Yann Junior Abianda 1107CAADaniel XavierVitinho130
112112ASA      KABAbianda 2Yann Junior Abianda 2107DESDanilson do Carmo Josefino TraçaDany Traça130
113113ASA      LIBAdilson KivãoAdilson Ernesto Kivão107LIBDário de Sousa Borges CardosoDário Cardoso130
114114ASA      CAAAdilson ManuelAdilson Joaquim Manuel1074ABDário Félix AmorimDário Amorim130
115115BENAdy PauloAgostinho Domingos Paulo 55 ACAAdó PenaAntónio Joaquim Barros Pena10ASADavid Dinis Magalhães 1David 118
STATS2
Cell Formulas
RangeFormula
BZ100BZ100=INDIRECT("ASA!B28")
CA100CA100=INDIRECT("ASA!C28")
CB100CB100=INDIRECT("ASA!E28")
CC100CC100=INDIRECT("ASA!F28")
CD100CD100=INDIRECT("ASA!G28")
CE100:CE115CE100=IF($CB100=0,"",IF($CB100>0,RANK(CB100,$CB$1:$CB$608)+COUNTIF($CB$1:CB100,CB100)-1))
BZ101BZ101=INDIRECT("ASA!B29")
CA101CA101=INDIRECT("ASA!C29")
CB101CB101=INDIRECT("ASA!E29")
CC101CC101=INDIRECT("ASA!F29")
CD101CD101=INDIRECT("ASA!G29")
BZ102BZ102=INDIRECT("ASA!B30")
CA102CA102=INDIRECT("ASA!C30")
CB102CB102=INDIRECT("ASA!E30")
CC102CC102=INDIRECT("ASA!F30")
CD102CD102=INDIRECT("ASA!G30")
BZ103BZ103=INDIRECT("ASA!B31")
CA103CA103=INDIRECT("ASA!C31")
CB103CB103=INDIRECT("ASA!E31")
CC103CC103=INDIRECT("ASA!F31")
CD103CD103=INDIRECT("ASA!G31")
BZ104BZ104=INDIRECT("ASA!B32")
CA104CA104=INDIRECT("ASA!C32")
CB104CB104=INDIRECT("ASA!E32")
CC104CC104=INDIRECT("ASA!F32")
CD104CD104=INDIRECT("ASA!G32")
BZ105BZ105=INDIRECT("ASA!B33")
CA105CA105=INDIRECT("ASA!C33")
CB105CB105=INDIRECT("ASA!E33")
CC105CC105=INDIRECT("ASA!F33")
CD105CD105=INDIRECT("ASA!G33")
BZ106BZ106=INDIRECT("ASA!B34")
CA106CA106=INDIRECT("ASA!C34")
CB106CB106=INDIRECT("ASA!E34")
CC106CC106=INDIRECT("ASA!F34")
CD106CD106=INDIRECT("ASA!G34")
BZ107BZ107=INDIRECT("ASA!B35")
CA107CA107=INDIRECT("ASA!C35")
CB107CB107=INDIRECT("ASA!E35")
CC107CC107=INDIRECT("ASA!F35")
CD107CD107=INDIRECT("ASA!G35")
BZ108BZ108=INDIRECT("ASA!B36")
CA108CA108=INDIRECT("ASA!C36")
CB108CB108=INDIRECT("ASA!E36")
CC108CC108=INDIRECT("ASA!F36")
CD108CD108=INDIRECT("ASA!G36")
BZ109BZ109=INDIRECT("ASA!B37")
CA109CA109=INDIRECT("ASA!C37")
CB109CB109=INDIRECT("ASA!E37")
CC109CC109=INDIRECT("ASA!F37")
CD109CD109=INDIRECT("ASA!G37")
BZ110BZ110=INDIRECT("ASA!B38")
CA110CA110=INDIRECT("ASA!C38")
CB110CB110=INDIRECT("ASA!E38")
CC110CC110=INDIRECT("ASA!F38")
CD110CD110=INDIRECT("ASA!G38")
BZ111BZ111=INDIRECT("ASA!B39")
CA111CA111=INDIRECT("ASA!C39")
CB111CB111=INDIRECT("ASA!E39")
CC111CC111=INDIRECT("ASA!F39")
CD111CD111=INDIRECT("ASA!G39")
BZ112BZ112=INDIRECT("ASA!B40")
CA112CA112=INDIRECT("ASA!C40")
CB112CB112=INDIRECT("ASA!E40")
CC112CC112=INDIRECT("ASA!F40")
CD112CD112=INDIRECT("ASA!G40")
BZ113BZ113=INDIRECT("ASA!B41")
CA113CA113=INDIRECT("ASA!C41")
CB113CB113=INDIRECT("ASA!E41")
CC113CC113=INDIRECT("ASA!F41")
CD113CD113=INDIRECT("ASA!G41")
BZ114BZ114=INDIRECT("ASA!B42")
CA114CA114=INDIRECT("ASA!C42")
CB114CB114=INDIRECT("ASA!E42")
CC114CC114=INDIRECT("ASA!F42")
CD114CD114=INDIRECT("ASA!G42")
BZ115BZ115=INDIRECT("BEN!B5")
CA115CA115=INDIRECT("BEN!C5")
CB115CB115=INDIRECT("BEN!E5")
CC115CC115=INDIRECT("BEN!F5")
CD115CD115=INDIRECT("BEN!G5")
CL100:CL115CL100=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX100,$CO$1:$CO$608,0),1),"")
CM100:CM115CM100=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX100,$CO$1:$CO$608,0),3),"")
CN100:CN115CN100=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX100,$CO$1:$CO$608,0),2),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
BZ2:BZ608Expression=CA2=0textNO
CA1:CA608Cell Value=0textNO
CI1:CI608Cell Value=0textNO
CH1:CH608Expression=CI1=0textNO
CB1:CD608Cell Value=0textNO
 
Upvote 0
Each team (represented by BY) has a slot for a total of 38 players. Some players may be added in the future.
 
Upvote 0
It looks like some of your blanks might actually be zeros that are being masked by conditional formatting, see if this corrects it.
Excel Formula: 
=SORT(FILTER(BY1:CA10,CA1:CA10<>0),2,1)
 
Upvote 0
Solution
jasonb75 said:
It looks like some of your blanks might actually be zeros that are being masked by conditional formatting, see if this corrects it.
Excel Formula: 
=SORT(FILTER(BY1:CA10,CA1:CA10<>0),2,1)
Click to expand...
This is perfect Sir! Thank you so much for your time and patience! You just saved my day! ?
 
Upvote 0
jasonb75 said:
It looks like some of your blanks might actually be zeros that are being masked by conditional formatting, see if this corrects it.
Excel Formula: 
=SORT(FILTER(BY1:CA10,CA1:CA10<>0),2,1)
Click to expand...
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
INDEX MATCH from two sheets onto third sheet?
Replies
1
Views
75
etaf
etaf
M
  • Question
Formula to return most repeated text in filtered data
Replies
4
Views
207
Domenic
D
F
  • Question
Tie-Breaking Question (Using Index/Match/Large)
Replies
3
Views
307
Fluff
Fluff
A
  • Solved
Index Match/X-Lookup
2
Replies
11
Views
162
Fluff
Fluff
S
  • Question
INDEX, MATCH-Excel
Replies
1
Views
455
GuzmanServices
G

Forum statistics

Threads
1,214,651
Messages
6,120,739
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top