I'm setting up a spreadsheet to process exports from an online survey tool. For the purpose of this post assume the column headings I need always have the same name and are always in Sheet 1. However, their column locations in each export can vary. For simplicity here are two examples:
Export 1
<tbody>
</tbody>
Export 2
<tbody>
</tbody>
I need a formula that will work in both scenarios to populate this table:
<tbody>
</tbody>
I've got as far as the following, but know the highlighted part is the problem.
{=INDEX('Sheet 1'!$1:$1048576, MATCH(A2,MATCH("EMAIL",'Sheet 1'!$1:$1,0),0), MATCH("Q2",'Sheet 1'!$1:$1,0))}
Any help would be much appreciated - Thanks.
Export 1
A | B | C | D | E | F | |
1 | ID | Q1 | Q1a | Q2 | Q3 | |
2 | 1 | Yes | 4 | No | Yes | bob@notreal.com |
3 | 2 | Yes | 7 | Yes | No | jane@notreal.com |
<tbody>
</tbody>
Export 2
A | B | C | D | |
1 | ID | Q1 | Q2 | |
2 | 1 | No | Yes | jane@notreal.com |
3 | 2 | Yes | Yes | bob@notreal.com |
<tbody>
</tbody>
I need a formula that will work in both scenarios to populate this table:
A | B | |
1 | Q2 | |
2 | bob@notreal.com | Formula here |
3 | jane@notral.com | Formula here |
<tbody>
</tbody>
I've got as far as the following, but know the highlighted part is the problem.
{=INDEX('Sheet 1'!$1:$1048576, MATCH(A2,MATCH("EMAIL",'Sheet 1'!$1:$1,0),0), MATCH("Q2",'Sheet 1'!$1:$1,0))}
Any help would be much appreciated - Thanks.