Vlookup - Multiple Values

[whhaatt]

I am trying to use a Vlookup, in a Spreadsheet that has 15000 records. Column A has account numbers (which are repeated a few times) and Column B has person names.

What I am trying to do is Vlookup repeat account numbers and show the person linked to that account in the form of a row.

Vlookup how ever looks at the first Value search only, Is it possible to use mulitple values.

example: - Current Setup (Column C has formula =VLOOKUP(A2,A2:B8,2,FALSE)

A B C
account
123 Joe bloggs Joe bloggs
234 P Smith P Smith
546 D Johnson D Johnson
123 Fred Bloggs Fred Bloggs
223 C Smith C Smith
123 K Bloggs K Bloggs
234 D Smith D Smith


Required Setup

A B C D E
123 Joe bloggs Joe bloggs Fred Bloggs K Bloggs
234 P Smith P Smith D Smith
546 D Johnson D Johnson
123 Fred Bloggs Fred Bloggs
223 C Smith C Smith
123 K Bloggs K Bloggs
234 D Smith D Smith

Help someone

 

[JamesMcS]

As far as I know vlookup will only pull back the first value it finds.... interested to see if soemone who knows what they're talking about has a solution!

 

[namliam]

It can be done, though it gets ugly quick...
Assuming a table like:

1	a
2	b
3	c
3	d
3	e
4	f
5	g
6	h

Now assuming in F3 you have the value 3 you want to lookup:
=VLOOKUP($F3,OFFSET($A$1:$B$10,MATCH($F3,$A:$A,0),0,10,2),2,FALSE)
This function will return "d"

For the next value you then have to next the OFFSET into the MATCH and repeat that for however many options you want to have.

Assuming it is sorted by number...
In column K, put this formula:
=MATCH(J1,A:A,0)

Where column J holds your unique values, column K will then have the "rownumber" where to find the first match.
Then Column L (first value) :
=IF(OFFSET($A1,$K1-1,0,1,1)=$J1,OFFSET($A1,$K1-1,1,1,1),"")
Column M (second value):
=IF(OFFSET($A1,$K1+0,0,1,1)=$J1,OFFSET($A1,$K1+0,1,1,1),"")
Column N (Third value):
=IF(OFFSET($A1,$K1+1,0,1,1)=$J1,OFFSET($A1,$K1+1,1,1,1),"")
Column O (Fourth value):
=IF(OFFSET($A1,$K1+2,0,1,1)=$J1,OFFSET($A1,$K1+2,1,1,1),"")
etc...

Or if you prefer it all in one, for the first... change the rest accoordingly:
=IF(OFFSET($A1,MATCH(J1,A:A,0)-1,0,1,1)=$J1,OFFSET($A1,MATCH(J1,A:A,0)-1,1,1,1),"")

 

[whhaatt]

Thanks namliam

That is to much coding for me, as i need to concatanate cells at a later stage, and record all this in a macro. This file needs to be run every week.

I am not that bothered if i use vlookup, any other function/macro will do, as long as the required output works.

Any ideas?

 

[chergh]

Well you could use a special filter to create a unique list of account numbers, then use the output of the special filter to create a loop for an auto filter and then copy and paste and transpose the names into a row. But I'm guessing that will be to complicated for you to code, and I'm to busy to write it for you.

 

[namliam]

How about using a pivot table...

1 a
2 b
3 c
  d
  e
4 f
5 g

Then using some concatination wouldnt be to hard....

 

[Brianwarnock]

I can honestly say that I don't understand your data, and how it went from the current to the desired. In the current each name appears twice, in the desired the first name appears twice and then other names once, and the accounts still appear multiple times !!

Brian