Copy multiple data columns with the same key from another sheet excel

Question

I want to copy multiple columns with the same key from one Excel sheet to another.

Example:

Sheet 1:

123 | a | aa  
123 | b | bb  
123 | c | cc  
234 | d | dd  
234 | e | ee  
55  | f | ff  
123 | g | gg

Sheet 2:

One cell where I put my key; in this case 123; and I want to have every column from sheet 1 shown on sheet 2, with key 123.
So it has to look like this on sheet 2:

Key:123

automaticly shown:  
123 | a | aa  
123 | b | bb  
123 | c | cc  
123 | g | gg

I guess it is one of the easier things to do, but I am pretty new to Excel.
I do not want to use VBA code. I just want to use normal functions which you put in a cell, like IF etc.

Answer 1

Say we have data in Sheet1 like:

and we want to retrieve records with the value 123 in column A. We can use:

• AutoFilter to display only the 123 rows and then use Copy/Paste
• Use a VBA macro to do the Copy/Paste
• Use formulas to retrieve the rows

To implement the third method we need to:

1. first get the required row number(s)
2. then get the row's contents using INDEX()

In Sheet2 cell A1 enter the array formula:

=SMALL(IF(Sheet1!$A$1:$A$22=123,ROW($A$1:$A$22),9E+99),ROW())

and copy this cell downwards.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Then in Sheet2 cell B1 enter the normal formula:

=IF($A1>22,"",INDEX(Sheet1!A:A,$A1))

Finally copy B1 and paste to the rectangular area from B1 through D22

Sheet2 displays:

(ignore the junk appearing at the bottom of column A)

Answer 2

Insert->PivotTable. Put all three headers into row labels. Set filter on 1st col to be equal to 123.