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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]John[/td][td][/td][td]Brad[/td][td]C2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

245​

[/td][td][/td][td="bgcolor:#CCFFCC"]

148​

[/td][td="bgcolor:#CCFFCC"]C3:C5: {=INDEX(A2:A13, MATCH(C2, A2:A13, 0) + 1):INDEX(A2:A13, MATCH(C2, A2:A13, 0) + 3)}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

345​

[/td][td][/td][td="bgcolor:#CCFFCC"]

255​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

615​

[/td][td][/td][td="bgcolor:#CCFFCC"]

619​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Brad[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

148​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

255​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

619​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Lisa[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

550​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

210​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

125​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[sandy666]

another way with PowerQuery aka Get&Transform:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#5B9BD5]List[/td][td][/td][td=bgcolor:#70AD47]Number[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John[/td][td][/td][td=bgcolor:#FFFF00]Mike[/td][td][/td][td=bgcolor:#E2EFDA]

123​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

245​

[/td][td][/td][td][/td][td][/td][td]

456​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

345​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

789​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

615​

[/td][td][/td][td][/td][td][/td][td]

135​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Brad[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

246​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

148​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

255​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

619​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]555[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Lisa[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

550​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

210​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mike[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]123[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

456​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

789​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

135​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

246​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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.