Do i need Vlookup or Indirect or something else?

[MICHAELSNOWDON]

I have a simple workbook with two sheets.

Sheet 1 contains:

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64"]NAME[/TD]
[TD="class: xl63, width: 64"]AGE[/TD]
[TD="class: xl63, width: 64"]HEIGHT[/TD]
[TD="class: xl63, width: 64"]WEIGHT[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]PETER[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD]LUCY[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]86[/TD]
[/TR]
[TR]
[TD]DANIEL[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 contains a message: Choose a person
I then has a dropdown/combo box (data validation list, the source being the list of names from sheet 1)

So sheet 2 looks like:

[TABLE="width: 218"]
<tbody>[TR]
[TD]Choose person:[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD="align: right"]=Sheet1!B2[/TD]
[/TR]
[TR]
[TD]Height[/TD]
[TD="align: right"]=Sheet1!C2[/TD]
[/TR]
[TR]
[TD]Weight[/TD]
[TD="align: right"]=Sheet1!D2[/TD]
[/TR]
</tbody>[/TABLE]

But obviously it be dynamic and choose relevant row instead of just row 2.



When user selects a name from the menu, the cells next to age, height and weight on sheet 2 should grab relevant data from sheet 1.

Please help with the formulas I need to do this.

 

[steve the fish]

Hi. Try like this:

=VLOOKUP($B$1,Sheet1!$A$1:$D$5,MATCH(A3,Sheet1!$A$1:$D$1,0),0)

The headers in your lookup table must be the same as the headers in Sheet2 (Age etc.)

 

[sandy666]

or use Pivot Table

 

[mohadin]

One more option

=INDEX(Sheet1!$A$2:$D$5;MATCH($B$1;Sheet1!$A$2:$A$5);ROW()-1)

 

[MICHAELSNOWDON]

Thanks for your suggestions. The problem is, the spreadsheet I have is much more complicated than my example and so the headers cannot be the same. Is there a way of posting attachments on here?

 

[sandy666]

post a link to the shared Excel FILE with representative example of data using googledrive, onedrive , dropbox or any similar