Index match nth value multiple criteria

[ldarley]

Hi

I've been using the following formula to obtain a list of values from a table that match one set criteria, I've tried a range of ways to adapt this to allow me to match against two different criteria but they all generate the a spurious result. Any pointers on how I can use index to match against the nth value based on two sets of criteria?

Thanks

{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Postcode	In use?	Latitude	Longitude	Easting	Northing	Grid Ref	County	District	Ward
2	RG9 6YJ	No	51.593958	-0.914808	475269	188903	SU752889	Buckinghamshire	Wycombe	Hambleden Valley
3	PL32 9RH	Yes	50.587138	-4.701739	208847	79863	SX088798	Cornwall	Cornwall	St Teath and St Breward	Greater Manchester
4	DH9 6TQ	Yes	54.866323	-1.688048	420120	552438	NZ201524	County Durham	County Durham	Stanley	Oldham
5	CA11 0SA	Yes	54.615912	-2.908883	341403	524908	NY414249	Cumbria	Eden	Ullswater	Royton South
6	CA10 3JE	Yes	54.526308	-2.584611	362261	514715	NY622147	Cumbria	Eden	Crosby Ravensworth	St Mary's
7	DE4 5GL	Yes	53.113157	-1.512498	432729	357441	SK327574	Derbyshire	Amber Valley	Alport	Shaw
8	S33 9JL	Yes	53.327934	-1.741022	417346	381255	SK173812	Derbyshire	Derbyshire Dales	Bradwell
9	SK22 1DF	Yes	53.375915	-1.978091	401555	386562	SK015865	Derbyshire	High Peak	New Mills East
10	EX6 6AN	Yes	50.716241	-3.668437	282310	92062	SX823920	Devon	Teignbridge	Teign Valley
11	EX22 7NB	Yes	50.849725	-4.33216	235926	108170	SS359081	Devon	Torridge	Waldon
12	SP7 8HA	Yes	51.003982	-2.197881	386212	122751	ST862227	Dorset	North Dorset	Shaftesbury West
13	GL53 9NH	Yes	51.839061	-2.052204	396500	215607	SO965156	Gloucestershire	Cotswold	Ermin
14	OL2 6JG	Yes	53.560404	-2.105381	393117	407092	SD931070	Greater Manchester	Oldham	Royton South
15	OL1 9LN	No	53.541497	-2.099827	393482	404988	SD934049	Greater Manchester	Oldham	St Mary's
16	OL2 8RP	Yes	53.574264	-2.09492	393812	408633	SD938086	Greater Manchester	Oldham	Shaw
17	SK6 7HW	Yes	53.384991	-2.058445	396210	387573	SJ962875	Greater Manchester	Stockport	Marple South
18	BB1 2JN	Yes	53.740811	-2.448947	370486	427252	SD704272	Lancashire	Blackburn with Darwen	Shadsworth with Whitebirk
19	BB8 0PG	Yes	53.857663	-2.157957	389708	440171	SD897401	Lancashire	Pendle	Boulsworth
20	BB12 9QJ	Yes	53.83691	-2.256667	383207	437881	SD832378	Lancashire	Pendle	Old Laund Booth
21	DL6 3AQ	Yes	54.372545	-1.289145	446278	497681	SE462976	North Yorkshire	Hambleton	Osmotherley & Swainby
22	YO18 8RE	Yes	54.36231	-0.898006	471705	496869	SE717968	North Yorkshire	Ryedale	Dales
23	DY14 0JB	No	52.409181	-2.544818	363036	1110	SO630791	Shropshire	Shropshire	Cleobury Mortimer
24	WS12 2EW	Yes	52.691305	-1.977617	401610	310402	SK016104	Staffordshire	Cannock Chase	Heath Hayes East and Wimblebury
25	WS12 0QH	Yes	52.712374	-1.96723	402311	312746	SK023127	Staffordshire	Cannock Chase	Rawnsley
Main

Cell Formulas
Range	Formula
K5:K7	K5	=INDEX($J$2:$J$25,AGGREGATE(15,6,(ROW($H$2:$H$25)-ROW($H$2)+1)/($H$2:$H$25=$K$3)/($I$2:$I$25=$K$4),ROWS(K$4:K4)))

 

[jasonb75]

Unless you need to work with excel 2007 or older, you could use this method (does not need to be array confirmed).

=INDEX(Whole_Column,AGGREGATE(15,6,ROW(range1)/(range1=crit1)/(range2=crit2),nth))

Using the whole column for the index array reduces the number of calculation steps, making the formula more efficient.

 

[ldarley]

Thanks Fluff and Jasonb75 that works perfectly, never used the aggregate formula before it looks super versatile!

 

[Fluff]

Glad we could help & thanks for the feedback.