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

[elew69811]

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

 

[bosco_yip]

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]

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