v lookup

jaybee7171

New Member
Joined
Nov 18, 2005
Messages
2
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 .
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Crow_23

Board Regular
Joined
Feb 17, 2005
Messages
183
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try this:

Data Source Sheet (original)
Indexing.xls
ABCD
1ID #ClientRev
21abc1000
31def2000
41gef3000
52asdf4000
62ewr5000
73asdv600
Source


ID # 1 Sheet:
Indexing.xls
ABCD
1IDClientRev
21abc1000
3def2000
4gef3000
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,499
Members
412,670
Latest member
Khin Zaw Htwe
Top