How to pick up second and third values using a vlookup??

[bat18]

I am trying to create a horizontal list of data, the problem is a vlookup will only give me the first bit of information that appears. I need a list of information.

data is along these lines

Name Time

Dave .567
John .897
Thom .987
John .876
John .765
Dave .987
John .908

So in another spreadsheet i have the names John, Dave and Thom down column A, I would like to pick up all the values associated with each name and list them across their particular row.

Hope this makes sense

Thanks

 

[T. Valko]

I am trying to create a horizontal list of data, the problem is a vlookup will only give me the first bit of information that appears. I need a list of information.

data is along these lines

Name Time

Dave .567
John .897
Thom .987
John .876
John .765
Dave .987
John .908

So in another spreadsheet i have the names John, Dave and Thom down column A, I would like to pick up all the values associated with each name and list them across their particular row.

Hope this makes sense

Thanks

Try this...

Let's assume your data is on Sheet1 in the range A2:B8.

In the formulas I use these named ranges:

• Names
• Refers to: =Sheet1!$A$2:$A$8
• Values
• Refers to: =Sheet1!$B:$B

On Sheet2, in the range A2:A4 you have the unique names listed.

Enter this formula in B2. This will return a count for how many times each name is listed on Sheet1.

=COUNTIF(Names,A2)

Copy down to B4.

Enter this array formula** in C2. This will return the values from Sheet1 column B.

=IF(COLUMNS($C2:C2)>$B2,"",INDEX(Values,SMALL(IF(Names=$A2,ROW(Names)),COLUMNS($C2:C2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down to C4 then across until you get a solid column of blank cells.

 

[bat18]

Works perfectly

Cheers

 

[T. Valko]

Works perfectly

Cheers

You're welcome. Thanks for the feedback!