What I would like to do is give me the results for each person (A,B,C...) based on which test we are looking at. In cell B14, I would like to put in the number of which test and have it use a formula to tell me which result. In the current example, I show test 1 being used in B14 and below are what I would like to auto populate using a formula. Since each person can only qualify for one option and after that option is used, no other person can be that for that specific test. (make sense?)
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | Test Group | 1 | 2 | 3 | 4 | 5 | 6 |
2 | A | RCF | Unclear | RCF | RCF | RCF | RCF |
3 | B | CA | CA | CA | CA | Unclear | CA |
4 | C | PT | Unclear | PT | PT | PT | Unclear |
5 | D | TRD | TRD | Unclear | TED | TRD | TRD |
6 | E | LCF | LCF | Unclear | LCF | LCF | LCF |
7 | F | FST | FST | FST | FST | Unclear | FST |
8 | G | SHS | PT | TRD | Unclear | FST | SHS |
9 | H | RF | RF | RF | Unclear | RF | RF |
10 | I | LF | SHS | Unclear | LF | SHS | Unclear |
11 | |||||||
12 | |||||||
13 | |||||||
14 | Test | 1 | |||||
15 | |||||||
16 | |||||||
17 | A | RCF | |||||
18 | B | CA | |||||
19 | C | PT | |||||
20 | D | TRD | |||||
21 | E | LCF | |||||
22 | F | FST | |||||
23 | G | SHS | |||||
24 | H | RF | |||||
25 | I | LF |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3