Index cannot accept array inputs

[archangel99]

Hi

I am trying to help my colleague solve a simple problem on allocation. In the spreadsheet shown in the image below, each of the students has three choices of classes – A, B and C. Each class has a max capacity. The challenge is to allocate students to their lowest choice subject to the availability of max capacity.
The challenge I am facing was not being able to wrap the array made from comparison into an index statement. If I were to use helper cells, the answer worked out perfectly.

The formula for the helper cell is shown in the image.

Column H contains the allocated choice.

Cell H2: A <- first case always allocated first choice
Cell H3: =INDEX(A3:C3,,MATCH(TRUE,E3:G3,0))
Cell H4: INDEX(A4:C4,,MATCH(TRUE,E4:G4,0))
Why can’t I wrap the array (computed from the countif) into the index formula?

I also take this opportunity to thank you in advance.

 

[StephenCrump]

You can produce the same results using:

H3: {=INDEX(A3:C3,MATCH(TRUE,LOOKUP(A3:C3,L$1:L$3,M$1:M$3)>COUNTIF(H2:H$2,A3:C3),))}

 

[archangel99]

You can produce the same results using:

H3: {=INDEX(A3:C3,MATCH(TRUE,LOOKUP(A3:C3,L$1:L$3,M$1:M$3)>COUNTIF(H2:H$2,A3:C3),))}

It produces an error. Also, need to count the assigned choice for the other students too.

 

[StephenCrump]

It produces an error. Also, need to count the assigned choice for the other students too.

I'm not sure what you mean?

H3: =INDEX(A3:C3,MATCH(TRUE,LOOKUP(A3:C3,L$1:L$3,M$1:M$3)>COUNTIF(H2:H$2,A3:C3),)) array-entered
Copy down to H4:H9

This reproduces your assigned choices for all students.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Choice1	Choice2	Choice3					Me	You			A	3
2	A	B	C					A	A			B	1
3	B	C	A					B	B			C	4
4	A	C	B					A	A
5	B	C	A					C	C
6	B	A	C					A	A
7	A	B	C					C	C
8	C	A	B					C	C
9	A	B	C					C	C
Sheet1

 

[Tetra201]

... Why can’t I wrap the array (computed from the countif) into the index formula?..

Actually, it's the VLOOKUP that needs some coercing. The following array formula has been tested to return the required results:

=INDEX(A3:C3,,MATCH(TRUE,(COUNTIF(A3:C3,A3:C3)+COUNTIF($H$2:H2,A3:C3))<=VLOOKUP(T(IF({TRUE},A3:C3)),$L$1:$M$3,2,FALSE),0))

It can be further simplified and shortened:

=INDEX(A3:C3,,MATCH(TRUE,({1,1,1}+COUNTIF($H$2:H2,A3:C3))<=VLOOKUP(T(IF({1},A3:C3)),$L$1:$M$3,2,0),0))

 

[archangel99]

I'm not sure what you mean?

H3: =INDEX(A3:C3,MATCH(TRUE,LOOKUP(A3:C3,L$1:L$3,M$1:M$3)>COUNTIF(H2:H$2,A3:C3),)) array-entered
Copy down to H4:H9

This reproduces your assigned choices for all students.

	A	B	C	D	E	F	G	H	I	J	K	L	M
A
B
C

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Choice1[/TD]
[TD="align: center"]Choice2[/TD]
[TD="align: center"]Choice3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Me[/TD]
[TD="align: center"]You[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

It works. Thanks a lot. Do you know why mind can't work?

 

[archangel99]

Actually, it's the VLOOKUP that needs some coercing. The following array formula has been tested to return the required results:

=INDEX(A3:C3,,MATCH(TRUE,(COUNTIF(A3:C3,A3:C3)+COUNTIF($H$2:H2,A3:C3))<=VLOOKUP(T(IF({TRUE},A3:C3)),$L$1:$M$3,2,FALSE),0))

It can be further simplified and shortened:

=INDEX(A3:C3,,MATCH(TRUE,({1,1,1}+COUNTIF($H$2:H2,A3:C3))<=VLOOKUP(T(IF({1},A3:C3)),$L$1:$M$3,2,0),0))

Hey thanks. Super complicated. Will take some time for me to digest. But you are right it is the vlookup. I substituted with lookup as shared by StephenCrump and it worked perfectly. Now to analyze your array coercion.

 

[StephenCrump]

Now to analyze your array coercion.

Perhaps easier to see if you rearrange slightly:

=INDEX(A3:C3,MATCH(TRUE, [B]VLOOKUP(T(IF({1},A3:C3)),$L$1:$M$3,2,)[/B] >COUNTIF(H$2:H2,A3:C3),))

vs

=INDEX(A3:C3,MATCH(TRUE, [B]LOOKUP(A3:C3,L$1:L$3,M$1:M$3)[/B]          >COUNTIF(H2:H$2,A3:C3),))

 

[archangel99]

Perhaps easier to see if you rearrange slightly:

=INDEX(A3:C3,MATCH(TRUE, [B]VLOOKUP(T(IF({1},A3:C3)),$L$1:$M$3,2,)[/B] >COUNTIF(H$2:H2,A3:C3),))

vs

=INDEX(A3:C3,MATCH(TRUE, [B]LOOKUP(A3:C3,L$1:L$3,M$1:M$3)[/B]          >COUNTIF(H2:H$2,A3:C3),))

Thank you very much, StephenCrump