Microsoft Excel - How can you select and display contents of cells in cells in an array?

If I have a set of data on soccer players such as the following:

Country	Player	Goals	Fouls
Brazil	Player 1	2	0
Brazil	Player 2	3	3
Brazil	Player 3	4	2
Brazil	Player 4	5	1
Brazil	Player 5	6	1
Brazil	Player 6	7	0
Brazil	Player 7	8	3
Brazil	Player 8	3	0
Brazil	Player 9	5	0
Brazil	Player 10	8	3
Germany	Player 1	9	1
Germany	Player 2	4	6
Germany	Player 3	7	5
Germany	Player 4	8	6
Germany	Player 5	6	1
Germany	Player 6	2	1

And I want to select and display all of the Germany player data in another range of cells elsewhere on the spreadsheet.  How would I do that?

Simon

 

Hello Simon,
One way would be to set up a Pivot Table.  This is probably the easiest.
If you wanted to use formulae, assuming that your table starts in cell A1, the following in M2/N2,O2 and copied down would work.
=OFFSET($A$1,MATCH("Germany",$A:$A,0)-1+ROW()-ROW(M$2),1)
=OFFSET($A$1,MATCH("Germany",$A:$A,0)-1+ROW()-ROW(M$2),2)
=OFFSET($A$1,MATCH("Germany",$A:$A,0)-1+ROW()-ROW(M$2),3)
You could make it more general by substituting a cell reference for the string and entering the country into that cell or by setting up a drop-down list of the country names.
Hope this helps but get back if you need more.
Harry

Thank you, i'll give it a try.

Simon