What is missing/wrong with this formula? if+index-match+text-join

[valmir]

Hi everyone
This formula:
=IF(INDEX(INDIRECT($A24&"!$R$2:$FK$51");50;MATCH(INDIRECT($A24&"!$HV$5");INDIRECT($A24&"!$R$2:$FK$2");0)+2)<0;INDEX(INDIRECT($A24&"!$R$2:$FK$51");50;MATCH(INDIRECT($A24&"!$HV$5");INDIRECT($A24&"!$R$2:$FK$2");0)+2);TEXTJOIN("-";TRUE;INDIRECT($A24&"!$ID$5";INDIRECT($A24&"!$IE$5"))))
is made up of three parts:
Part 1: if index match < 0
IF(INDEX(INDIRECT($A24&"!$R$2:$FK$51");50;MATCH(INDIRECT($A24&"!$HV$5");INDIRECT($A24&"!$R$2:$FK$2");0)+2)<0
Part 2: value if true
index match
Part 3: value if false:
TEXTJOIN("-";TRUE;INDIRECT($A24&"!$ID$5";INDIRECT($A24&"!$IE$5")
Textjoin part is showing only the first value, not the second one.
Thanks!

 

[6StringJazzer]

The INDIRECT syntax does not look right in Part 3.

INDIRECT($A24&"!$ID$5";INDIRECT($A24&"!$IE$5"))

The second argument to INDIRECT is optional to indicate address style and should be TRUE or FALSE if included. But you have another INDIRECT there that will be a multi-cell range reference. I'm guessing that is your problem. But since I don't know what you want your formula to do I cannot guess at what would be correct.

 

[valmir]

In part 3, I just want to join ID5 & IE5. How do I do that then?
In this formula:
=IF(INDIRECT($A24&"!$IH$5")<0;INDIRECT($A24&"!$IH$5");TEXTJOIN("-";TRUE;INDIRECT($A24&"!$ID$5");INDIRECT($A24&"!$IE$5")))
It works perfectly!
For this case, I have this in "$A24&"!$IH$5":
=IFERROR(INDEX($R$51:$FK$51;MATCH(HV5;$R$2:$FK$2;0)+2);"")

 

[valmir]

I don't have much of an idea how I got this but this is the solution:
=IF(INDEX(INDIRECT($A24&"!$R$51:$FK$51");MATCH(1;INDIRECT($A24&"!$R$2:$FK$2");0)+2)<0;INDEX(INDIRECT($A24&"!$R$51:$FK$51");MATCH(1;INDIRECT($A24&"!$R$2:$FK$2");0)+2);TEXTJOIN("-";TRUE;INDIRECT($A24&"!$ID$5");INDIRECT($A24&"!$IE$5")))

 

[valmir]

I don't have much of an idea how I got this but this is the solution:
=IF(INDEX(INDIRECT($A24&"!$R$51:$FK$51");MATCH(1;INDIRECT($A24&"!$R$2:$FK$2");0)+2)<0;INDEX(INDIRECT($A24&"!$R$51:$FK$51");MATCH(1;INDIRECT($A24&"!$R$2:$FK$2");0)+2);TEXTJOIN("-";TRUE;INDIRECT($A24&"!$ID$5");INDIRECT($A24&"!$IE$5")))

Still need help: match value is not working...

 

[valmir]

Still need help: match value is not working...

Removed exact match! Voilá!