Using INDEX and MATCH with two arguments, allowing up to four results

[johnburgin]

Hi,

i have a spreadsheet which contains information about booked appointments, for up to six officers. I now need to build a 2 weekly diary summary sheet. I need to match the officer against their appointments for the day.

So, Monday would list underneath up to four names of clients booked, Tuesday the same and so on.

I need to match, therefore, the officer with the date, and return the client name.

So far, I have learnt how to match an officer and use index to return the client name using:

=index(drange,match("FM",qrange,false))

where "FM" is the officer's name, drange contains client names and qrange contains the officer's initials.

Booking dates are stored in rrange.

But I need to be able to match the above for a specified date contained in cell A2.

Also, there may be up to four appointments booked for that date, so I plan on using the formula in four cells, each returning the next booking.

Finally, I would prefer not to use arrays IF POSSIBLE so as to avoid slowing the spreadsheet down.

Im using Excel 2003.

Thanks for your help.

JB

 

[Snakehips]

johnburgin,

In the absence of any other responses here is my take on this.
Unless you were to use vba to compile your summary then array formulas are unavoidable.

I do not know how you intend laying out your summary but the below might give you ideas if nothing else.

Have assumed yournamed ranges to be in eg 'Diary' sheet and summary on 'Summary' sheet.

Date of first day of summary in cell D3 the other day/dates by formula eg =D3+1 etc.

The formula in C3 will copy down 4 appointmet row and across for two weeks of dates.

However the next block of data for the second officer will require an edit to increment the
ROWS($A$3:$A3) and the qrange=$A$3 elements to ROWS($A$8:$A8) and the qrange=$A$8

Similarly, add 5 for each subsequent officer block.

Excel 2007
	C	D	E
2	Date	Client	Officer
3	01/01/2013	A	AS
4	04/01/2013	B	FM
5	03/01/2013	A	HY
6	02/01/2013	C	FM
7	04/01/2013	D	WE
8	04/01/2013	P	HY
9	04/01/2013	G	WE
10	04/01/2013	X	WE
11	04/01/2013	T	FM
12	05/01/2013	L	WE
13	05/01/2013	M	FM
14	06/01/2013	Z	FM
15	06/01/2013	K	FM
16	06/01/2013	C	WE
17	06/01/2013	V	FM
Diary

Excel 2007

	A	B	C	D	E
1
2	Officer		04/01/2013	05/01/2013	06/01/2013
3	FM	App 1	B	M	Z
4		App 2	T		K
5		App 3			V
6		App 4
7
8	WE	App 1	D	L	C
9		App 2	G
10		App 3	X
11		App 4

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Summary​

Array Formulas Cell Formula C3 {=IF(ROWS($A$3:$A3)>SUMPRODUCT((qrange=$A$3)*(rrange=C$2)),"",INDEX(drange,MATCH(LARGE((qrange=$A$3)*(rrange=C$2)*(50000-ROW(rrange)),ROWS($A$3:$A3)),(qrange=$A$3)*(rrange=C$2)*(50000-ROW(rrange)),0),1))} <THEAD> </THEAD><TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Workbook Defined Names Name Refers To drange =Diary!$D$3:$D$17 qrange =Diary!$E$3:$E$17 rrange =Diary!$C$3:$C$17 <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

I hope that makes sense and is of some help.

 

[johnburgin]

Yes, that's excellent, thanks. How does the reference to 50000-row work exactly?

JB

johnburgin,

In the absence of any other responses here is my take on this.
Unless you were to use vba to compile your summary then array formulas are unavoidable.

I do not know how you intend laying out your summary but the below might give you ideas if nothing else.

Have assumed yournamed ranges to be in eg 'Diary' sheet and summary on 'Summary' sheet.

Date of first day of summary in cell D3 the other day/dates by formula eg =D3+1 etc.

The formula in C3 will copy down 4 appointmet row and across for two weeks of dates.

However the next block of data for the second officer will require an edit to increment the
ROWS($A$3:$A3) and the qrange=$A$3 elements to ROWS($A$8:$A8) and the qrange=$A$8

