Hi all,
I am attempting to extract full columns of values only when the column header cell contains specific text in a large matrix where those headers are repeated (due to testing under multiple conditions.)
I have attached an example sheet. I would like to make the measures contiguous (e.g. intensity together, phase together, etc.). I have attempted a combination of Match and Index seen in the sheet, =INDEX($A$3:$I$14,,MATCH($K$17,$A$2:$I$2,0)), but this only works once (or twice with relative referencing) due to the fact that the headers are exactly the same between conditions and it continues to just grab the first instance of matching. Unfortunately, as you can see in the sheet, there is no row identifying the condition with which the repeated measures are associated, so this cannot serve as an additional reference.
Is there anyway to make MATCH pull the position of 2nd, 3rd, 4th, matches and feed that to INDEX, or any other work around for this? Thanks so much for any help.
I am attempting to extract full columns of values only when the column header cell contains specific text in a large matrix where those headers are repeated (due to testing under multiple conditions.)
I have attached an example sheet. I would like to make the measures contiguous (e.g. intensity together, phase together, etc.). I have attempted a combination of Match and Index seen in the sheet, =INDEX($A$3:$I$14,,MATCH($K$17,$A$2:$I$2,0)), but this only works once (or twice with relative referencing) due to the fact that the headers are exactly the same between conditions and it continues to just grab the first instance of matching. Unfortunately, as you can see in the sheet, there is no row identifying the condition with which the repeated measures are associated, so this cannot serve as an additional reference.
Is there anyway to make MATCH pull the position of 2nd, 3rd, 4th, matches and feed that to INDEX, or any other work around for this? Thanks so much for any help.
| Examplehelp.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | Q1 | Q5 | Q6 | Q19 | Q50 | Q65 | Q70 | Q71 | Q75 | ||||||||||||
| 2 | Intensity | Phase | Latency | Intensity | Phase | Latency | Intensity | Phase | Latency | ||||||||||||
| 3 | 8 | 6 | 6 | 6 | 5 | 8 | 6 | 6 | 5 | ||||||||||||
| 4 | 7 | 1 | 7 | 7 | 8 | 6 | 3 | 9 | 0 | ||||||||||||
| 5 | 8 | 7 | 8 | 2 | 3 | 5 | 3 | 7 | 1 | ||||||||||||
| 6 | 5 | 0 | 6 | 6 | 5 | 5 | 3 | 10 | 0 | ||||||||||||
| 7 | 5 | 2 | 3 | 3 | 7 | 0 | 0 | 9 | 6 | ||||||||||||
| 8 | 6 | 3 | 9 | 6 | 4 | 7 | 3 | 7 | 7 | ||||||||||||
| 9 | 7 | 7 | 3 | 0 | 10 | 3 | 2 | 8 | 5 | ||||||||||||
| 10 | 5 | 0 | 4 | 6 | 5 | 1 | 3 | 7 | 3 | ||||||||||||
| 11 | 5 | 5 | 7 | 2 | 3 | 6 | 0 | 6 | 5 | ||||||||||||
| 12 | 4 | 6 | 4 | 4 | 6 | 4 | 3 | 8 | 5 | ||||||||||||
| 13 | 3 | 4 | 3 | 2 | 9 | 1 | 0 | 8 | 7 | ||||||||||||
| 14 | 5 | 1 | 8 | 0 | 8 | 5 | 4 | 3 | 1 | ||||||||||||
| 15 | |||||||||||||||||||||
| 16 | |||||||||||||||||||||
| 17 | Intensity | Phase | Latency | ||||||||||||||||||
| 18 | 8 | ||||||||||||||||||||
| 19 | 7 | ||||||||||||||||||||
| 20 | 8 | ||||||||||||||||||||
| 21 | 5 | ||||||||||||||||||||
| 22 | 5 | ||||||||||||||||||||
| 23 | 6 | ||||||||||||||||||||
| 24 | 7 | ||||||||||||||||||||
| 25 | 5 | ||||||||||||||||||||
| 26 | 5 | ||||||||||||||||||||
| 27 | 4 | ||||||||||||||||||||
| 28 | 3 | ||||||||||||||||||||
| 29 | 5 | ||||||||||||||||||||
Sheet1 | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K18:K29 | K18 | =INDEX($A$3:$I$14,,MATCH($K$17,$A$2:$I$2,0)) |
| Dynamic array formulas. | ||
