# vlookup offset or index or ????? (better file example)

#### elew69811

##### Board Regular
On sheet 1 in cell B1 I am looking for a formula that will return what is in B1 on sheet 2 and in cell B2 on sheet 1 the formula should return what is in cell B9 on sheet 2 and in B3 on sheet 1 what is in cell B17 on sheet 2 and can be copied down so each cell down on sheet 1 looks down 8 more rows

SHEET 1
A B C D E F G H I J K L M N
1 Jeff Paul Harry Steve Tim Mark Dave
2 Jeff Paul Harry Steve Tim Mark Dave
3 Jeff Paul Harry Steve Tim Mark Dave
4 Jeff Paul Harry Steve Tim Mark Dave
5 Jeff Paul Harry Steve Tim Mark Dave
6 Jeff Paul Harry Steve Tim Mark Dave

SHEET 2
A B
1 Jeff \$11,459
2 Paul \$10,688
3 Harry \$13,868
4 Steve \$10,758
5 Tim \$9,762
6 Mark \$9,045
7 Dave \$11,774
8 George \$12,695
9 Jeff \$11,460
10 Paul \$12,440
11 Harry \$10,472
12 Steve \$11,455
13 Tim \$12,140
14 Mark \$13,454
15 Dave \$13,543
16 George \$11,288
17 Jeff \$10,115
18 Paul \$11,630
19 Harry \$10,787
20 Steve \$15,259
21 Tim \$14,777
22 Mark \$12,762
23 Dave \$13,775
24 George \$14,726
25 Jeff \$14,379

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### bosco_yip

##### Well-known Member
Try,

In Sheet1 B1, formula copy into right D1, F, H1, J1, L1, N1 and all copy down :

=IFERROR(INDEX(Sheet2!\$B\$1:\$B\$100,AGGREGATE(15,6,ROW(Sheet2!\$A\$1:\$A\$100)/(Sheet2!\$A\$1:\$A\$100=A1),ROWS(\$1:1))),"")

Regards
Bosco

#### elew69811

##### Board Regular
Thanks for the effort Bosco. I made progress in that I was able to enter the formula without getting error messages but it did not return anything. I will try substituting A's for B's and see if that helps

Replies
3
Views
431
Replies
6
Views
645
Replies
5
Views
6K

1,191,231
Messages
5,985,418
Members
439,963
Latest member
Triarch

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