v lookup

[jaybee7171]

I am trying to populate several separate sheets with several lines of data from a separate data source, based on a person's id number. Not all individuals have the same number of records. For example:

data source:
id# client rev
1 abc 1000
1 def 2000
1 gef 3000
2 asdf 4000
2 ewr 5000
3 asdv 600

Sheet to populate:
id # 1


client rev
abc 1000
def 2000
ger 3000
______________________________________
need populated with
data from above. All
3 records.


I can use vlookup to populate the first record for person #1, but how do I get it to return the second, third and so on .

 

[Crow_23]

I have never had to use it before, but sounds like you need to look into VLookupnth. If I find anything interesting I will post back. Do the same if you find what you are looking for.

 

[NBVC]

Try this:

Data Source Sheet (original)

Indexing.xls
	A	B	C	D
1	ID #	Client	Rev
2	1	abc	1000
3	1	def	2000
4	1	gef	3000
5	2	asdf	4000
6	2	ewr	5000
7	3	asdv	600
Source

ID # 1 Sheet:

Indexing.xls
	A	B	C	D
1	ID	Client	Rev
2	1	abc	1000
3		def	2000
4		gef	3000
5
6
7
ID 1

Formula in B2:
=IF(ROW(Source!$B2)-ROW(Source!$B$1)>COUNTIF(Source!$A$1:$A$7,$A$2),"",INDEX(Source!B$1:B$7,SMALL(IF(Source!$A$1:$A$7=$A$2,ROW(Source!$B$1:$B$7)),ROW(Source!$B2)-ROW(Source!$B$1))))

Confirmed with CTRL+SHIFT+ENTER instead of Enter only

Then copied left and copied down as far as you want.

The input cell is A2, just enter the ID # here.