Index Formula with One Condition Issue

coa747

New Member
Joined
Aug 2, 2016
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I have a data sheet with four different locations and noise values for each location. I want to create a Top 10 list for each location, the problem is that some of the noise values are the same across multiple locations. So when I use the Index formula sometimes it returns the wrong location as it finds the first instance of the value and reports that instead of making sure it references the proper location. The issue occurs in the Index formula in cell W28 which references the formula in V28. The value is 74.4 but instead of returning SYV-02 Dona Ave, it returns SYV-01 Ortega Park. I'm guessing this is happening because there are two noise value matches of 74.4 in column B one for SYV-01 Ortega Park and one for SYV-02 Dona Ave. In this case, I want the formula to ignore the value associated with location Ortega Park. Can someone tell me what I'm doing wrong?

SJC South Flow Landings Q3-2021.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1NMT_NameLmaxSELStart_TimeDurationEnd_TimeAltitudePCA_DistanceAC_TypeCallsignRegistrationDep_AirportApproachArr_AirportDirection
2SYV-01 Ortega Park83.695.69/1/2021 11:04559/1/2021 11:053200238GLEXN1886SN1886SKASEILS 12RKSJClandingA/C TypeDayMonthTimeAirportLmaxNMT_Name
3SYV-01 Ortega Park78.189.19/16/2021 9:47589/16/2021 9:483500262LJ60DLX401N401SYKONTILS 12LKSJClandingGLEX1Sep11:04 AMKASE83.6SYV-01 Ortega Park
4SYV-03 Community Center Park77.686.98/17/2021 21:57488/17/2021 21:5834001441B738UAL607N13248KIAHRNP Z 12L/RKSJClandingLJ6016Sep9:47 AMKONT78.1SYV-01 Ortega Park
5SYV-01 Ortega Park76.086.89/16/2021 8:55559/16/2021 8:563600211CL30LXJ524N524FXKHDNILS 12LKSJClandingCL3016Sep8:55 AMKHDN76.0SYV-01 Ortega Park
6SYV-01 Ortega Park74.481.27/14/2021 7:22397/14/2021 7:223100199CL35LXJ588N588FXKSBAILS 12RKSJClandingCL3514Jul7:22 AMKSBA74.4SYV-01 Ortega Park
7SYV-02 Dona Ave74.481.89/2/2021 0:40469/2/2021 0:412900228CL30LXJ533N533FXKLAXILS 12RKSJClandingB73716Sep9:40 AMKBUR73.9SYV-01 Ortega Park
8SYV-04 Washington Park74.384.98/31/2021 11:29488/31/2021 11:3029001649E75LQXE2272N647QXKSANRNP Z 12L/RKSJClandingCL3019Aug10:29 AMKDAL73.3SYV-01 Ortega Park
9SYV-02 Dona Ave74.080.89/16/2021 10:04309/16/2021 10:042600289B737SWA2934N7726AKBNARNP Z 12L/RKSJClandingCL301Sep10:00 AMKPRB72.9SYV-01 Ortega Park
10SYV-01 Ortega Park73.985.59/16/2021 9:40629/16/2021 9:413900249B737SWA5884N7733BKBURRNP Z 12L/RKSJClandingB73823Jul10:44 AMKDFW72.8SYV-01 Ortega Park
11SYV-02 Dona Ave73.881.98/21/2021 9:08518/21/2021 9:093300283B738SWA3944N8630BKLAXRNP Z 12L/RKSJClandingCL3020Aug10:46 AMKCRQ71.9SYV-01 Ortega Park
12SYV-02 Dona Ave73.781.77/4/2021 10:54457/4/2021 10:553000347B738AAL2046N836NNKDFWRNP Z 12L/RKSJClandingCL3521Aug4:07 PMKMVY71.7SYV-01 Ortega Park
13SYV-02 Dona Ave73.781.48/19/2021 10:30408/19/2021 10:303000396CL30N197JSN197JSKDALILS 12RKSJClanding
14SYV-01 Ortega Park73.382.08/19/2021 10:29378/19/2021 10:303300282CL30N197JSN197JSKDALILS 12RKSJClandingA/C TypeDayMonthTimeAirport55-64 dBA65-74 dBA
15SYV-02 Dona Ave73.380.37/14/2021 11:19337/14/2021 11:193000286CL30XOJ550N550XJKOXRILS 12RKSJClandingGLEX1Sep11:04 AMKASE
16SYV-02 Dona Ave73.182.47/4/2021 9:55467/4/2021 9:553000277B38MSWA2145N8723QKSANRNP Z 12L/RKSJClandingLJ6016Sep9:47 AMKONT
17SYV-01 Ortega Park72.980.99/1/2021 10:00349/1/2021 10:013100270CL30N825DTN825DTKPRBILS 12RKSJClandingCL3016Sep8:55 AMKHDN
18SYV-02 Dona Ave72.979.69/16/2021 8:45319/16/2021 8:453200221BCS1DAL2377N104DUKSLCILS 12LKSJClandingCL3514Jul7:22 AMKSBA 74.4
19SYV-01 Ortega Park72.878.97/23/2021 10:44307/23/2021 10:443600176B738AAL2046N846NNKDFWRNP Z 12L/RKSJClandingB73716Sep9:40 AMKBUR 73.9
20SYV-02 Dona Ave72.881.78/21/2021 13:31528/21/2021 13:322800142P180N360MCN360MCMMSDILS 12RKSJClandingCL3019Aug10:29 AMKDAL 73.3
21SYV-02 Dona Ave72.781.77/14/2021 10:26487/14/2021 10:272900291B737SWA5537N7868KKLAXRNP Z 12L/RKSJClandingCL301Sep10:00 AMKPRB 72.9
22SYV-04 Washington Park72.581.27/5/2021 8:03407/5/2021 8:0429001615E75LSKW3462N181SYKLAXRNP Z 12L/RKSJClandingB73823Jul10:44 AMKDFW 72.8
23SYV-02 Dona Ave72.381.07/4/2021 11:44457/4/2021 11:443000256B738SWA751N8316HKPHXRNP Z 12L/RKSJClandingCL3020Aug10:46 AMKCRQ 71.9
24SYV-02 Dona Ave72.080.38/20/2021 10:46398/20/2021 10:473300339CL30LXJ531N531FXKCRQILS 12RKSJClandingCL3521Aug4:07 PMKMVY 71.7
25SYV-02 Dona Ave72.080.99/16/2021 11:09369/16/2021 11:103000272E75LQXE2081N642QXKBOIRNP Z 12L/RKSJClanding
26SYV-01 Ortega Park71.980.88/20/2021 10:46428/20/2021 10:473700296CL30LXJ531N531FXKCRQILS 12RKSJClanding
27SYV-02 Dona Ave71.980.37/5/2021 9:18467/5/2021 9:193000303B737SWA1246N7855AKDALRNP Z 12L/RKSJClandingA/C TypeDayMonthTimeAirportLmaxNMT_Name
28SYV-02 Dona Ave71.881.69/1/2021 9:21539/1/2021 9:223000258E75LSKW3608N304SYKSEARNP Z 12L/RKSJClandingCL3514Jul7:22 AMKSBA74.4SYV-01 Ortega Park
29SYV-02 Dona Ave71.880.89/16/2021 8:56449/16/2021 8:563100426CL30LXJ524N524FXKHDNILS 12LKSJClandingB73716Sep10:04 AMKBNA74.0SYV-02 Dona Ave
30SYV-01 Ortega Park71.781.68/21/2021 16:07378/21/2021 16:073400216CL35LXJ598N598FXKMVYILS 12RKSJClandingB73821Aug9:08 AMKLAX73.8SYV-02 Dona Ave
31SYV-02 Dona Ave71.781.07/4/2021 10:52577/4/2021 10:533000242E75LQXE2022N634QXKLAXRNP Z 12L/RKSJClandingB7384Jul10:54 AMKDFW73.7SYV-02 Dona Ave
32SYV-02 Dona Ave71.781.67/5/2021 10:15547/5/2021 10:162900276B38MSWA2145N8748QKSANRNP Z 12L/RKSJClandingB7384Jul10:54 AMKDFW73.7SYV-02 Dona Ave
33SYV-02 Dona Ave71.780.48/22/2021 10:41518/22/2021 10:423000302B738AAL2046N945ANKDFWRNP Z 12L/RKSJClandingCL3019Aug10:29 AMKDAL73.3SYV-01 Ortega Park
34SYV-04 Washington Park71.677.68/19/2021 12:00338/19/2021 12:0036001685B738SWA1367N8503AKSANILS 12RKSJClandingB38M4Jul9:55 AMKSAN73.1SYV-02 Dona Ave
35SYV-01 Ortega Park71.579.79/2/2021 11:31379/2/2021 11:323000265CL35XOJ356KVNYILS 12RKSJClandingCL301Sep10:00 AMKPRB72.9SYV-01 Ortega Park
36SYV-01 Ortega Park71.483.89/16/2021 9:38709/16/2021 9:393600165E75LSKW5525N113SYKDENRNP Z 12L/RKSJClandingB73823Jul10:44 AMKDFW72.8SYV-01 Ortega Park
37SYV-01 Ortega Park71.379.47/14/2021 10:25387/14/2021 10:264300212B737SWA5537N7868KKLAXRNP Z 12L/RKSJClandingB73714Jul10:26 AMKLAX72.7SYV-02 Dona Ave
Sheet2
Cell Formulas
RangeFormula
Q3,Q28Q3=INDEX($I$2:$I$1317,MATCH(V3,$B$2:$B$1317,0))
R3:R12,R28:R37R3=INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0))
S3:S12,S28:S37S3=INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0))
T3:T12,T28:T37T3=INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0))
U3:U12,U28:U37U3=INDEX($L$2:$L$1317,MATCH(V3,$B$2:$B$1317,0))
V3V3=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),1)
W3,W28:W29,W33W3=INDEX($A$2:$A$1317,MATCH(V3,$B$2:$B$1317,0))
Q4:Q12,Q29:Q37Q4=INDEX($I$2:$I$1317,MATCH(V4,$B$2:$B$1317,0))
V4V4=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),2)
W4:W12,W30:W32,W34:W37W4=INDEX($A$2:$A$1317,MATCH(V4,$B$2:$B$1317,0))
V5V5=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),3)
V6V6=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),4)
V7V7=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),5)
V8V8=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),6)
V9V9=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),7)
V10V10=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),8)
V11V11=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),9)
V12V12=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),10)
Q15:U24Q15=Q3
V15:V24V15=(IF(AND(V3>=55,V3<65),V3," "))
W15:W24W15=(IF(AND(V3>=65,V3<75),V3," "))
V28V28=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),1)
V29V29=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),2)
V30V30=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),3)
V31V31=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),4)
V32V32=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),5)
V33V33=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),6)
V34V34=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),7)
V35V35=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),8)
V36V36=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),9)
V37V37=LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),10)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,247
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,457
Office Version
  1. 2016
