Thank you, Pete. As I mentioned to the others, my data starts in row 2 so I
thought I had to have the formula starting in row 2. Thank you! I'm so
pleased to get this thing working! Connie

"Pete_UK" wrote:

This part in the middle of your formula:

ROW($D$2:$D$5000)

will return 2 if there is a match on the first row. However, as your
INDEX array starts on row 2 then this will get the data from the
second element of the array, i.e. from row 3. So, to rectify it change
that middle part to:

ROW($D$2:$D$5000)-1
or
ROW($D$1:$D$4999).

Hope this helps.

Pete

On Oct 30, 6:49 pm, Connie Martin
wrote:
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...aspx?pid=CL100...
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