Looking up and displaying data tables based on a match criteria

Hi all,
Would love some help with this INDEX/LOOKUP/MATCH displaying information issue I am struggling with.


I have a 3x3 Display Table into which I will place formulas:

DISPLAY
Input cell (E1): name
A B C
1
2
3

I have 3 x 3x3 'People' Tables
John
A B C
5 XYZ
6 YZX
7 YYZ

Peter
A B C
9 TUV
10 UVT
11 TTV

Paul
A B C
13 MNO
14 OON
15 MOO


When the name is entered into the input cell E1, I want the information from the cells in that corresponding persons table to appear in the Display Table.
When a different name is entered the displayed data would change.

I am happy to add some 'helper' cells in order to make this happen, I just can't get my head around the indexing that may be necessary.

Thanks so much all.

Thanks for the rep. Glad that we could help.

This worked great =INDEX($D$10:$D$26,MATCH($E$1,$C$8:$C$24,0)+ROWS($D$3:$D3)-1)

BUT I wonder if I can throw a curve ball at it.
I have tried to follow similar logic, but I can't make the translation successfully.

Book attached.

What happens if you have a data table
2010 2011 2012
A B C D E F G H I
1 2 3 3 2 1 2 3 1

So 3 columns of data for each of the years, listed linearly.

Input cell : YEAR
Output table:
3 2 1

When you enter the year into the input box, it displays only the data from that specific year in the output table (which can then drive a graph)

Can the same index matching process work for this scenario?

Thanks

Merged cells tend to cause issues.

If you write out the year in each cell (I put the years in H3:P3), you can use this:

C3 =INDEX($H$6:$P$8,MATCH($B3,$G$6:$G$8,0),INDEX(MATCH($E$1&C$2,$H$3:$P$3&$H$5:$P$5,0),0))

Drag the formula to the right and down.

Here's an alternative that does not require any changes to the formatting:

=INDEX($H$6:$P$8,MATCH($B3,$G$6:$G$8,0),MATCH($E$1,$H$4:$P$4,0)+COLUMNS($A1:A1)-1)

Perfect. Astounded as ever by the communities generosity and genius' Thank you

You're welcome. Glad we could help.