INDEX MATCH MATCH based on 2 dynamic columns

Toxic9

New Member
Joined
Apr 4, 2018
Messages
5
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
ABCDEF
1IDQ1Q1aQ2Q3EMAIL
21Yes4NoYesbob@notreal.com
32Yes7YesNojane@notreal.com

<tbody>
</tbody>

Export 2
ABCD
1IDQ1Q2EMAIL
21NoYesjane@notreal.com
32YesYesbob@notreal.com

<tbody>
</tbody>


I need a formula that will work in both scenarios to populate this table:
AB
1EMAILQ2
2bob@notreal.comFormula here
3jane@notral.comFormula 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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Say you have this in Sheet1

A
B
C
D
E
F
1
ID​
Q1​
Q1a​
Q2​
Q3​
EMAIL​
2
1​
Yes​
4​
No​
Yes​
bob@notreal.com​
3
2​
Yes​
7​
Yes​
No​
jane@notreal.com​

Then on another sheet

A
B
1
EMAIL​
Q2​
2
bob@notreal.com​
No​
3
jane@notreal.com​
Yes​

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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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)),"")


ABCDEFG
1Export 1
2IDQ1Q1aQ2Q3EMAIL
31Yes4NoYesbob@notreal.com
42Yes7YesNojane@notreal.com
5
6Export 2
7IDQ1Q2EMAIL
81NoYesjane@notreal.com
92YesYesbob@notreal.com
10
11
12I need a formula that will work in both scenarios to populate this table:Q2
13EMAILExport1Export2
14bob@notreal.comNoYes
15jane@notral.com
16
*********************************************************************************

<tbody>
</tbody>


Markmzz
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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>


ABCDEABCDEFGABCDE
1I need a formula that will work in both scenarios to populate this table:Q2Sheet11IDQ1Q1aQ2Q3EMAILExport11IDQ1Q2EMAILExport2
2EMAILExport1Export221Yes4NoYesbob@notreal.com21NoYesjane@notreal.com
3bob@notreal.comNoYes32Yes7YesNojane@notreal.com32YesYesbob@notreal.com
4jane@notreal.comYesYes44
555
********************************************************************************************************************************************

<tbody>
</tbody>


Do some test.

Markmzz
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top