Excel Workbook | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | M | N | |||
5 | 1-Jan | 2-Jan | 3-Jan | 4-Jan | 5-Jan | 6-Jan | 7-Jan | 3-Jan | ||||
6 | ||||||||||||
7 | ||||||||||||
8 | JOHN | A | A | A | A | JOHN | ||||||
9 | ALEX | B | B | B | B | B | ANDREW | |||||
10 | ANDREW | A | A | A | A | |||||||
11 | PETER | B | B | B | B | |||||||
12 | MATHEW | B | B | B | B | B | ||||||
13 | MIKE | D | D | D | D | D | D | |||||
14 | WERNER | D | D | D | D | |||||||
15 | FERRY | |||||||||||
16 | ||||||||||||
Sheet1 |
=INDEX($17:$17;COLUMN($O$1)+LARGE(INDEX(ISNUMBER(MATCH(OFFSET($P$1:$IV$1;MATCH($C$5;$N$3:$N$2000;0)+1;;);$D$5:$D$11;0))*(COLUMN($P$1:$IV$1)-COLUMN($O$1));0);ROWS($5:16)))
Please see my proposed solution at the link to the other forum.I opened a similar thread on the following forum]