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

[MEUserII]

Consider the following table.

	A	B	C
1	1	=$A$1:$A$5
2	2
3	=VALUE("THREE")
4	4
5	5

<tbody>
</tbody>

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}.