Lookup, Match, Index help

[OttoMan]

Hello All,

In my file I have two sheets. In the summary sheet, C1:C25 has the top 25 user ranks (numbers 1 to 25) for a particular week (52 weeks in total). The Data sheet has all the data for the 52 weeks for 1000 users. Range A1 to A1001 has their unique ID numbers. B1:BA1 has the column headers (Week1, Week2, Week3 etc) and under each Week is their rank for the week (1 to 1000).
Without using macros, I want to populate the Top 25 user ID's (from the data sheet range A1:A1001) in the summary sheet automatically by specifying a week number. For example if I type Week48, it should look at column AW1:AW1001 and identify the relevant rank from the summary sheet (numbers 1 to 25) and match that to the ID's in A1:A1001 and populate the summary sheet with that ID.

Any help is greatly appreciated,..

Thanks heap...

 

[DonkeyOte]

Assuming:

Summary Sheet:
A1 = week variable
B1 = MATCH(A1,DATA!$1:$1,0) (for ease)

C1
=INDEX(DATA!$A$1:$AI$1001,MATCH(ROW(C1),OFFSET(DATA!$A$1,0,$B$1-1):OFFSET(DATA!$A$1001,0,$B$1-1),0),1)

would return ID of rank 1 in that week

Copy C1 to C25 and you would get remaining ID's in position.

There is a more elegant way I'm sure...

 

[Peter_SSs]

OttoMan

Welcome to the MrExcel board!

A smaller sample, but the same idea I believe. My assumption is that there are no equal ranks in any given week.

Data sheet:

Excel Workbook
	A	B	C	D	E
1	Unique ID	Week 1	Week 2	Week 3
2	A1	6	6	7
3	A2	2	1	5
4	A3	8	5	6
5	A4	1	4	3
6	A5	7	7	8
7	A6	3	2	1
8	A7	5	8	4
9	A8	4	3	2
10
Data

Summary sheet uses similar layout and helper cell as suggested by lasw10.
Formula in D1 copied down.

Excel Workbook
	A	B	C	D	E
1	Week 2	3	1	A2
2			2	A6
3			3	A8
4			4	A4
5
Summary

 

[OttoMan]

Thank you soo much,... Lasw10 & Peter_SSs, you guys are brilliant,..