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,R OW($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,R OW($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