Help with INDEX+MATCH AND ARRAY FORMULAS TO SORT OUT NAMES OF AGENTS AT A SPECIFIC TIME

[ambepat]

Hi guys,

I have this question and I need some help here.

Here is my table;

	18:00	18:30	19:00	19:30	20:00
John	IB	IB	IB
Abraham	IB	IB	IB	IB	IB
Tom	IB	IB	IB	IB	IB
Pam	IB	IB	IB

<tbody>
</tbody>

Name of Agent 18:00 - 19:00 18:00 - 20:00


What I need is a formula that will give me

1) The name of the agents that is Inbound (IB) from 18:00 - 19:00?
2) The name of the agents that is Inbound (IB) from 18:00 - 20:00?


Thanks in advance for you assistance.

 

[ambepat]

Hey guys,

Please really need help here. Is there anyone out there that can smack this down????

Thanks,

ambepat

 

[AhoyNC]

ambepat,

Per your PM where you stated you only wanted to see agents between 18:00 - 19:00 and 18:00 - 20:00, try the formulas below.
You might also want to look at Filter under the Data menu.

Note that these formulas pretty much only work for the times you asked for.

Excel Workbook
	A	B	C	D	E	F
1		18:00	18:30	19:00	19:30	20:00
2	John	IB	IB	IB
3	Abraham	IB	IB	IB	IB	IB
4	Tom	IB	IB	IB	IB	IB
5	Pam	IB	IB	IB
6
7
8		18:00	19:00		18:00	20:00
9		John			Abraham
10		Pam			Tom
11
12
Sheet

 

[ambepat]

Thanks a lot man.

Really appreciate. You always there when I need help. Really grateful.

Peace

ambepat,

Per your PM where you stated you only wanted to see agents between 18:00 - 19:00 and 18:00 - 20:00, try the formulas below.
You might also want to look at Filter under the Data menu.

Note that these formulas pretty much only work for the times you asked for.

	A	B	C	D	E	F
1		18:00	18:30	19:00	19:30	20:00
2	John	IB	IB	IB
3	Abraham	IB	IB	IB	IB	IB
4	Tom	IB	IB	IB	IB	IB
5	Pam	IB	IB	IB
6
7
8		18:00	19:00		18:00	20:00
9		John			Abraham
10		Pam			Tom
11
12

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:64px;"><col style="width:64px;"><col style="width:48px;"><col style="width:68px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B9 {=IFERROR(INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$F$5,0,MATCH($B$8,$B$1:$F$1,0))="IB",IF(INDEX($B$2:$F$5,0,MATCH($C$8,$B$1:$F$1,0))="IB",IF(INDEX($B$2:$F$5,0,MATCH($C$8+0.5/24,$B$1:$F$1,0))=0,ROW($A$2:$A$5)-ROW($A$2)+1))),ROWS($A$2:A2))),"")} E9 {=IFERROR(INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$F$5,0,MATCH($E$8,$B$1:$F$1,0))="IB",IF(INDEX($B$2:$F$5,0,MATCH($F$8,$B$1:$F$1,0))="IB",ROW($A$2:$A$5)-ROW($A$2)+1)),ROWS($A$2:A2))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4