If #value! In an array then substitute #value!?

[MEUserII]

Consider the following table.

[TABLE="width: 200"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"] C [/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]=$A$1:$A$5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]=VALUE("THREE")[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

If the range is taken from: $A$1:$A$5, and that range is represented as an array in B1 using the formula entered: =$A$1:$A$5; which is equal to the array: {1; 2; #VALUE!; 4 ;5} (when pressing F9 to view full cell contents); then is there a way to substitute the "#VALUE!" element in this array?

In other words, substitute the "#VALUE!" from {1; 2; #VALUE!; 4; 5} to {1; 2; X; 4; 5}; where "X" represents what is substituted for the "#VALUE!" error?

For reference, this question builds on the useful information provided in this thread: https://www.mrexcel.com/forum/excel-questions/1084361-if-value-then-action.html

 

[Marcelo Branco]

Maybe...

=IFERROR(IF(ERROR.TYPE(A1:A5)=3,"X"),A1:A5)

M.

 

[MEUserII]

Maybe...

=IFERROR(IF(ERROR.TYPE(A1:A5)=3,"X"),A1:A5)

M.

Yes, this works! Thank you for the guidance.

For reference I rewrote the formula, so it's a bit clearer: =IFERROR( (IF( ( (ERROR.TYPE($A$1:$A$5) )=(3) ), ("X") ) ), ($A$1:$A$5) ); which accomplishes taking the range input/array of, {1, 2, #VALUE!, 4, 5}, and creating the array: {1, 2, "X", 4, 5}.

Specifically, when applying this formula for the value: "X"=3; it takes on the form: =IFERROR( (IF( ( (ERROR.TYPE($A$1:$A$5) )=(3) ), (3) ) ), ($A$1:$A$5) ); which accomplishes creating the array: {1, 2, 3, 4, 5}.