Looking for help with a lookup. Happy 4th!

[xoenix]

Okay, this is probably something simple but I cannot figureit out. I have a single column of data – approximately 9,000 cells in length. Itfollows the same format. A person’s name followed by 3 values. Can I use alookup function that will return the same 3 values that follow each person’sname? For example, if I select John from a drop down, is there a formula thatwill return the 3 values below john’s name? Same deal with any name. Any help wouldbe greatly appreciated. I just cannot figure it out. Thanks.

John
245
345
615
Brad
148
255
619
Lisa
550
210
125

 

[mumps]

I'm not sure about a formula but a macro can do this for you. In which column is your data? In which cell is the drop down and where do you want to return the 3 values?

 

[shg]

	A​	B​	C​	D​
1​
2​	John		Brad	C2: Input
3​	245​		148​	C3:C5: {=INDEX(A2:A13, MATCH(C2, A2:A13, 0) + 1):INDEX(A2:A13, MATCH(C2, A2:A13, 0) + 3)}
4​	345​		255​
5​	615​		619​
6​	Brad
7​	148​
8​	255​
9​	619​
10​	Lisa
11​	550​
12​	210​
13​	125​

 

[sandy666]

another way with PowerQuery aka Get&Transform:

raw		List		Number
John		Mike		123​
245​				456​
345​				789​
615​				135​
Brad				246​
148​
255​
619​
555
Lisa
550​
210​
Mike
123
456​
789​
135​
246​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    Number = Table.AddColumn(Type, "Number", each try Number.FromText([raw]) otherwise null),
    Name = Table.AddColumn(Number, "Name", each if [Number] = null then [raw] else null),
    FillD = Table.FillDown(Name,{"Name"}),
    Filter = Table.SelectRows(FillD, each ([Number] <> null)),
    ROC = Table.SelectColumns(Filter,{"Number", "Name"})
in
    ROC[/SIZE]

[SIZE=1]// Table2 input or DV list
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]
in
    Source[/SIZE]

[SIZE=1]// Result
let
    Source = Table.NestedJoin(Table2,{"List"},Table1,{"Name"},"Table1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table1", {"Number"}, {"Number"}),
    ROC = Table.SelectColumns(Expand,{"Number"})
in
    ROC[/SIZE]

doesn't matter how many values will be below the name

 

[t0azt]

If your data is in column A and the drop down with the name is cell C1 you can use the following formulas:

=INDIRECT("A"&MATCH($C$1,$A$1:$A$16,0)+1)
=INDIRECT("A"&MATCH($C$1,$A$1:$A$16,0)+2)
=INDIRECT("A"&MATCH($C$1,$A$1:$A$16,0)+3)

Adjust your ranges accordingly.