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]

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

Please lend a hand!

 

[s hal]

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

So demanding!

The following formula starting in cell B2 and pasted in all directions:

=INDEX(sheet2!$A:$A,MATCH(B$1,INDIRECT("Sheet2!"&CHAR(64+MATCH($A2,sheet2!$C$1:$L$1,0)+3)&":"&CHAR(64+MATCH($A2,sheet2!$C$1:$L$1,0)+3)),0))

 

[s hal]

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

Your attempt didn't work because you didn't make a match in both directions.

Also, I forgot to mention, you'll want to do IFERROR( (my formula), "") to get rid of the errors for blanks!

 

[OTOTO]

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

=IFERROR(INDEX(Sheet2!$A:$A,MATCH(B$1,INDIRECT("Sheet2!"&CHAR(64+MATCH($A4,Sheet2!$C$1:$L$1,0)+3)&":"&CHAR(64+MATCH($A4,Sheet2!$C$1:$L$1,0)+3)),0)), "")

Thanks a million S Hal. I'm getting zero hits with this, I made sure my dates were exact same format 01/01/14 etc on both pages, bold with same alignment and the roles are all same exact values but It's not recognizing any matches. also control+Shift+enter is not giving me my array {} quotations... what's going on?

 

[OTOTO]

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

also, I've never used the Char function, I'm not sure what the c4 refers to?

 

[s hal]

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

My formula is NOT an array formula.. Do not enter it with CTRL-SHIFT-ENTER. It worked just fine for me, change the sheet references to match yours, I assumed sheet1 was what was on top, which seems to have been the wrong assumption on my part..

 

[OTOTO]

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

you're right that sheet 1 was on top, and I see that you reference it correctly in the formula. still nothing here. Let's see...

 

[OTOTO]

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

unfortunately, nothing. Even with the IfError functionality removed i'm getting #N/As all around, even when a match should occur. Any thoughts? what does this guy represent? is it an error? &":"&

 

[s hal]

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

also, I've never used the Char function, I'm not sure what the c4 refers to?

That is the number 64, NOT C4 - HUGE difference! Char() = makes a letter based on a number, 64 = A, your data starts in row D, so I make an indirect reference to whichever column the data is in by matching the role to the column in your data sheet. And I noticed that I did make a boo-boo in this - I have it adding too many to 64, try the following:

=IFERROR(INDEX(sheet1!$A:$A,MATCH(B$1,INDIRECT("Sheet1!"&CHAR(64+MATCH($A2,sheet1!$C$1:$L$1,0)+2)&":"&CHAR(64+MATCH($A2,sheet1!$C$1:$L$1,0)+2)),0)),"")

Edit: added Sheet1