Multiple Match Criteria_Index across rows and Match columns

[riteshpatnaik]

I am trying to get value from another sheet based on 3 criteria-I have students name across rows and days across columns. They either attend Economics or Business Strategy and their attendance is recorded with "B" or an "E".
In another sheet I want to find the number of students who have attended on a particular day and who all have attended. Number of students is easy but I am not able to pull up the names.
If (in the current sheet the day matches with the day in another sheet, if the student has attended Economics "E" or Business Strategy "B", then <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">NAME</code> of the student who has attended on that particular day....similarly list all the names for that particular day.....Andy SAM Sunny Rachel etc etc(row wise). The value"B" and "E" are Unique I.e that first few columns will be all Bs and then next few columns all Es

Attendance Sheet

	Sam	Roger	Patrcik	Jane
Monday
Tuesday	B	B	E	E
Wednesday
Thursday	B	B
Friday			E	E
Saturday
Sunday

<tbody>
</tbody>

Output Sheet

Monday
Tuesday	Sam	Roger	Patrick	Jane
Wednesday
Thursday	Sam	Roger
Friday			Patrick	Jane
Saturday
Sunday

<tbody>
</tbody>

 

[AhoyNC]

Maybe:
Copy formula down and across as needed.

Excel Workbook
	A	B	C	D	E
1	Monday
2	Tuesday	Sam	Roger	Patrcik	Jane
3	Wednesday
4	Thursday	Sam	Roger
5	Friday			Patrcik	Jane
6	Saturday
7	Sunday
Output

Excel Workbook
	A	B	C	D	E
1		Sam	Roger	Patrcik	Jane
2	Monday
3	Tuesday	B	B	E	E
4	Wednesday
5	Thursday	B	B
6	Friday			E	E
7	Saturday
8	Sunday
Attendance

 

[riteshpatnaik]

Maybe:
Copy formula down and across as needed.

Output

	A	B	C	D	E
1	Monday
2	Tuesday	Sam	Roger	Patrcik	Jane
3	Wednesday
4	Thursday	Sam	Roger
5	Friday			Patrcik	Jane
6	Saturday
7	Sunday

<colgroup><col style="width:30px; "><col style="width:98px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(SUMPRODUCT((Attendance!$A$2:$A$8=Output!$A1)*(Attendance!B$2:B$8={"B","E"}))=1,Attendance!B$1,"") <tbody> </tbody>

<tbody>
</tbody>

Attendance

	A	B	C	D	E
1		Sam	Roger	Patrcik	Jane
2	Monday
3	Tuesday	B	B	E	E
4	Wednesday
5	Thursday	B	B
6	Friday			E	E
7	Saturday
8	Sunday

<colgroup><col style="width:30px; "><col style="width:80px;"><col style="width:64px;"><col style="width:67px;"><col style="width:69px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi, Thanks for the help. One more help..I tried to do the same here

Attendance
	Sam	Sunny	Roger	Rachel	Patrick	Tony	Jane
Monday
Tuesday	B		B		E		E
Wednesday
Thursday	B		B
Friday					E		E
Saturday
Sunday

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

Output
Monday
Tuesday	Sam	Roger	Patrick	Jane
Wednesday
Thursday	Sam	Roger
Friday			Patrick	Jane
Saturday
Sunday

<colgroup><col width="72" span="5" style="width:54pt"> </colgroup><tbody>
</tbody>

Here I don't want blanks in between. I want the same output like this

Output


<table border="0" cellspacing="0" cellpadding="0" calibri,="" arial;="" font-size:="" 11pt;="" padding-left:="" 2pt;="" padding-right:="" 2pt;"="" width=""><colgroup><col style="width: 30px;"><col style="width: 98px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Tuesday[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]Roger[/TD]
[TD="align: center"]Patrcik[/TD]
[TD="align: center"]Jane[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Wednesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Thursday[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]Roger[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Friday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Patrcik[/TD]
[TD="align: center"]Jane[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Saturday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] [/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody></table>