Multiple Array Criteria

[BrizzleC]

Hello all.

Using the table below:

Excel 2007

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Schemes[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Cat[/TD]
[TD="align: center"]Result Header[/TD]
[TD="align: center"]£0-200k (CWBS Only)[/TD]
[TD="align: center"]£0-2m
(CWBS Only)[/TD]
[TD="align: center"]£2-7m[/TD]
[TD="align: center"]£7-20m[/TD]
[TD="align: center"]£20-70m[/TD]
[TD="align: center"]£70-250m[/TD]
[TD="align: center"]£250 - 750m[/TD]
[TD="align: center"]>£750m[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]VL[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]5,000[/TD]
[TD="align: center"]25,000[/TD]
[TD="align: center"]75,000[/TD]
[TD="align: center"]250,000[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]2,500,000[/TD]
[TD="align: center"]5,000,000[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]10,000[/TD]
[TD="align: center"]50,000[/TD]
[TD="align: center"]150,000[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]1,000,000[/TD]
[TD="align: center"]5,000,000[/TD]
[TD="align: center"]10,000,000[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"]10,000[/TD]
[TD="align: center"]50,000[/TD]
[TD="align: center"]150,000[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]1,000,000[/TD]
[TD="align: center"]5,000,000[/TD]
[TD="align: center"]10,000,000[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]1,750[/TD]
[TD="align: center"]17,500[/TD]
[TD="align: center"]87,500[/TD]
[TD="align: center"]250,000[/TD]
[TD="align: center"]875,000[/TD]
[TD="align: center"]1,750,000[/TD]
[TD="align: center"]8,750,000[/TD]
[TD="align: center"]17,500,000[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]2,500[/TD]
[TD="align: center"]25,000[/TD]
[TD="align: center"]125,000[/TD]
[TD="align: center"]350,000[/TD]
[TD="align: center"]1,250,000[/TD]
[TD="align: center"]2,500,000[/TD]
[TD="align: center"]12,500,000[/TD]
[TD="align: center"]25,000,000[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]2,500[/TD]
[TD="align: center"]25,000[/TD]
[TD="align: center"]125,000[/TD]
[TD="align: center"]350,000[/TD]
[TD="align: center"]1,250,000[/TD]
[TD="align: center"]2,500,000[/TD]
[TD="align: center"]12,500,000[/TD]
[TD="align: center"]25,000,000[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]4,000[/TD]
[TD="align: center"]37,500[/TD]
[TD="align: center"]187,500[/TD]
[TD="align: center"]550,000[/TD]
[TD="align: center"]1,875,000[/TD]
[TD="align: center"]4,000,000[/TD]
[TD="align: center"]18,750,000[/TD]
[TD="align: center"]37,500,000[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]5,500[/TD]
[TD="align: center"]50,000[/TD]
[TD="align: center"]250,000[/TD]
[TD="align: center"]750,000[/TD]
[TD="align: center"]2,500,000[/TD]
[TD="align: center"]5,500,000[/TD]
[TD="align: center"]25,000,000[/TD]
[TD="align: center"]50,000,000[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]5,500[/TD]
[TD="align: center"]50,000[/TD]
[TD="align: center"]250,000[/TD]
[TD="align: center"]750,000[/TD]
[TD="align: center"]2,500,000[/TD]
[TD="align: center"]5,500,000[/TD]
[TD="align: center"]25,000,000[/TD]
[TD="align: center"]50,000,000[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]7,750[/TD]
[TD="align: center"]75,000[/TD]
[TD="align: center"]375,000[/TD]
[TD="align: center"]1,125,000[/TD]
[TD="align: center"]4,000,000[/TD]
[TD="align: center"]7,750,000[/TD]
[TD="align: center"]37,500,000[/TD]
[TD="align: center"]75,000,000[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]10,000[/TD]
[TD="align: center"]100,000[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]1,500,000[/TD]
[TD="align: center"]5,500,000[/TD]
[TD="align: center"]10,000,000[/TD]
[TD="align: center"]50,000,000[/TD]
[TD="align: center"]100,000,000[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]VH[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]10,000[/TD]
[TD="align: center"]100,000[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]1,500,000[/TD]
[TD="align: center"]5,500,000[/TD]
[TD="align: center"]10,000,000[/TD]
[TD="align: center"]50,000,000[/TD]
[TD="align: center"]100,000,000[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: center"]150,000[/TD]
[TD="align: center"]750,000[/TD]
[TD="align: center"]2,250,000[/TD]
[TD="align: center"]7,750,000[/TD]
[TD="align: center"]15,000,000[/TD]
[TD="align: center"]75,000,000[/TD]
[TD="align: center"]150,000,000[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]20,000[/TD]
[TD="align: center"]200,000[/TD]
[TD="align: center"]1,000,000[/TD]
[TD="align: center"]3,000,000[/TD]
[TD="align: center"]10,000,000[/TD]
[TD="align: center"]20,000,000[/TD]
[TD="align: center"]100,000,000[/TD]
[TD="align: center"]200,000,000[/TD]

</tbody>

Sheet2​

The scheme is selected within one part of the spreadsheet (B25) and the Category is selected in the same table (F25) as the results; and I want the Min, ML and Max values to be auto-populated within the table dependant on the criteria selected.

eg Scheme £2-7m & Category L will return Min = 50,00; ML = 87,500; Max = 125,000

There will only be one Scheme per spreadsheet, however this will be part of a larger template so there is a need to have one index rather than one separate for each Scheme.

I've got the following to work to populate when working with a smaller table with just the min values:

=INDEX($C$2:$J$7,MATCH($F$13,$A$2:$A$7,0),MATCH($B$13,$C$1:$J$1,0))
But not sure how to populate the ML and Max parts.

Any help would be much appreciated.

 

[ndsutherland]

What is you destination layout? Do you want min ml and max all in the same cells, or in separate rows? Can you post a sample of what you want the end result to look like?

 

[BrizzleC]

The destination is part of a large Risk Register so far too large to post the whole thing here; but essentially the Scheme is selected from a drop down box in the header section of the register and there is a Impact Column (Category) that the VH, H, M, L, VL & N is selected and to the right are three columns where the Min, ML & Max are to be populated.

I have attached an example extract below:

Excel 2007

	F	G	H	I

<tbody>
[TD="align: center"]22[/TD]
[TD="align: center"]Impact[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]ML[/TD]
[TD="align: center"]Max[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]N[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]VL[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]L[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]M[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]H[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]VH[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]#N/A[/TD]

</tbody>

Sheet2
Thanks again

 

[ndsutherland]

If Min, ML, and Max are always in that order you could simply add 1 to the end of the row argument for ML and 2 for Max:

=INDEX($C$2:$J$7,MATCH($F$13,$A$2:$A$7,0)+1,MATCH($B$13,$C$1:$J$1,0)) and =INDEX($C$2:$J$7,MATCH($F$13,$A$2:$A$7,0)+2,MATCH($B$13,$C$1:$J$1,0))

or

=INDEX($C$2:$J$7,Match("ML",OFFSET($B$2:$B$4,$MATCH($F$13,$A$2:$A$7,0)-1,0),0),MATCH($B$13,$C$1:$J$1,0)) -untested

 

[BrizzleC]

Cheers ndsutherland!!

The top suggestion works perfectly Have managed to incorporate into my Register and can happily change the Schemes and Impacts; with the Min , ML and Max columns being populated correctly.

FYI I tried the bottom one but it didn't work.