Excel 2007 : How to Look Up Entire Columns

[scovachev]

Hi,

I am having a problem with using excel for my dissertation. I uploaded the spreadsheet to demonstrate.

In the "All periods" worksheet, I have company data for two sub periods (originally more, but two are enough for this example) -1987-1989 and 1990-1992. In the F and T 1 worksheet I copied the data from the first sub period and used the sort function to rank the companies according to the sums of each column (contained in the last row of the data). Then I copied the names of the best 20 companies and worst 20 companies below. Now I need to look up the entire columns of data for those companies but for the second sub period (1990-1992) using the data from the All periods worksheet. How can I do this fast? I would much appreciate any help.

[NBVC]

If the dates are also mixed up in the All Periods sheet use:

=INDEX('All periods'!$B$42:$MJ$77,MATCH($A45,'All periods'!$A$42:$A$77,0),MATCH(B$44,'All periods'!$B$41:$MJ$41,0))

otherwise you can even use:

=INDEX('All periods'!$B$42:$MJ$77,ROWS($A$45:$A45),MATCH(B$44,'All periods'!$B$41:$MJ$41,0))

either copied down and across

[scovachev]

Thanks for helping. I tried the shorter formula and it worked.