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