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

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

Hi Connie,

I think you should leave out at least one $ sing in the first argument:

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

Please let us know your results

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
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

Duke, I'm just going home now, so will check this out in the morning. Thank
you so much for responding. Will get back to you tomorrow. Thank you.
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

This gives me the right answer for the first one and #N/A for the rest when I
drag it down. This lookup must always look for the name in U2 in Col. D and
give the corresponding number in Col. E. Connie

"Niek Otten" wrote:

Hi Connie,

I think you should leave out at least one $ sing in the first argument:

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

Please let us know your results

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
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

This gives me the right answer for the first one and #N/A for the rest when I
drag it down. This lookup must always look for the name in U2 in Col. D and
give the corresponding number in Col. E. 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

Niek,

Which $ sign should be removed? I'm having the same exact problem... and
I've never had this problem with the vlookup formula before. Normally, I
don't use all the dollar signs ($), but this formula is kicking my butt this
time. I'm going to keep working on it and let you know what I come up with.
I'm an expert Excel user, so this is incredibly frustrating for me -
especially since I use it all the time with no such trouble. I'm going to
keep working on it, but if you have the solution, please - let us know.

Thanks.

Jackson

"Niek Otten" wrote:

Hi Connie,

I think you should leave out at least one $ sing in the first argument:

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

Please let us know your results

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
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

Connie -

Your description of what you are trying to do makes no sense to me. If you
are only looking for one name - the one in U2 - and it only appears once in
your lookup table, then why copy the formula down?

I'm guessing that VLOOKUP isn't the right vehicle for what you're trying to
accomplish

"Connie Martin" wrote:

This gives me the right answer for the first one and #N/A for the rest when I
drag it down. This lookup must always look for the name in U2 in Col. D and
give the corresponding number in Col. E. 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

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

"Jackson Martin" wrote:

Niek,

Which $ sign should be removed? I'm having the same exact problem... and
I've never had this problem with the vlookup formula before. Normally, I
don't use all the dollar signs ($), but this formula is kicking my butt this
time. I'm going to keep working on it and let you know what I come up with.
I'm an expert Excel user, so this is incredibly frustrating for me -
especially since I use it all the time with no such trouble. I'm going to
keep working on it, but if you have the solution, please - let us know.

Thanks.

Jackson

"Niek Otten" wrote:

Hi Connie,

I think you should leave out at least one $ sing in the first argument:

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

Please let us know your results

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
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

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

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

"Niek Otten" wrote:

Hi Connie,

I think you should leave out at least one $ sing in the first argument:

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

Please let us know your results

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Connie Martin" wrote in message
...
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

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

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

I am looking for one name in cell U2 that appears several times in col. D
with different numbers in the corresponding cell of col. E. Please see my
previous post to you about an array formula that is very close to what I
want. I just don't understand why sometimes it's picking out numbers in col.
E that don't correspond with the name in U2. It gets some right and throws
in others that don't make sense. Connie

"Duke Carey" wrote:

Connie -

Your description of what you are trying to do makes no sense to me. If you
are only looking for one name - the one in U2 - and it only appears once in
your lookup table, then why copy the formula down?

I'm guessing that VLOOKUP isn't the right vehicle for what you're trying to
accomplish

"Connie Martin" wrote:

This gives me the right answer for the first one and #N/A for the rest when I
drag it down. This lookup must always look for the name in U2 in Col. D and
give the corresponding number in Col. E. 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

Perhaps you could explain what you are trying to do with the returned data
from Column D. I'm guessing you want to add them, multiply them, count the
matches, make a list of them... Perhaps you can get a better function
recommendation that way.

I'm not familiar with INDEX or SMALL, so if you get responses there I'll bow
out.

FYI: VLOOKUP only returns the first match in the array. That's why you kept
getting the same match or NA.

"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

I need a list of all sales orders for that name (customer). I will actually
have several columns in this spreadsheet that will pick out information from
a huge spreadsheet. I'm working on one column at a time. If I can get this
column of info right, the rest should be a breeze. I could use Filter on the
huge spreadsheet and hide unwanted columns, etc. That's an option, but I
sure would like this smaller one to work, because if so, then I could do it
for all customers. But maybe I'm asking too much. Don't know. Thank you
for responding. Connie

"Chris T-M" wrote:

Perhaps you could explain what you are trying to do with the returned data
from Column D. I'm guessing you want to add them, multiply them, count the
matches, make a list of them... Perhaps you can get a better function
recommendation that way.

I'm not familiar with INDEX or SMALL, so if you get responses there I'll bow
out.

FYI: VLOOKUP only returns the first match in the array. That's why you kept
getting the same match or NA.

"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

I'm out of my league Connie.
It sounds like you need a VBA macro to lookup the data, and make a list out
of it.
Good Luck

"Connie Martin" wrote:

I need a list of all sales orders for that name (customer). I will actually
have several columns in this spreadsheet that will pick out information from
a huge spreadsheet. I'm working on one column at a time. If I can get this
column of info right, the rest should be a breeze. I could use Filter on the
huge spreadsheet and hide unwanted columns, etc. That's an option, but I
sure would like this smaller one to work, because if so, then I could do it
for all customers. But maybe I'm asking too much. Don't know. Thank you
for responding. Connie

"Chris T-M" wrote:

Perhaps you could explain what you are trying to do with the returned data
from Column D. I'm guessing you want to add them, multiply them, count the
matches, make a list of them... Perhaps you can get a better function
recommendation that way.

I'm not familiar with INDEX or SMALL, so if you get responses there I'll bow
out.

FYI: VLOOKUP only returns the first match in the array. That's why you kept
getting the same match or NA.

"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

Connie

Is this the problem you are trying to solve.

U2 contains Bolts

D2:E6 contains

Bolts 10
Nuts 20
Nuts 15
Bolts 18
Bolts 10

you are looking for a formula that will give you a list of all the rows with
Bolts, which in this example would be three rows




"Chris T-M" wrote:

I'm out of my league Connie.
It sounds like you need a VBA macro to lookup the data, and make a list out
of it.
Good Luck

"Connie Martin" wrote:

I need a list of all sales orders for that name (customer). I will actually
have several columns in this spreadsheet that will pick out information from
a huge spreadsheet. I'm working on one column at a time. If I can get this
column of info right, the rest should be a breeze. I could use Filter on the
huge spreadsheet and hide unwanted columns, etc. That's an option, but I
sure would like this smaller one to work, because if so, then I could do it
for all customers. But maybe I'm asking too much. Don't know. Thank you
for responding. Connie

"Chris T-M" wrote:

Perhaps you could explain what you are trying to do with the returned data
from Column D. I'm guessing you want to add them, multiply them, count the
matches, make a list of them... Perhaps you can get a better function
recommendation that way.

I'm not familiar with INDEX or SMALL, so if you get responses there I'll bow
out.

FYI: VLOOKUP only returns the first match in the array. That's why you kept
getting the same match or NA.

"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

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