Connie

The piece you missed was enter the formula as an array. Highlight the
formula and press (CTRL+SHIFT+ENTER) then copy the formula down and it will
give you the results your looking for.

Jack

"Connie Martin" wrote:

Chris, that is exactly the formula I had originally (see bottom post). But
it doesn't work. 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


"Chris T-M" wrote:

I can't tell if you've resolved this yet, but I thought the following
explanation of what's going on might help you solve this.

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

...$U$2... the $ sign locks the cell the function is looking at. If you copy
this down, it's still looking at the value in cell U2. Column U, Row 2 are
locked in.

If you used $U2 and copied it down, then it is locked for Column U, but it
will lookup row 2, 3, 4... as it copies down. Column U is locked in.

If you used U$2 and copied it down or across (I know that's not what you
asked, but for illustration...) then the function would be looking at V2, W2,
X2... Row 2 is locked in.

I hope this will lead you to a resolution.

CTM

"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