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

V

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!
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
393
sksanjeev786
S
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
172
SBExcel123
S
I
  • Solved
Indirect formula based off cell value on current sheet
Replies
3
Views
406
Alex Blakenburg
A
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
310
GeneBF
G
K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
472
Kenny_D
K

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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