# Lookup, Match, Index help

#### OttoMan

##### New Member
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...

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### DonkeyOte

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
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
ABCDE
1Unique IDWeek 1Week 2Week 3
2A1667
3A2215
4A3856
5A4143
6A5778
7A6321
8A7584
9A8432
10
Data

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

Excel Workbook
ABCDE
1Week 231A2
22A6
33A8
44A4
5
Summary

Last edited:

#### OttoMan

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

Replies
1
Views
354
Replies
1
Views
239
Replies
0
Views
134
Replies
15
Views
917
Replies
1
Views
158

1,190,558
Messages
5,981,688
Members
439,730
Latest member
gjvv

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back