Similarly, add 5 for each subsequent officer block.


Excel 2007

	C	D	E
2	Date	Client	Officer
3	01/01/2013	A	AS
4	04/01/2013	B	FM
5	03/01/2013	A	HY
6	02/01/2013	C	FM
7	04/01/2013	D	WE
8	04/01/2013	P	HY
9	04/01/2013	G	WE
10	04/01/2013	X	WE
11	04/01/2013	T	FM
12	05/01/2013	L	WE
13	05/01/2013	M	FM
14	06/01/2013	Z	FM
15	06/01/2013	K	FM
16	06/01/2013	C	WE
17	06/01/2013	V	FM

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Diary​

Excel 2007

	A	B	C	D	E
1
2	Officer		04/01/2013	05/01/2013	06/01/2013
3	FM	App 1	B	M	Z
4		App 2	T		K
5		App 3			V
6		App 4
7
8	WE	App 1	D	L	C
9		App 2	G
10		App 3	X
11		App 4

<tbody>
</tbody>

Summary​

Array Formulas Cell Formula C3 {=IF(ROWS($A$3:$A3)>SUMPRODUCT((qrange=$A$3)*(rrange=C$2)),"",INDEX(drange,MATCH(LARGE((qrange=$A$3)*(rrange=C$2)*(50000-ROW(rrange)),ROWS($A$3:$A3)),(qrange=$A$3)*(rrange=C$2)*(50000-ROW(rrange)),0),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To drange =Diary!$D$3:$D$17 qrange =Diary!$E$3:$E$17 rrange =Diary!$C$3:$C$17 <tbody> </tbody>

<tbody>
</tbody>

I hope that makes sense and is of some help.

 

[Aladin Akyurek]

Hi,

i have a spreadsheet which contains information about booked appointments, for up to six officers. I now need to build a 2 weekly diary summary sheet. I need to match the officer against their appointments for the day.

So, Monday would list underneath up to four names of clients booked, Tuesday the same and so on.

I need to match, therefore, the officer with the date, and return the client name.

So far, I have learnt how to match an officer and use index to return the client name using:

=index(drange,match("FM",qrange,false))

where "FM" is the officer's name, drange contains client names and qrange contains the officer's initials.

Booking dates are stored in rrange.

But I need to be able to match the above for a specified date contained in cell A2.

Also, there may be up to four appointments booked for that date, so I plan on using the formula in four cells, each returning the next booking.

Finally, I would prefer not to use arrays IF POSSIBLE so as to avoid slowing the spreadsheet down.

Im using Excel 2003.

Thanks for your help.

JB

Sheet2, the destination sheet...

A1 houses an initial like FM...
A2 houses a date of interest...

A3, control+shift+enter:

=SUM(IF(qrange=A$1,IF(rrange=A$2,1)))

A4: Client

A5, control+shift+enter, and copy down:

=IF(ROWS(A$5:A5)<=A$3,INDEX(drange,SMALL(IF(qrange=A$1,IF(rrange=A$2,ROW(drange)-ROW(INDEX(drange,1,1))+1)),ROWS(A$5:A5)),"")

 

[Snakehips]

Yes, that's excellent, thanks. How does the reference to 50000-row work exactly?

JB

JB,

Here is my thinking on the 500000.

The (qrange=$A$3)*(rrange=C$2) portion of the formula will yield an array of zeros and ones.
Adding *(ROW(rrange) keeps the zeros and turn the ones into unique numbers ie row numbers.

I wanted to avoid using SMALL because of all the zeros. Using LARGE without the 50000- will give you a result if you try it but the appointments will be in the reverse order. Eg in my example for WE on the 04/01/2013, appointments D, G, X would invert to be X, G, D. Thinking that appointment 1 would be above appointment 2, etc, in your diary data, I used 50000 as an arbitrary large number so that 50000-Row.... would preserve the order of appointments.

Once again, I hope that makes sense.