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

valmir

Active Member
Joined
Feb 10, 2021
Messages
267
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!
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Change the <>0 part to <>"" instead. A blank cell will be treated as 0 but a formula returning "" will not.
 
Upvote 0
Change the <>0 part to <>"" instead. A blank cell will be treated as 0 but a formula returning "" will not.
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

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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