Strange behavior in INDEX(..., MIN(...)) - array formula

[vezerid]

Hi all,

I ran into this while trying to develop a solution for another OP. I
did come up with a solution, but as I was working the intermediate
steps of this complex problem, I ran into this strange behavior.

The input of the problem is a structure like this. F1, F2, etc are not
cells, they are codes.

F1 a b c d
F2 b h
F3 d
F4 a d
F5 c e h

The object is to reach an output structure like this:

a F1 F4
b F1
c F1 F5
d F1 F3 F4
e F5
f
g
h F2 F5

Below you see my efforts while trying to build the solution. The first
column contains a, b, c, and so on. The second column contains the
following array formula:

=MIN(IF(COUNTIF(OFFSET($A$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5)))

The idea being to find the smallest row number in which the letter of
the first column appears. As you see it contains correct numbers. a, b,
c, d are all in F1. e first appears in F5. f and g do not appear, hence
the 0. And h appears first in F2.

a 1 F1 F1
b 1 F1 F1
c 1 F1 F1
d 1 F1 F1
e 5 F5 F5
f 0 F1 #VALUE!
g 0 F1 #VALUE!
h 2 F2 F2

The third column is the result of plugging the previous formula into
INDEX (again array-entered):

=INDEX($A$1:$A$5,MIN(IF(COUNTIF(OFFSET($A$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5))))

And as you see, in f and g (the two 0's), I am getting *erroneously*
F1, instead of an error value. The error value is successfully produced
in the last column, where I am simply using:

=INDEX($A$1:$A$5,H1)

where H1 contains the value in the second column above.

Given all this: Is this a glitch? Am I missing something? I worked the
problem around by using a more elaborate IF:

=INDEX($A$1:$A$5,MIN(IF(COUNTIF(OFFSET($B$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5),1000)))

This would cause a #REF! error in the place of 0's of the 2nd column by
introducing a very large number and could be handled with error
checking. But why did the previous formula not work?

Regards,
Kostis Vezerides

 

[Herbert Seidenberg]

Index(array,0) is not defined.
Here is an alternate solution in R1C1

listb

F_1 b c d e
F_2 c d . h
F_3 a b c g
F_4 c e g h
F_5 a . b c

lista
a F_5 F_3
b F_5 F_3 F_1
c F_5 F_4 F_3 F_2 F_1
d F_2 F_1
e F_4 F_1
f
g F_4 F_3
h F_4 F_2

Name the F_x listb, but put a space in the first entry,
in my case also, to circumvent the problem you had.
Name the 5x4 input matrix array1.
Name lista.
seq1 Refers to =ROW(INDEX(C1,1):INDEX(C1,ROWS(array1)))
seq2 Refers to =COLUMN(INDEX(R1,1):INDEX(R1,ROWS(array1)))
The output array formula is
=INDEX(listb,LARGE((array1=lista R)*(seq1),seq2)+1)

 

[vezerid]

Herbert

Thanks for the alternative suggestion. This still doesn't answer my
question though. As I said in the post, I know that INDEX(arr,0) is
supposed to produce an error, only in my imlpementation it produced an
erroneous result and not an error value. I was trying to figure out
this behavior.

Regards

Kostis