Function to search array to identify 2 elements and output the matching cells corresponding column 1 value

[OTOTO]

Hi there. I am a toastmaster. My group decided that we would use a spreadsheet to sign up for roles on meeting dates. Accordingly I want to have the users fill out a userform to indicate which dates they are interested in which roles. I have sheet1, which is seen below: It is a series of roles in the first Column (A2:A11) and a series of dates in the first Row (B1:J1). The userform allows users to pull dates from a dropdown box. The form submits the dates along with their username to sheet 2. Sheet 2 is seen below. It contains Name, Time of entry and the roles in the first row (A1:L1). Simple enough. What I need is the function that will be entered into the cells of sheet 1 to list the usernames for the respective dates and roles as determined by the data entered in sheet2. Any takers? 1/23/2014 2/13/2014 2/27/2014 3/13/2014 3/27/2014 4/10/2014 4/24/2014 5/8/2014 5/29/2014 Toastmaster Ah-Counter Word of the Day and Grammerian Timer General Evaluator Speaker #1 Evaluator #1 Speaker #2 Evaluator #2 Table Topics Master <colgroup><col><col span="9"></colgroup><tbody> </tbody>

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

Here's my sheet 2:

Submitter	Time Submitted	Toastmaster	Ah-Counter	Word of the Day and Grammerian	Timer	General Evaluator	Speaker #1	Evaluator #1	Speaker #2	Evaluator #2	Table Topics Master
sotoole	16/01/2014 17:19:55	01/23/2014			05/08/2014						05/29/2014

<colgroup><col span="12"></colgroup><tbody>
</tbody>

 

[OTOTO]

Re: Why isn't this working? {=INDEX(Sheet2!A2:A1000, MATCH(B1,IF(Sheet2!C1:L1=A11,Sheet2!B3:L1000),0))} ?

Now your formula makes no references to sheet2

 

[s hal]

Re: Why isn't this working? {=INDEX(Sheet2!A2:A1000, MATCH(B1,IF(Sheet2!C1:L1=A11,Sheet2!B3:L1000),0))} ?

Now your formula makes no references to sheet2

Change it to be 2, then... I'm confused about which sheet you reference. The formula I made goes in your report and hinges off your datasheet.

Also, my formula is for the US version of excel - I don't know what character sets other languages may have. So if you're not using the US version, that would explain the formula not working at this point - it works 100% without error for me.

 

[OTOTO]

Re: Why isn't this working? {=INDEX(Sheet2!A2:A1000, MATCH(B1,IF(Sheet2!C1:L1=A11,Sheet2!B3:L1000),0))} ?

The first data set is Sheet 1:

	01/23/14	02/13/14	02/27/14	03/13/14	03/27/14	04/10/14	04/24/14	05/08/14	05/29/14
Toastmaster
Ah-Counter
Word of the Day and Grammerian
Timer
General Evaluator
Speaker #1
Evaluator #1
Speaker #2
Evaluator #2
Table Topics Master

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

and the second data set is Sheet 2:

Submitter	Time Submitted	Toastmaster	Ah-Counter	Word of the Day and Grammerian	Timer	General Evaluator	Speaker #1	Evaluator #1	Speaker #2	Evaluator #2	Table Topics Master
sotoole	16/01/2014 17:19:55	01/23/2014			05/08/2014						05/29/2014
sotoole	16/01/2014 18:26:48	01/23/2014								01/23/2014
sotoole	16/01/2014 18:29:00		01/23/2014			03/27/2014	04/10/2014		01/23/2014
sotoole		01/23/2014						01/23/2014
sotoole			01/23/2014				01/23/2014
sotoole				01/23/2014		01/23/2014
sotoole					01/23/2014

<colgroup><col><col><col><col><col><col span="3"><col><col><col><col></colgroup><tbody>
</tbody>

I think it should ref Sheet 2 like this:
=IFERROR(INDEX(Sheet1!$A:$A,MATCH(B$1,INDIRECT("Sheet2!"&CHAR(64+MATCH($A2,Sheet2!$C$1:$L$1,0)+2)&":"&CHAR(64+MATCH($A2,Sheet2!$C$1:$L$1,0)+2)),0)),"")

but again... no hits.

 

[s hal]

Re: Why isn't this working? {=INDEX(Sheet2!A2:A1000, MATCH(B1,IF(Sheet2!C1:L1=A11,Sheet2!B3:L1000),0))} ?

OTOTO, you missed a sheet1 in there - ALL of them must be the same sheet.