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 | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
1 | NMT_Name | Lmax | SEL | Start_Time | Duration | End_Time | Altitude | PCA_Distance | AC_Type | Callsign | Registration | Dep_Airport | Approach | Arr_Airport | Direction | ||||||||||
2 | SYV-01 Ortega Park | 83.6 | 95.6 | 9/1/2021 11:04 | 55 | 9/1/2021 11:05 | 3200 | 238 | GLEX | N1886S | N1886S | KASE | ILS 12R | KSJC | landing | A/C Type | Day | Month | Time | Airport | Lmax | NMT_Name | |||
3 | SYV-01 Ortega Park | 78.1 | 89.1 | 9/16/2021 9:47 | 58 | 9/16/2021 9:48 | 3500 | 262 | LJ60 | DLX401 | N401SY | KONT | ILS 12L | KSJC | landing | GLEX | 1 | Sep | 11:04 AM | KASE | 83.6 | SYV-01 Ortega Park | |||
4 | SYV-03 Community Center Park | 77.6 | 86.9 | 8/17/2021 21:57 | 48 | 8/17/2021 21:58 | 3400 | 1441 | B738 | UAL607 | N13248 | KIAH | RNP Z 12L/R | KSJC | landing | LJ60 | 16 | Sep | 9:47 AM | KONT | 78.1 | SYV-01 Ortega Park | |||
5 | SYV-01 Ortega Park | 76.0 | 86.8 | 9/16/2021 8:55 | 55 | 9/16/2021 8:56 | 3600 | 211 | CL30 | LXJ524 | N524FX | KHDN | ILS 12L | KSJC | landing | CL30 | 16 | Sep | 8:55 AM | KHDN | 76.0 | SYV-01 Ortega Park | |||
6 | SYV-01 Ortega Park | 74.4 | 81.2 | 7/14/2021 7:22 | 39 | 7/14/2021 7:22 | 3100 | 199 | CL35 | LXJ588 | N588FX | KSBA | ILS 12R | KSJC | landing | CL35 | 14 | Jul | 7:22 AM | KSBA | 74.4 | SYV-01 Ortega Park | |||
7 | SYV-02 Dona Ave | 74.4 | 81.8 | 9/2/2021 0:40 | 46 | 9/2/2021 0:41 | 2900 | 228 | CL30 | LXJ533 | N533FX | KLAX | ILS 12R | KSJC | landing | B737 | 16 | Sep | 9:40 AM | KBUR | 73.9 | SYV-01 Ortega Park | |||
8 | SYV-04 Washington Park | 74.3 | 84.9 | 8/31/2021 11:29 | 48 | 8/31/2021 11:30 | 2900 | 1649 | E75L | QXE2272 | N647QX | KSAN | RNP Z 12L/R | KSJC | landing | CL30 | 19 | Aug | 10:29 AM | KDAL | 73.3 | SYV-01 Ortega Park | |||
9 | SYV-02 Dona Ave | 74.0 | 80.8 | 9/16/2021 10:04 | 30 | 9/16/2021 10:04 | 2600 | 289 | B737 | SWA2934 | N7726A | KBNA | RNP Z 12L/R | KSJC | landing | CL30 | 1 | Sep | 10:00 AM | KPRB | 72.9 | SYV-01 Ortega Park | |||
10 | SYV-01 Ortega Park | 73.9 | 85.5 | 9/16/2021 9:40 | 62 | 9/16/2021 9:41 | 3900 | 249 | B737 | SWA5884 | N7733B | KBUR | RNP Z 12L/R | KSJC | landing | B738 | 23 | Jul | 10:44 AM | KDFW | 72.8 | SYV-01 Ortega Park | |||
11 | SYV-02 Dona Ave | 73.8 | 81.9 | 8/21/2021 9:08 | 51 | 8/21/2021 9:09 | 3300 | 283 | B738 | SWA3944 | N8630B | KLAX | RNP Z 12L/R | KSJC | landing | CL30 | 20 | Aug | 10:46 AM | KCRQ | 71.9 | SYV-01 Ortega Park | |||
12 | SYV-02 Dona Ave | 73.7 | 81.7 | 7/4/2021 10:54 | 45 | 7/4/2021 10:55 | 3000 | 347 | B738 | AAL2046 | N836NN | KDFW | RNP Z 12L/R | KSJC | landing | CL35 | 21 | Aug | 4:07 PM | KMVY | 71.7 | SYV-01 Ortega Park | |||
13 | SYV-02 Dona Ave | 73.7 | 81.4 | 8/19/2021 10:30 | 40 | 8/19/2021 10:30 | 3000 | 396 | CL30 | N197JS | N197JS | KDAL | ILS 12R | KSJC | landing | ||||||||||
14 | SYV-01 Ortega Park | 73.3 | 82.0 | 8/19/2021 10:29 | 37 | 8/19/2021 10:30 | 3300 | 282 | CL30 | N197JS | N197JS | KDAL | ILS 12R | KSJC | landing | A/C Type | Day | Month | Time | Airport | 55-64 dBA | 65-74 dBA | |||
15 | SYV-02 Dona Ave | 73.3 | 80.3 | 7/14/2021 11:19 | 33 | 7/14/2021 11:19 | 3000 | 286 | CL30 | XOJ550 | N550XJ | KOXR | ILS 12R | KSJC | landing | GLEX | 1 | Sep | 11:04 AM | KASE | |||||
16 | SYV-02 Dona Ave | 73.1 | 82.4 | 7/4/2021 9:55 | 46 | 7/4/2021 9:55 | 3000 | 277 | B38M | SWA2145 | N8723Q | KSAN | RNP Z 12L/R | KSJC | landing | LJ60 | 16 | Sep | 9:47 AM | KONT | |||||
17 | SYV-01 Ortega Park | 72.9 | 80.9 | 9/1/2021 10:00 | 34 | 9/1/2021 10:01 | 3100 | 270 | CL30 | N825DT | N825DT | KPRB | ILS 12R | KSJC | landing | CL30 | 16 | Sep | 8:55 AM | KHDN | |||||
18 | SYV-02 Dona Ave | 72.9 | 79.6 | 9/16/2021 8:45 | 31 | 9/16/2021 8:45 | 3200 | 221 | BCS1 | DAL2377 | N104DU | KSLC | ILS 12L | KSJC | landing | CL35 | 14 | Jul | 7:22 AM | KSBA | 74.4 | ||||
19 | SYV-01 Ortega Park | 72.8 | 78.9 | 7/23/2021 10:44 | 30 | 7/23/2021 10:44 | 3600 | 176 | B738 | AAL2046 | N846NN | KDFW | RNP Z 12L/R | KSJC | landing | B737 | 16 | Sep | 9:40 AM | KBUR | 73.9 | ||||
20 | SYV-02 Dona Ave | 72.8 | 81.7 | 8/21/2021 13:31 | 52 | 8/21/2021 13:32 | 2800 | 142 | P180 | N360MC | N360MC | MMSD | ILS 12R | KSJC | landing | CL30 | 19 | Aug | 10:29 AM | KDAL | 73.3 | ||||
21 | SYV-02 Dona Ave | 72.7 | 81.7 | 7/14/2021 10:26 | 48 | 7/14/2021 10:27 | 2900 | 291 | B737 | SWA5537 | N7868K | KLAX | RNP Z 12L/R | KSJC | landing | CL30 | 1 | Sep | 10:00 AM | KPRB | 72.9 | ||||
22 | SYV-04 Washington Park | 72.5 | 81.2 | 7/5/2021 8:03 | 40 | 7/5/2021 8:04 | 2900 | 1615 | E75L | SKW3462 | N181SY | KLAX | RNP Z 12L/R | KSJC | landing | B738 | 23 | Jul | 10:44 AM | KDFW | 72.8 | ||||
23 | SYV-02 Dona Ave | 72.3 | 81.0 | 7/4/2021 11:44 | 45 | 7/4/2021 11:44 | 3000 | 256 | B738 | SWA751 | N8316H | KPHX | RNP Z 12L/R | KSJC | landing | CL30 | 20 | Aug | 10:46 AM | KCRQ | 71.9 | ||||
24 | SYV-02 Dona Ave | 72.0 | 80.3 | 8/20/2021 10:46 | 39 | 8/20/2021 10:47 | 3300 | 339 | CL30 | LXJ531 | N531FX | KCRQ | ILS 12R | KSJC | landing | CL35 | 21 | Aug | 4:07 PM | KMVY | 71.7 | ||||
25 | SYV-02 Dona Ave | 72.0 | 80.9 | 9/16/2021 11:09 | 36 | 9/16/2021 11:10 | 3000 | 272 | E75L | QXE2081 | N642QX | KBOI | RNP Z 12L/R | KSJC | landing | ||||||||||
26 | SYV-01 Ortega Park | 71.9 | 80.8 | 8/20/2021 10:46 | 42 | 8/20/2021 10:47 | 3700 | 296 | CL30 | LXJ531 | N531FX | KCRQ | ILS 12R | KSJC | landing | ||||||||||
27 | SYV-02 Dona Ave | 71.9 | 80.3 | 7/5/2021 9:18 | 46 | 7/5/2021 9:19 | 3000 | 303 | B737 | SWA1246 | N7855A | KDAL | RNP Z 12L/R | KSJC | landing | A/C Type | Day | Month | Time | Airport | Lmax | NMT_Name | |||
28 | SYV-02 Dona Ave | 71.8 | 81.6 | 9/1/2021 9:21 | 53 | 9/1/2021 9:22 | 3000 | 258 | E75L | SKW3608 | N304SY | KSEA | RNP Z 12L/R | KSJC | landing | CL35 | 14 | Jul | 7:22 AM | KSBA | 74.4 | SYV-01 Ortega Park | |||
29 | SYV-02 Dona Ave | 71.8 | 80.8 | 9/16/2021 8:56 | 44 | 9/16/2021 8:56 | 3100 | 426 | CL30 | LXJ524 | N524FX | KHDN | ILS 12L | KSJC | landing | B737 | 16 | Sep | 10:04 AM | KBNA | 74.0 | SYV-02 Dona Ave | |||
30 | SYV-01 Ortega Park | 71.7 | 81.6 | 8/21/2021 16:07 | 37 | 8/21/2021 16:07 | 3400 | 216 | CL35 | LXJ598 | N598FX | KMVY | ILS 12R | KSJC | landing | B738 | 21 | Aug | 9:08 AM | KLAX | 73.8 | SYV-02 Dona Ave | |||
31 | SYV-02 Dona Ave | 71.7 | 81.0 | 7/4/2021 10:52 | 57 | 7/4/2021 10:53 | 3000 | 242 | E75L | QXE2022 | N634QX | KLAX | RNP Z 12L/R | KSJC | landing | B738 | 4 | Jul | 10:54 AM | KDFW | 73.7 | SYV-02 Dona Ave | |||
32 | SYV-02 Dona Ave | 71.7 | 81.6 | 7/5/2021 10:15 | 54 | 7/5/2021 10:16 | 2900 | 276 | B38M | SWA2145 | N8748Q | KSAN | RNP Z 12L/R | KSJC | landing | B738 | 4 | Jul | 10:54 AM | KDFW | 73.7 | SYV-02 Dona Ave | |||
33 | SYV-02 Dona Ave | 71.7 | 80.4 | 8/22/2021 10:41 | 51 | 8/22/2021 10:42 | 3000 | 302 | B738 | AAL2046 | N945AN | KDFW | RNP Z 12L/R | KSJC | landing | CL30 | 19 | Aug | 10:29 AM | KDAL | 73.3 | SYV-01 Ortega Park | |||
34 | SYV-04 Washington Park | 71.6 | 77.6 | 8/19/2021 12:00 | 33 | 8/19/2021 12:00 | 3600 | 1685 | B738 | SWA1367 | N8503A | KSAN | ILS 12R | KSJC | landing | B38M | 4 | Jul | 9:55 AM | KSAN | 73.1 | SYV-02 Dona Ave | |||
35 | SYV-01 Ortega Park | 71.5 | 79.7 | 9/2/2021 11:31 | 37 | 9/2/2021 11:32 | 3000 | 265 | CL35 | XOJ356 | KVNY | ILS 12R | KSJC | landing | CL30 | 1 | Sep | 10:00 AM | KPRB | 72.9 | SYV-01 Ortega Park | ||||
36 | SYV-01 Ortega Park | 71.4 | 83.8 | 9/16/2021 9:38 | 70 | 9/16/2021 9:39 | 3600 | 165 | E75L | SKW5525 | N113SY | KDEN | RNP Z 12L/R | KSJC | landing | B738 | 23 | Jul | 10:44 AM | KDFW | 72.8 | SYV-01 Ortega Park | |||
37 | SYV-01 Ortega Park | 71.3 | 79.4 | 7/14/2021 10:25 | 38 | 7/14/2021 10:26 | 4300 | 212 | B737 | SWA5537 | N7868K | KLAX | RNP Z 12L/R | KSJC | landing | B737 | 14 | Jul | 10:26 AM | KLAX | 72.7 | SYV-02 Dona Ave | |||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Q3,Q28 | Q3 | =INDEX($I$2:$I$1317,MATCH(V3,$B$2:$B$1317,0)) |
R3:R12,R28:R37 | R3 | =INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0)) |
S3:S12,S28:S37 | S3 | =INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0)) |
T3:T12,T28:T37 | T3 | =INDEX($D$2:$D$1317,MATCH(V3,$B$2:$B$1317,0)) |
U3:U12,U28:U37 | U3 | =INDEX($L$2:$L$1317,MATCH(V3,$B$2:$B$1317,0)) |
V3 | V3 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),1) |
W3,W28:W29,W33 | W3 | =INDEX($A$2:$A$1317,MATCH(V3,$B$2:$B$1317,0)) |
Q4:Q12,Q29:Q37 | Q4 | =INDEX($I$2:$I$1317,MATCH(V4,$B$2:$B$1317,0)) |
V4 | V4 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),2) |
W4:W12,W30:W32,W34:W37 | W4 | =INDEX($A$2:$A$1317,MATCH(V4,$B$2:$B$1317,0)) |
V5 | V5 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),3) |
V6 | V6 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),4) |
V7 | V7 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),5) |
V8 | V8 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),6) |
V9 | V9 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),7) |
V10 | V10 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),8) |
V11 | V11 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),9) |
V12 | V12 | =LARGE(IF($A$2:$A$1317="SYV-01 Ortega Park",$B$2:$B$1317),10) |
Q15:U24 | Q15 | =Q3 |
V15:V24 | V15 | =(IF(AND(V3>=55,V3<65),V3," ")) |
W15:W24 | W15 | =(IF(AND(V3>=65,V3<75),V3," ")) |
V28 | V28 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),1) |
V29 | V29 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),2) |
V30 | V30 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),3) |
V31 | V31 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),4) |
V32 | V32 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),5) |
V33 | V33 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),6) |
V34 | V34 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),7) |
V35 | V35 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),8) |
V36 | V36 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),9) |
V37 | V37 | =LARGE(IF($A$2:$A$1317="SYV-02 Dona Ave",$B$2:$B$1317),10) |
Press CTRL+SHIFT+ENTER to enter array formulas. |