I have found this array formula that is partly working---closer than what
I've tried so far:
=INDEX($D$2:$E$5000,SMALL(IF($D$2:$D$5000=$U$2,ROW ($D$2:$D$5000)),ROW(1:1)),2)

The problem with it is it's picking up names that are not in cell U2.

I found this answer in this website:

http://office.microsoft.com/en-us/ex...0corresponding

Connie

"Duke Carey" wrote:

Connie -

Use this in the first cell and copy it down. Note that my version looks for
U2, not $U$2 (relative address instead of absolute). The latter, when
copied, makes EVERY one of the VLOOKUPs find the value in U2, while the
former will look to U3, then U4, etc.

=VLOOKUP(U2,$D$2:$E$5000,2,FALSE)



"Connie Martin" wrote:

I have such a struggle with this formula. I've looked in Help and Online in
MS, but to no avail. It still remains a mystery! I have this formula that
works for one cell only, not for the column:
=VLOOKUP($U$2,$D$2:$E$5000,2,FALSE). It picks up the first number in the E
column correctly, but when I drag it down, it simply repeats that number.
What gives? Why won't it continue to look for others?

To further explain: In cell U2 is a name. I want to search that name
within the range D2:D5000 (which appears very often) and return the number
for that name listed in range E2:E5000 (which number will quite often vary).
Obviously my formula isn't quite right. Hope I get E for Effort, at least!!
But the correct formula would be better! Thank you! Connie