Multiple Array Criteria

[BrizzleC]

Hello all.

Using the table below:

Excel 2007

	A	B	C	D	E	F	G	H	I	J
1			Schemes
2	Cat	Result Header	£0-200k (CWBS Only)	£0-2m (CWBS Only)	£2-7m	£7-20m	£20-70m	£70-250m	£250 - 750m	>£750m
3	N	Min	0	0	0	0	0	0	0	0
4		ML	0	0	0	0	0	0	0	0
5		Max	0	0	0	0	0	0	0	0
6	VL	Min	0	0	0	0	0	0	0	0
7		ML	500	5,000	25,000	75,000	250,000	500,000	2,500,000	5,000,000
8		Max	1,000	10,000	50,000	150,000	500,000	1,000,000	5,000,000	10,000,000
9	L	Min	1,000	10,000	50,000	150,000	500,000	1,000,000	5,000,000	10,000,000
10		ML	1,750	17,500	87,500	250,000	875,000	1,750,000	8,750,000	17,500,000
11		Max	2,500	25,000	125,000	350,000	1,250,000	2,500,000	12,500,000	25,000,000
12	M	Min	2,500	25,000	125,000	350,000	1,250,000	2,500,000	12,500,000	25,000,000
13		ML	4,000	37,500	187,500	550,000	1,875,000	4,000,000	18,750,000	37,500,000
14		Max	5,500	50,000	250,000	750,000	2,500,000	5,500,000	25,000,000	50,000,000
15	H	Min	5,500	50,000	250,000	750,000	2,500,000	5,500,000	25,000,000	50,000,000
16		ML	7,750	75,000	375,000	1,125,000	4,000,000	7,750,000	37,500,000	75,000,000
17		Max	10,000	100,000	500,000	1,500,000	5,500,000	10,000,000	50,000,000	100,000,000
18	VH	Min	10,000	100,000	500,000	1,500,000	5,500,000	10,000,000	50,000,000	100,000,000
19		ML	15,000	150,000	750,000	2,250,000	7,750,000	15,000,000	75,000,000	150,000,000
20		Max	20,000	200,000	1,000,000	3,000,000	10,000,000	20,000,000	100,000,000	200,000,000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
22	Impact	Min	ML	Max
23	N	0	#N/A	#N/A
24	VL	#N/A	#N/A	#N/A
25	L	#N/A	#N/A	#N/A
26	M	#N/A	#N/A	#N/A
27	H	#N/A	#N/A	#N/A
28	VH	#N/A	#N/A	#N/A

<tbody>
</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.