Platform
  1. Windows
Hi COA747,

I know Fluff will be back with the 365 approach but I'll persevere with my 2016 solution.

Your column V formula has the hard-coded "SYV-02 Dona Ave" and W could use the same

Excel Formula:
=INDEX($A$2:$A$1317,MATCH(1,INDEX(($B$2:$B$1317=V28)*($A$2:$A$1317="SYV-02 Dona Ave"),0,1),0))

...although I'd suggest not hiding that "SYV-02 Dona Ave" so maybe put it in Q26 and refer to it in the V and W formula for row 28 and below?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,247
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for that, how about for Q28, filled down
Excel Formula:
=INDEX($I$2:$I$1317,AGGREGATE(15,6,(ROW($A$2:$A$1317)-ROW($A$2)+1)/($A$2:$A$1317="SYV-02 Dona Ave")/($B$2:$B$1317=V28),COUNTIFS(V$28:V28,V28)))
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,247
Office Version
  1. 365
Platform
  1. Windows
Another option using the dynamic functions
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1NMT_NameLmaxSELStart_TimeDurationEnd_TimeAltitudePCA_DistanceAC_TypeCallsignRegistrationDep_AirportApproachArr_AirportDirection
2SYV-01 Ortega Park83.695.644440.46145544440.4623200238GLEXN1886SN1886SKASEILS 12RKSJClandingA/C TypeDayMonthTimeAirportLmaxNMT_Name
3SYV-01 Ortega Park78.189.144455.4085844455.40873500262LJ60DLX401N401SYKONTILS 12LKSJClandingGLEX44440.461444440.461444440.4614KASE83.6SYV-01 Ortega Park
4SYV-03 Community Center Park77.686.944425.91484844425.915434001441B738UAL607N13248KIAHRNP Z 12L/RKSJClandingLJ6044455.40844455.40844455.408KONT78.1SYV-01 Ortega Park
5SYV-01 Ortega Park7686.844455.37215544455.37273600211CL30LXJ524N524FXKHDNILS 12LKSJClandingCL3044455.372144455.372144455.3721KHDN76SYV-01 Ortega Park
6SYV-01 Ortega Park74.481.244391.30713944391.30763100199CL35LXJ588N588FXKSBAILS 12RKSJClandingCL3544391.307144391.307144391.3071KSBA74.4SYV-01 Ortega Park
7SYV-02 Dona Ave74.481.844441.02824644441.02872900228CL30LXJ533N533FXKLAXILS 12RKSJClandingB73744455.403444455.403444455.4034KBUR73.9SYV-01 Ortega Park
8SYV-04 Washington Park74.384.944439.47894844439.479429001649E75LQXE2272N647QXKSANRNP Z 12L/RKSJClandingCL3044427.437244427.437244427.4372KDAL73.3SYV-01 Ortega Park
9SYV-02 Dona Ave7480.844455.41963044455.41992600289B737SWA2934N7726AKBNARNP Z 12L/RKSJClandingCL3044440.41744440.41744440.417KPRB72.9SYV-01 Ortega Park
10SYV-01 Ortega Park73.985.544455.40346244455.40413900249B737SWA5884N7733BKBURRNP Z 12L/RKSJClandingB73844400.447544400.447544400.4475KDFW72.8SYV-01 Ortega Park
11SYV-02 Dona Ave73.881.944429.38095144429.38153300283B738SWA3944N8630BKLAXRNP Z 12L/RKSJClandingCL3044428.448944428.448944428.4489KCRQ71.9SYV-01 Ortega Park
12SYV-02 Dona Ave73.781.744381.45444544381.4553000347B738AAL2046N836NNKDFWRNP Z 12L/RKSJClandingCL3544429.671744429.671744429.6717KMVY71.7SYV-01 Ortega Park
13SYV-02 Dona Ave73.781.444427.43754044427.4383000396CL30N197JSN197JSKDALILS 12RKSJClanding
14SYV-01 Ortega Park73.38244427.43723744427.43773300282CL30N197JSN197JSKDALILS 12RKSJClandingA/C TypeDayMonthTimeAirport55-64 dBA65-74 dBA
15SYV-02 Dona Ave73.380.344391.47173344391.47213000286CL30XOJ550N550XJKOXRILS 12RKSJClandingGLEX44440.461444440.461444440.4614KASE
16SYV-02 Dona Ave73.182.444381.41334644381.41383000277B38MSWA2145N8723QKSANRNP Z 12L/RKSJClandingLJ6044455.40844455.40844455.408KONT
17SYV-01 Ortega Park72.980.944440.4173444440.41743100270CL30N825DTN825DTKPRBILS 12RKSJClandingCL3044455.372144455.372144455.3721KHDN
18SYV-02 Dona Ave72.979.644455.36473144455.36513200221BCS1DAL2377N104DUKSLCILS 12LKSJClandingCL3544391.307144391.307144391.3071KSBA 74.4
19SYV-01 Ortega Park72.878.944400.44753044400.44793600176B738AAL2046N846NNKDFWRNP Z 12L/RKSJClandingB73744455.403444455.403444455.4034KBUR 73.9
20SYV-02 Dona Ave72.881.744429.56335244429.56392800142P180N360MCN360MCMMSDILS 12RKSJClandingCL3044427.437244427.437244427.4372KDAL 73.3
21SYV-02 Dona Ave72.781.744391.4354844391.43552900291B737SWA5537N7868KKLAXRNP Z 12L/RKSJClandingCL3044440.41744440.41744440.417KPRB 72.9
22SYV-04 Washington Park72.581.244382.33574044382.336129001615E75LSKW3462N181SYKLAXRNP Z 12L/RKSJClandingB73844400.447544400.447544400.4475KDFW 72.8
23SYV-02 Dona Ave72.38144381.4894544381.48953000256B738SWA751N8316HKPHXRNP Z 12L/RKSJClandingCL3044428.448944428.448944428.4489KCRQ 71.9
24SYV-02 Dona Ave7280.344428.44923944428.44963300339CL30LXJ531N531FXKCRQILS 12RKSJClandingCL3544429.671744429.671744429.6717KMVY 71.7
25SYV-02 Dona Ave7280.944455.46513644455.46553000272E75LQXE2081N642QXKBOIRNP Z 12L/RKSJClanding
26SYV-01 Ortega Park71.980.844428.44894244428.44943700296CL30LXJ531N531FXKCRQILS 12RKSJClandingSYV-02 Dona Ave
27SYV-02 Dona Ave71.980.344382.38784644382.38833000303B737SWA1246N7855AKDALRNP Z 12L/RKSJClandingA/C TypeDayMonthTimeAirportLmaxNMT_Name
28SYV-02 Dona Ave71.881.644440.38975344440.39033000258E75LSKW3608N304SYKSEARNP Z 12L/RKSJClandingCL302Sep12:40 AMKLAX74.4SYV-02 Dona Ave
29SYV-02 Dona Ave71.880.844455.37244444455.37293100426CL30LXJ524N524FXKHDNILS 12LKSJClandingB73716Sep10:04 AMKBNA74SYV-02 Dona Ave
30SYV-01 Ortega Park71.781.644429.67173744429.67213400216CL35LXJ598N598FXKMVYILS 12RKSJClandingB73821Aug9:08 AMKLAX73.8SYV-02 Dona Ave
31SYV-02 Dona Ave71.78144381.45325744381.45393000242E75LQXE2022N634QXKLAXRNP Z 12L/RKSJClandingB7384Jul10:54 AMKDFW73.7SYV-02 Dona Ave
32SYV-02 Dona Ave71.781.644382.42765444382.42832900276B38MSWA2145N8748QKSANRNP Z 12L/RKSJClandingCL3019Aug10:30 AMKDAL73.7SYV-02 Dona Ave
33SYV-02 Dona Ave71.780.444430.44555144430.4463000302B738AAL2046N945ANKDFWRNP Z 12L/RKSJClandingCL3014Jul11:19 AMKOXR73.3SYV-02 Dona Ave
34SYV-04 Washington Park71.677.644427.50023344427.500636001685B738SWA1367N8503AKSANILS 12RKSJClandingB38M4Jul9:55 AMKSAN73.1SYV-02 Dona Ave
35SYV-01 Ortega Park71.579.744441.48033744441.48073000265CL35XOJ356KVNYILS 12RKSJClandingBCS116Sep8:45 AMKSLC72.9SYV-02 Dona Ave
36SYV-01 Ortega Park71.483.844455.4027044455.40283600165E75LSKW5525N113SYKDENRNP Z 12L/RKSJClandingP18021Aug1:31 PMMMSD72.8SYV-02 Dona Ave
37SYV-01 Ortega Park71.379.444391.43473844391.43524300212B737SWA5537N7868KKLAXRNP Z 12L/RKSJClandingB73714Jul10:26 AMKLAX72.7SYV-02 Dona Ave
Main
Cell Formulas
RangeFormula
Q3:Q12Q3=INDEX($I$2:$I$1317,MATCH(V3,$B$2:$B$1317,0))
R3:R12R3=INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0))
S3:S12S3=INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0))
T3:T12T3=INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0))
U3:U12U3=INDEX($L$2:$L$1317,MATCH(V3,$B$2:$B$1317,0))
V3V3=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),1)
W3:W12W3=INDEX($A$2:$A$1317,MATCH(V3,$B$2:$B$1317,0))
V4V4=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),2)
V5V5=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),3)
V6V6=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),4)
V7V7=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),5)
V8V8=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),6)
V9V9=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),7)
V10V10=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),8)
V11V11=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),9)
V12V12=LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),10)
Q15:U24Q15=Q3
V15:V24V15=(IF(AND(V3>=55,V3<65),V3," "))
W15:W24W15=(IF(AND(V3>=65,V3<75),V3," "))
Q28:Q37Q28=INDEX(SORT(FILTER(B2:I37,A2:A37=Q26),1,-1),SEQUENCE(10),8)
R28:R37R28=INDEX(SORT(FILTER(B2:D37,A2:A37=Q26),1,-1),SEQUENCE(10),3)
S28:S37S28=INDEX(SORT(FILTER(B2:D37,A2:A37=Q26),1,-1),SEQUENCE(10),3)
T28:T37T28=INDEX(SORT(FILTER(B2:D37,A2:A37=Q26),1,-1),SEQUENCE(10),3)
U28:U37U28=INDEX(SORT(FILTER(B2:L37,A2:A37=Q26),1,-1),SEQUENCE(10),11)
V28:W37V28=INDEX(SORT(FILTER(A2:B37,A2:A37=Q26),2,-1),SEQUENCE(10),{2,1})
Dynamic array formulas.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,247
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option using one formula, I've put it in Q3
+Fluff 1.xlsm
GHIJKLMNOPQRSTUVW
1AltitudePCA_DistanceAC_TypeCallsignRegistrationDep_AirportApproachArr_AirportDirectionSYV-01 Ortega Park
23200238GLEXN1886SN1886SKASEILS 12RKSJClandingA/C TypeDayMonthTimeAirportLmaxNMT_Name
33500262LJ60DLX401N401SYKONTILS 12LKSJClandingGLEX1Sep11:04 AMKASE83.6SYV-01 Ortega Park
434001441B738UAL607N13248KIAHRNP Z 12L/RKSJClandingLJ6016Sep9:47 AMKONT78.1SYV-01 Ortega Park
53600211CL30LXJ524N524FXKHDNILS 12LKSJClandingCL3016Sep8:55 AMKHDN76SYV-01 Ortega Park
63100199CL35LXJ588N588FXKSBAILS 12RKSJClandingCL3514Jul7:22 AMKSBA74.4SYV-01 Ortega Park
72900228CL30LXJ533N533FXKLAXILS 12RKSJClandingB73716Sep9:40 AMKBUR73.9SYV-01 Ortega Park
829001649E75LQXE2272N647QXKSANRNP Z 12L/RKSJClandingCL3019Aug10:29 AMKDAL73.3SYV-01 Ortega Park
92600289B737SWA2934N7726AKBNARNP Z 12L/RKSJClandingCL301Sep10:00 AMKPRB72.9SYV-01 Ortega Park
103900249B737SWA5884N7733BKBURRNP Z 12L/RKSJClandingB73823Jul10:44 AMKDFW72.8SYV-01 Ortega Park
113300283B738SWA3944N8630BKLAXRNP Z 12L/RKSJClandingCL3020Aug10:46 AMKCRQ71.9SYV-01 Ortega Park
123000347B738AAL2046N836NNKDFWRNP Z 12L/RKSJClandingCL3521Aug4:07 PMKMVY71.7SYV-01 Ortega Park
133000396CL30N197JSN197JSKDALILS 12RKSJClanding
143300282CL30N197JSN197JSKDALILS 12RKSJClandingA/C TypeDayMonthTimeAirport55-64 dBA65-74 dBA
153000286CL30XOJ550N550XJKOXRILS 12RKSJClandingGLEX1Sep11:04 AMKASE
163000277B38MSWA2145N8723QKSANRNP Z 12L/RKSJClandingLJ6016Sep9:47 AMKONT
173100270CL30N825DTN825DTKPRBILS 12RKSJClandingCL3016Sep8:55 AMKHDN
183200221BCS1DAL2377N104DUKSLCILS 12LKSJClandingCL3514Jul7:22 AMKSBA 74.4
193600176B738AAL2046N846NNKDFWRNP Z 12L/RKSJClandingB73716Sep9:40 AMKBUR 73.9
202800142P180N360MCN360MCMMSDILS 12RKSJClandingCL3019Aug10:29 AMKDAL 73.3
212900291B737SWA5537N7868KKLAXRNP Z 12L/RKSJClandingCL301Sep10:00 AMKPRB 72.9
2229001615E75LSKW3462N181SYKLAXRNP Z 12L/RKSJClandingB73823Jul10:44 AMKDFW 72.8
233000256B738SWA751N8316HKPHXRNP Z 12L/RKSJClandingCL3020Aug10:46 AMKCRQ 71.9
243300339CL30LXJ531N531FXKCRQILS 12RKSJClandingCL3521Aug4:07 PMKMVY 71.7
253000272E75LQXE2081N642QXKBOIRNP Z 12L/RKSJClanding
263700296CL30LXJ531N531FXKCRQILS 12RKSJClandingSYV-02 Dona Ave
273000303B737SWA1246N7855AKDALRNP Z 12L/RKSJClandingA/C TypeDayMonthTimeAirportLmaxNMT_Name
283000258E75LSKW3608N304SYKSEARNP Z 12L/RKSJClandingCL302Sep12:40 AMKLAX74.4SYV-02 Dona Ave
293100426CL30LXJ524N524FXKHDNILS 12LKSJClandingB73716Sep10:04 AMKBNA74SYV-02 Dona Ave
303400216CL35LXJ598N598FXKMVYILS 12RKSJClandingB73821Aug9:08 AMKLAX73.8SYV-02 Dona Ave
313000242E75LQXE2022N634QXKLAXRNP Z 12L/RKSJClandingB7384Jul10:54 AMKDFW73.7SYV-02 Dona Ave
322900276B38MSWA2145N8748QKSANRNP Z 12L/RKSJClandingCL3019Aug10:30 AMKDAL73.7SYV-02 Dona Ave
333000302B738AAL2046N945ANKDFWRNP Z 12L/RKSJClandingCL3014Jul11:19 AMKOXR73.3SYV-02 Dona Ave
3436001685B738SWA1367N8503AKSANILS 12RKSJClandingB38M4Jul9:55 AMKSAN73.1SYV-02 Dona Ave
353000265CL35XOJ356KVNYILS 12RKSJClandingBCS116Sep8:45 AMKSLC72.9SYV-02 Dona Ave
363600165E75LSKW5525N113SYKDENRNP Z 12L/RKSJClandingP18021Aug1:31 PMMMSD72.8SYV-02 Dona Ave
374300212B737SWA5537N7868KKLAXRNP Z 12L/RKSJClandingB73714Jul10:26 AMKLAX72.7SYV-02 Dona Ave
Main
Cell Formulas
RangeFormula
Q3:W12Q3=LET(Fltr,SORT(FILTER(A2:L300,A2:A300=Q1),2,-1),Ary,INDEX(Fltr,SEQUENCE(MIN(ROWS(Fltr),10)),{9,4,4,4,12,2,1}),Ary)
Q15:U24Q15=Q3
V15:V24V15=(IF(AND(V3>=55,V3<65),V3," "))
W15:W24W15=(IF(AND(V3>=65,V3<75),V3," "))
Q28:Q37Q28=INDEX(SORT(FILTER(B2:I37,A2:A37=Q26),1,-1),SEQUENCE(10),8)
R28:R37R28=INDEX(SORT(FILTER(B2:D37,A2:A37=Q26),1,-1),SEQUENCE(10),3)
S28:S37S28=INDEX(SORT(FILTER(B2:D37,A2:A37=Q26),1,-1),SEQUENCE(10),3)
T28:T37T28=INDEX(SORT(FILTER(B2:D37,A2:A37=Q26),1,-1),SEQUENCE(10),3)
U28:U37U28=INDEX(SORT(FILTER(B2:L37,A2:A37=Q26),1,-1),SEQUENCE(10),11)
V28:W37V28=INDEX(SORT(FILTER(A2:B37,A2:A37=Q26),2,-1),SEQUENCE(10),{2,1})
Dynamic array formulas.
 

coa747

New Member
Joined
Aug 2, 2016
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Thank you all for your help the combination of the Toadstool and Fluff's formulas solved the problems. Much appreciated.

Regards,

Greg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,247
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,403
Messages
5,769,865
Members
425,574
Latest member
grimeslisa

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