Thanks for the update.

You're right about the array enter reminder. But, in my defense, in your
first post you mentioned that you knew about array entering and the formula
I suggested is just a different way to write the formula you posted so I
assumed you knew that it also needed array entry.

Back to the problem with the formula...

When you index a range the range is held in a specific sequence. I like to
think of this sequence as positions. The positions are numbered from 1 to
the total count of cells in the indexed range. If the indexed range was
A10:A14 then this range is indexed (has the positions) like this:

A10 = position 1
A11 = position 2
A12 = position 3
A13 = position 4
A14 = position 5

So, when you use the ROW function to tell INDEX which position you want then
the arguments used in ROW have to match the positions in INDEX. Like this:

INDEX(A10:A14,........ROW(A10:A14).....

As written that does not match. We have 5 items indexed in positions 1, 2,
3, 4, and 5 but the ROW function is returning 10, 11, 12, 13, and 14. So, we
need to convert ROW(A10:A14) so that it matches the positions of the indexed
range. We need to convert 10, 11, 12, 13, 14 to 1, 2, 3, 4, 5.

Here's the most "user-proof" method to do that:

ROW(A10:A14)-MIN(ROW(A10:A14))+1

This expression evaluates to:

10-10+1 = 1
11-10+1 = 2
12-10+1 = 3
13-10+1 = 4
14-10+1 = 5

Now we have ROW values that match the positions of the indexed range.

Hopefully that isn't more confusing!



--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
I'm sorry. I tried it again and it worked. I failed to do
CTRL+SHIFT+ENTER
and that's why it didn't work. But it DOES work, and I want to thank you
very much. Just a suggestion, though---for us people who are not Excel
gurus
it would be good to just mention that it's an array formula and one must
press CTRL+SHIFT+ENTER. Thank you. Connie

"T. Valko" wrote:

Hmmm...

Works ok for me.

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
This gives me 0 all the way down. Sorry.

"T. Valko" wrote:

Try it like this:

=INDEX(E$2:E$5000,SMALL(IF(D$2:D$5000=$U$2,ROW(E$2 :E$5000)),ROWS(A$1:A1))-min(row(E$2:E$5000))+1)

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in
message
...
Here's an array formula I found in Excel Help that works almost
perfectly.
There's a glitch and I don't know how to fix it. This is the
formula:
=INDEX($D$2:$E$5000,SMALL(IF($D$2:$D$5000=$U$2,ROW ($D$2:$D$5000)),ROW(1:1)),2)

I know about the CTRL+SHIFT+ENTER for an array formula, so that is
not
the
problem.

This formula is looking for the name in U2 within Col. D (which
appears
multiple times) and returning the corresponding number in Col. E.

The glitch: the first time the name appears in Col. D is in row 26.
It's
again in row 27, 28 and 29. But what the formula is returning is
the
corresponding numbers in 26, 27, 28 and 30. Then the name appears
in
row
77,
78 and 79 and the formula returns 78, 79, 80. The pattern
continues.
See
below. Maybe that will give a better picture. I got this formula
from:
http://office.microsoft.com/en-us/ex...0corresponding
The formula for returning the row number works fine but the
formula
for
returning the corersponding value doesn't work. Here's what it's
doing:

CORRECT ROW ROW RETURNED
26 26
27 27
28 28
29
30
77
78 78
79 79
80
81
82
91
92
96
97 97
98 98
99 99
100 100
101
120
121 121
122 122
123 123
124 124
125
134
135 135
136 136
137 137
138 138
139
143
144 144
145 145
146 146
147