Index match nth value multiple criteria

ldarley

Board Regular
Joined
Apr 10, 2012
Messages
104
Office Version
  1. 2019
  2. 2016
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))}
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,427
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJK
1PostcodeIn use?LatitudeLongitudeEastingNorthingGrid RefCountyDistrictWard
2RG9 6YJNo51.593958-0.914808475269188903SU752889BuckinghamshireWycombeHambleden Valley
3PL32 9RHYes50.587138-4.70173920884779863SX088798CornwallCornwallSt Teath and St BrewardGreater Manchester
4DH9 6TQYes54.866323-1.688048420120552438NZ201524County DurhamCounty DurhamStanleyOldham
5CA11 0SAYes54.615912-2.908883341403524908NY414249CumbriaEdenUllswaterRoyton South
6CA10 3JEYes54.526308-2.584611362261514715NY622147CumbriaEdenCrosby RavensworthSt Mary's
7DE4 5GLYes53.113157-1.512498432729357441SK327574DerbyshireAmber ValleyAlportShaw
8S33 9JLYes53.327934-1.741022417346381255SK173812DerbyshireDerbyshire DalesBradwell
9SK22 1DFYes53.375915-1.978091401555386562SK015865DerbyshireHigh PeakNew Mills East
10EX6 6ANYes50.716241-3.66843728231092062SX823920DevonTeignbridgeTeign Valley
11EX22 7NBYes50.849725-4.33216235926108170SS359081DevonTorridgeWaldon
12SP7 8HAYes51.003982-2.197881386212122751ST862227DorsetNorth DorsetShaftesbury West
13GL53 9NHYes51.839061-2.052204396500215607SO965156GloucestershireCotswoldErmin
14OL2 6JGYes53.560404-2.105381393117407092SD931070Greater ManchesterOldhamRoyton South
15OL1 9LNNo53.541497-2.099827393482404988SD934049Greater ManchesterOldhamSt Mary's
16OL2 8RPYes53.574264-2.09492393812408633SD938086Greater ManchesterOldhamShaw
17SK6 7HWYes53.384991-2.058445396210387573SJ962875Greater ManchesterStockportMarple South
18BB1 2JNYes53.740811-2.448947370486427252SD704272LancashireBlackburn with DarwenShadsworth with Whitebirk
19BB8 0PGYes53.857663-2.157957389708440171SD897401LancashirePendleBoulsworth
20BB12 9QJYes53.83691-2.256667383207437881SD832378LancashirePendleOld Laund Booth
21DL6 3AQYes54.372545-1.289145446278497681SE462976North YorkshireHambletonOsmotherley & Swainby
22YO18 8REYes54.36231-0.898006471705496869SE717968North YorkshireRyedaleDales
23DY14 0JBNo52.409181-2.5448183630361110SO630791ShropshireShropshireCleobury Mortimer
24WS12 2EWYes52.691305-1.977617401610310402SK016104StaffordshireCannock ChaseHeath Hayes East and Wimblebury
25WS12 0QHYes52.712374-1.96723402311312746SK023127StaffordshireCannock ChaseRawnsley
Main
Cell Formulas
RangeFormula
K5:K7K5=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)))
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
Unless you need to work with excel 2007 or older, you could use this method (does not need to be array confirmed).
Excel Formula:
=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

Board Regular
Joined
Apr 10, 2012
Messages
104
Office Version
  1. 2019
  2. 2016
Thanks Fluff and Jasonb75 that works perfectly, never used the aggregate formula before it looks super versatile!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,427
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,381
Messages
5,624,360
Members
416,024
Latest member
Illissa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top