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 .
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,685
Messages
6,120,876
Members
448,993
Latest member
InquisitiveFrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top