INDEX MATCH MATCH based on 2 dynamic columns

[Toxic9]

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
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ID[/TD]
[TD]Q1[/TD]
[TD]Q1a[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]EMAIL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]4[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]bob@notreal.com[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]7[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]jane@notreal.com[/TD]
[/TR]
</tbody>[/TABLE]

Export 2
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]EMAIL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]jane@notreal.com[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]bob@notreal.com[/TD]
[/TR]
</tbody>[/TABLE]


I need a formula that will work in both scenarios to populate this table:
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]EMAIL[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bob@notreal.com[/TD]
[TD]Formula here[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]jane@notral.com[/TD]
[TD]Formula here[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Marcelo Branco]

Say you have this in Sheet1

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

ID​

[/td][td]

Q1​

[/td][td]

Q1a​

[/td][td]

Q2​

[/td][td]

Q3​

[/td][td]

EMAIL​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

Yes​

[/td][td]

4​

[/td][td]

No​

[/td][td]

Yes​

[/td][td]

bob@notreal.com​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

Yes​

[/td][td]

7​

[/td][td]

Yes​

[/td][td]

No​

[/td][td]

jane@notreal.com​

[/td][/tr]
[/table]

Then on another sheet

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

EMAIL​

[/td][td]

Q2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

bob@notreal.com​

[/td][td]

No​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

jane@notreal.com​

[/td][td]

Yes​

[/td][/tr]
[/table]

Formula in B2 copied down
=INDEX(Sheet1!A:F,MATCH(A2,Sheet1!F:F,0),MATCH(B$1,Sheet1!$A$1:$F$1,0))

Adjust the ranges to get the proper values on Sheet2

M.

 

[Toxic9]

Appreciate the swift help.

That will work when EMAIL is fixed in column F, but won't work in the second scenario where EMAIL is now in column D. I'm looking for a formula that won't require me changing it each time I bring a new export into Sheet 1.

 

[Aladin Akyurek]

Perhaps...

=VLOOKUP($A2,CHOOSE({1,2},INDEX(A:XFD,0,MATCH("email",INDEX(A:XFD,1,0)),INDEX(A:XFD,0,MATCH("Q2",INDEX(A:XFD,1,0),0))),2,0)

 

[markmzz]

Perhaps...

=VLOOKUP($A2,CHOOSE({1,2},INDEX(A:XFD,0,MATCH("email",INDEX(A:XFD,1,0)),INDEX(A:XFD,0,MATCH("Q2",INDEX(A:XFD,1,0),0))),2,0)

Try this suggestion with names.

1) Create the names below:

Export1 - Refers to: $B$2:$F$4

Export2 - Refers to: $B$7:$D$9

2) Put the formula below in B14 and copy down and to the right:

=IFERROR(INDEX(INDIRECT(B$13),
MATCH($A14,INDEX(INDIRECT(B$13),,MATCH($A$13,INDEX(INDIRECT(B$13),1,),0)),0),
MATCH($B$12,INDEX(INDIRECT(B$13),1,),0)),"")


[TABLE="class: grid, width: 607"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Export 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ID[/TD]
[TD]Q1[/TD]
[TD]Q1a[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]EMAIL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1[/TD]
[TD]Yes[/TD]
[TD="align: right"]4[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]bob@notreal.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD="align: right"]7[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]jane@notreal.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Export 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ID[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]EMAIL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]1[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]jane@notreal.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]bob@notreal.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]I need a formula that will work in both scenarios to populate this table:[/TD]
[TD]Q2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]EMAIL[/TD]
[TD]Export1[/TD]
[TD]Export2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]bob@notreal.com[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]jane@notral.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]******************[/TD]
[TD]*******[/TD]
[TD]********[/TD]
[TD]******************[/TD]
[TD]*******[/TD]
[TD]******************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[Toxic9]

Will either of these work if I have a 3rd scenario with Q2 in column AA and email in column AFD. Or a 4th scenario with Q2 in AZ and EMAIL in XX. Or any other number of scenarios where either Q2 or EMAIL can be in any column? I'd like to remove the need to fix reference any column or range (could be 10 rows or 1048575 or anything between) for as many scenarios as excel has.

Thanks
T

 

[Toxic9]

A little further info: I want a formula that says:
1) Find the column where the email addresses are
2) Find a certain email address in that column
3) Find the column where the answers to Q2 are
4) Finally find the answer from the corresponding the email address.

Thanks

 

[Marcelo Branco]

A little further info: I want a formula that says:
1) Find the column where the email addresses are
2) Find a certain email address in that column
3) Find the column where the answers to Q2 are
4) Finally find the answer from the corresponding the email address.

Thanks

Using the data sample in post 2, try (assumes headers in row 1 of Sheet1)

On another sheet
Formula in B2 copied dowm
=INDEX(INDEX(Sheet1!$A:$Z,0,MATCH(B$1,Sheet1!$A$1:$Z$1,0)),MATCH(A2,INDEX(Sheet1!$A:$Z,0,MATCH(A$1,Sheet1!$A$1:$Z$1,0)),0))

M.

 

[Aladin Akyurek]

A little further info: I want a formula that says:
1) Find the column where the email addresses are
2) Find a certain email address in that column
3) Find the column where the answers to Q2 are
4) Finally find the answer from the corresponding the email address.

Thanks

That's exactly what the formula I suggested does, so does Marcelo's.

 

[markmzz]

A little further info: I want a formula that says:
1) Find the column where the email addresses are
2) Find a certain email address in that column
3) Find the column where the answers to Q2 are
4) Finally find the answer from the corresponding the email address.
Thanks

Hi Toxic9,

Try this small modification in Aladin's suggestion:

1) Give the name Export1 and Export2 to the sheets with the export data (important: with the headers in the row one).

2) In the Sheet1, put the formula below in B3 and copy down and to the right:

=IFERROR(INDEX(INDIRECT("'"&B$2&"'!A:XFD"),
MATCH($A3,INDEX(INDIRECT("'"&B$2&"'!A:XFD"),0,MATCH($A$2,INDEX(INDIRECT("'"&B$2&"'!A:XFD"),1,0),0)),0),
MATCH($B$1,INDEX(INDIRECT("'"&B$2&"'!A:XFD"),1,0),0)),"")<strike></strike>


[TABLE="class: grid, width: 1170"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]I need a formula that will work in both scenarios to populate this table:[/TD]
[TD]Q2[/TD]
[TD][/TD]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Q1[/TD]
[TD]Q1a[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]EMAIL[/TD]
[TD]Export1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]EMAIL[/TD]
[TD]Export2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EMAIL[/TD]
[TD]Export1[/TD]
[TD]Export2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD="align: right"]1[/TD]
[TD]Yes[/TD]
[TD="align: right"]4[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]bob@notreal.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD="align: right"]1[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]jane@notreal.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bob@notreal.com[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD="align: right"]7[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]jane@notreal.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]bob@notreal.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]jane@notreal.com[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**************************[/TD]
[TD]********[/TD]
[TD]********[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]****[/TD]
[TD]*****[/TD]
[TD]****[/TD]
[TD]****[/TD]
[TD]***************[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]****[/TD]
[TD]*****[/TD]
[TD]***************[/TD]
[TD]*******[/TD]
[/TR]
</tbody>[/TABLE]


Do some test.

Markmzz