I need a formula to return a column of data from sheet1 into sheet 2

I need a formula to pull a column of data from sheet 1 to sheet 2 when a certain text is entered into a cell.
If b2 on sheet 2 = "AA" i would like A2:10 to return the information from sheet 1 A2:10 and if "BB" then A2:10 then return sheet 1 B2:10 and so forth. I will need to be able to return data from at least ten different columns on sheet 1 but only one at a time.
Ant help would be greatly appreciated and thank you in advance

Originally Posted by FDibbins

Hi DustOfTheEarth and welcome to the forum

If you can give each of the columns on sheet1 the same heading as you want to use (AA, BB etc), then you can use this on sheet2, copied down...

=INDEX(Sheet1!$A$1:$D$11,ROW(Sheet1!A2),MATCH($B$2,Sheet1!$A$1:$D$1,0))

Thank you so VERY MUCH!!!
It worked perfectly!