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

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

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)

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