Combined textjoint/index formula behaving differently to direct input vs formula input

V

valmir

Board Regular
Joined
Feb 10, 2021
Messages
235
Office Version
  1. 365
Platform
  1. Windows
Hello everyone
The formula in O6:O9:
=IFERROR(TEXTJOIN(";";1;IF(INDEX($C6:$L49;MATCH(1;($A6:$A49=M6)*($B6:$B49=N6);0);0)<>0;$C$1:$L$1;""));"")
is behaving differently when there is a direct input (from source sheet) (C6:L27) as opposed to a value drawn from a formula (C27:L27).
Specifically: all inputs in the range C6:L26 are direct input values (on the source sheet), whereas the inputs in C27:L27 are drawn from a formula (in the source sheet).
If working properly, in O9 it should not be "1;2;3;4;5;6;7;8;9;10" but rather "4;10"
Is there a fix for this? Thanks in advance!
2020-21 segundona.xlsx
ABCDEFGHIJKLMNO
112345678910
2
3
4
5
6AGUAlvin          AGUFX5
7AGUCalvin          AGUIbra1
8AGUDibala          AGUTed7
9AGUFX    ꞁ     AGUzMissingAGU1;2;3;4;5;6;7;8;9;10
10AGUIbraՁ          
11AGUJack           
12AGULelis           
13AGUMark           
14AGUMel           
15AGUMelvin           
16AGUMeduso           
17AGUMervin           
18AGUMob           
19AGUNat           
20AGUNeyde           
21AGUPaul           
22AGURomeo           
23AGUSawa           
24AGUStone           
25AGUTed      ꞁ    
26AGUTitanic           
27AGUzMissingAGU   Ձ     ꞁ 
STATS4 (2)
Cell Formulas
RangeFormula
A6A6=INDIRECT("STATS2!AE1")
B6B6=INDIRECT("STATS2!AG1")
C6:L6C6=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B6,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
M6:N9M6=IFERROR(FILTER(A6:B49,MMULT(--(C6:L49<>0)*(C6:L49<>""),SEQUENCE(COLUMNS(C6:L49)))),"")
A7A7=INDIRECT("STATS2!AE2")
B7B7=INDIRECT("STATS2!AG2")
C7:L7C7=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B7,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A8A8=INDIRECT("STATS2!AE3")
B8B8=INDIRECT("STATS2!AG3")
C8:L8C8=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B8,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A9A9=INDIRECT("STATS2!AE4")
B9B9=INDIRECT("STATS2!AG4")
C9:L9C9=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B9,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A10A10=INDIRECT("STATS2!AE5")
B10B10=INDIRECT("STATS2!AG5")
C10:L10C10=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B10,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A11A11=INDIRECT("STATS2!AE6")
B11B11=INDIRECT("STATS2!AG6")
C11:L11C11=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B11,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A12A12=INDIRECT("STATS2!AE7")
B12B12=INDIRECT("STATS2!AG7")
C12:L12C12=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B12,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A13A13=INDIRECT("STATS2!AE8")
B13B13=INDIRECT("STATS2!AG8")
C13:L13C13=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B13,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A14A14=INDIRECT("STATS2!AE9")
B14B14=INDIRECT("STATS2!AG9")
C14:L14C14=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B14,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A15A15=INDIRECT("STATS2!AE10")
B15B15=INDIRECT("STATS2!AG10")
C15:L15C15=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B15,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A16A16=INDIRECT("STATS2!AE11")
B16B16=INDIRECT("STATS2!AG11")
C16:L16C16=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B16,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A17A17=INDIRECT("STATS2!AE12")
B17B17=INDIRECT("STATS2!AG12")
C17:L17C17=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B17,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A18A18=INDIRECT("STATS2!AE13")
B18B18=INDIRECT("STATS2!AG13")
C18:L18C18=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B18,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A19A19=INDIRECT("STATS2!AE14")
B19B19=INDIRECT("STATS2!AG14")
C19:L19C19=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B19,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A20A20=INDIRECT("STATS2!AE15")
B20B20=INDIRECT("STATS2!AG15")
C20:L20C20=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B20,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A21A21=INDIRECT("STATS2!AE16")
B21B21=INDIRECT("STATS2!AG16")
C21:L21C21=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B21,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A22A22=INDIRECT("STATS2!AE17")
B22B22=INDIRECT("STATS2!AG17")
C22:L22C22=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B22,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A23A23=INDIRECT("STATS2!AE18")
B23B23=INDIRECT("STATS2!AG18")
C23:L23C23=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B23,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A24A24=INDIRECT("STATS2!AE19")
B24B24=INDIRECT("STATS2!AG19")
C24:L24C24=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B24,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A25A25=INDIRECT("STATS2!AE20")
B25B25=INDIRECT("STATS2!AG20")
C25:L25C25=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B25,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A26A26=INDIRECT("STATS2!AE21")
B26B26=INDIRECT("STATS2!AG21")
C26:L26C26=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B26,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
A27A27=INDIRECT("STATS2!AE22")
B27B27=INDIRECT("STATS2!AG22")
C27:L27C27=IFERROR(INDEX(INDIRECT($A6&"!$R$5:$FL$48"),MATCH($B27,INDIRECT($A6&"!$B$5:$B$48"),0),MATCH(C$1,INDIRECT($A6&"!$R$2:$FL$2"),0)+2),"")
O6O6=IFERROR(TEXTJOIN(";",1,IF(INDEX($C6:$L49,MATCH(1,($A6:$A49=M6)*($B6:$B49=N6),0),0)<>0,$C$1:$L$1,"")),"")
O7:O27O7=IFERROR(TEXTJOIN(";",1,IF(INDEX($C$6:$L$49,MATCH(1,($A$6:$A$49=M7)*($B$6:$B$49=N7),0),0)<>0,$C$1:$L$1,"")),"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B6:B49Cell Value=0textNO
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Change the <>0 part to <>"" instead. A blank cell will be treated as 0 but a formula returning "" will not.
 
Upvote 0
RoryA said:
Change the <>0 part to <>"" instead. A blank cell will be treated as 0 but a formula returning "" will not.
Click to expand...
I changed the formula in O6 to:
=IFERROR(TEXTJOIN(";";1;IF(INDEX($C6:$L49;MATCH(1;($A6:$A49=M6)*($B6:$B49=N6);0);0)<>"";$C$1:$L$1;""));"")
And I got this:
1;2;3;4;5;6;7;8;9;10
So, the same issue as in O9
 
Upvote 0
Perhaps your formulas for the direct cells are returning 0 but you've hidden those, whereas the other cells are returning "" due to errors. If you revert to the original formula but change the IFERROR formulas in columns C:L to return 0 rather than "", does that work?
 
Upvote 0
Sorry, I'm not that good. Could you please be so kind as to implement those changes and show me how the formula should look like?
 
Upvote 0
I wonder if a filter/index match formula can replace this one...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Need to delete color and no data after blank column
Replies
2
Views
251
sksanjeev786
S
I
  • Solved
Indirect formula based off cell value on current sheet
Replies
3
Views
61
Alex Blakenburg
A
S
  • Solved
need to remove all data after "n"
Replies
2
Views
308
sksanjeev786
S
A
  • Question
Sorting from the lowest to highest value
Replies
1
Views
69
dermie_72
D
V
  • Question
What is missing/wrong with this formula? if+index-match+text-join
Replies
5
Views
193
valmir
V

Forum statistics

Threads
1,215,073
Messages
6,122,974
Members
449,095
Latest member
Mr Hughes

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