VLOOKUP

[Connie Martin]

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 D25000 (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 Carey]

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)

 

[Niek Otten]

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

 

[Connie Martin]

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

 

[Connie Martin]

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

 

[Connie Martin]

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

 

[Jackson Martin]

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

 

[Duke Carey]

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]

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/e...=CL100570551033#Return multiple corresponding

Connie

 

[Connie Martin]

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/e...=CL100570551033#Return multiple corresponding

Connie

 

[Connie Martin]

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/e...=CL100570551033#Return multiple corresponding

Connie

 

[Chris T-M]

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]

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/e...=CL100570551033#Return multiple corresponding

Connie

 

[Connie Martin]

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

 

[Chris T-M]

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]

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]

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

 

[Jack]

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

 

[Jack]

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