SUBSTITUTE function with dynamic array -- works great but is this an Excel bug?

Question

The original puzzle "use dynamic arrays to get into E the column header where values in B through whatever match the value in same row A".

I put this expression in a LET formula and it works:

NOTE 1. The number 2 does not exist in seq

NOTE 2. No text exists in the dynamic arrays generated by SEQUENCE

NOTE 3. In Dewey the required argument old_text is missing

Is this a bug, a feature or am I going crazy?

P.S. If somebody has a better solution than TRANSPOSE(TEXTSPLIT(TEXTJOIN... to collapse a multicolumn array where exactly one element per row is not blank into a single column containing only the nonblank elements I'd like to know. Thanks.

Answer 1

Hi,

Enter this formula in cell E2 and copy down

=INDEX($B$1:$D$1,1,MATCH(TRUE,INDEX(B2:D2=A2,,),0))

Does this help?

Answer 2

Thank you for your kind words. If i figure out the reason, I will post it.

Answer 3

Hi, Ashish --

I have seen your work in this forum and I am impressed.

I am not looking for another way of doing this using copy down. My OP states using dynamic arrays.

My big problem is SUBSTITUTE takes three required arguments according to MS documentation and I successfully executed a formula with one of them missing. This is not supposed to be possible and looks like an Excel bug.

Thanks