Multiple Match Criteria_Index across rows and Match columns

R

riteshpatnaik

New Member
Joined
Jan 28, 2019
Messages
2
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
[TABLE="width: 621"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]Roger[/TD]
[TD="align: center"]Patrcik[/TD]
[TD="align: center"]Jane[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Output Sheet

[TABLE="width: 621"]
<tbody>[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]Roger[/TD]
[TD="align: center"]Patrick[/TD]
[TD="align: center"]Jane[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]Roger[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Patrick[/TD]
[TD="align: center"]Jane[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Maybe:
Copy formula down and across as needed.

Excel Workbook
ABCDE
1Monday 
2TuesdaySamRogerPatrcikJane
3Wednesday
4ThursdaySamRoger
5FridayPatrcikJane
6Saturday
7Sunday
Output
Excel Workbook
ABCDE
1SamRogerPatrcikJane
2Monday
3TuesdayBBEE
4Wednesday
5ThursdayBB
6FridayEE
7Saturday
8Sunday
Attendance
 
Upvote 0
AhoyNC said:
Maybe:
Copy formula down and across as needed.

Output

ABCDE

<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>
[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="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]

[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="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="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="align: center"]Patrcik[/TD]
[TD="align: center"]Jane[/TD]

[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="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]

</tbody>

Spreadsheet Formulas
CellFormula
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

ABCDE

<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>
[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] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Sam[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Roger[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Patrcik[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Jane[/TD]

[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] "]Monday[/TD]

[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] "]Tuesday[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]E[/TD]

[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] "]Wednesday[/TD]

[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] "]Thursday[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]

[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] "]Friday[/TD]

[TD="align: center"]E[/TD]
[TD="align: center"]E[/TD]

[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] "]Saturday[/TD]

[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"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Sunday[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...


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

SamSunnyRogerRachelPatrickTonyJane
Monday
TuesdayB B E E
Wednesday
ThursdayB B
Friday E E
Saturday
Sunday

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


[TABLE="width: 360"]
<colgroup><col width="72" span="5" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 72"]Output[/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 72"]Monday[/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 72"]Tuesday[/TD]
[TD="class: xl68, width: 72"]Sam[/TD]
[TD="class: xl68, width: 72"]Roger[/TD]
[TD="class: xl68, width: 72"]Patrick[/TD]
[TD="class: xl68, width: 72"]Jane[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 72"]Wednesday[/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 72"]Thursday[/TD]
[TD="class: xl68, width: 72"]Sam[/TD]
[TD="class: xl68, width: 72"]Roger[/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 72"]Friday[/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"]Patrick[/TD]
[TD="class: xl68, width: 72"]Jane[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 72"]Saturday[/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 72"]Sunday[/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[TD="class: xl68, width: 72"] [/TD]
[/TR]
</tbody>[/TABLE]

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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Lookup match with multiple criteria
Replies
2
Views
107
KGee
K
B
  • Question Question
Find Multiple Match Results Where You Use * in the Search Criteria Across Multiple Columns and Rows
Replies
4
Views
613
alansidman
alansidman
S
  • Question Question
COUNTIFS with multiple criteria in both columns and rows
2
Replies
10
Views
815
Fluff
Fluff
T
  • Solved
Index match with multiple criteria, sheets & columns
2
Replies
10
Views
754
Fluff
Fluff
S
  • Question Question
Index/Match Formula Formula with multiple criteria
Replies
1
Views
941
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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