Excel Vlookup to return multiple rows

[davedave24]

Hi. I need to pull multiple rows from a sheet that have a certain name.

Example sheet:

Paul    5
Mark    10
Mark    7
Paul    23
Paul    8
Sarah   89
Mark    63
Sarah   7
Paul    19

What I need to accomplish on the next sheet:

Paul        Mark       Sarah
5           10         89
23          7          7
8           63
19

I've been playing with Vlookup but I don't think it will work as it's not searching for a unique term. What's the best way to do this?

 

[SkipVought]

hi,

If your source table is SORTED in order by NAME, then the OFFSET() function can return a RANGE that defines the values for each NAME, also using the MATCH() function to define the row offset and COUNTIF() function to define the row count for any Name.

Then, that OFFSET() formula in the INDEX() function and the ROW() to index the row offset within the range for that Name.

You must read and understand the HELP on each of these functions that you are not familiar with.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's an Example that might help

[b]
Name   Val[/b]
Mark    10
Mark     7
Mark    63
Paul     5 <--+ [highlight]4[/highlight][b]=MATCH("Paul",A:A,0)[/b]
Paul    23    |
Paul     8    | [highlight]4[/highlight]=[b]COUNTIF(A:A,"Paul")[/b]
Paul    19 <--+
Sarah   89
Sarah    7

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

also a simple macro can do this as well without rearranging your data

in Range C1 of spreadsheet insert =COUNTA(A:A)

and have the following macro

sub names()
x = Range("c1")
For i = 1 To 3
a = 2
n$ = Choose(i, "Mark", "Paul", "Sarah")
Worksheets("Sheet2").Select
Range(Chr$(64 + i) & 1) = n$
Worksheets("Sheet1").Select
For y = 1 To x
If Range("A" & y) = n$ Then
Z = Range("b" & y)
Worksheets("Sheet2").Select
Range(Chr$(64 + i) & a) = Z
a = a + 1
Worksheets("Sheet1").Select
End If
Next y
Next i