Forum Discussion
Next lowest with multiple criteria
I have a fixed table (Table 1) with columns for ID, X and Y values. Each ID may have 20 X and Y values. I have a second table (Table 2) with a Column ID and a measured value. I'm trying to find the X...
Table 1
| IPN | X_VALUE | Vol (AF) |
| 1089 | 101 | 5167.63 |
| 1089 | 103 | 5465.07 |
| 1089 | 105 | 5775.73 |
| 1089 | 107 | 6099.9 |
| 1089 | 109 | 6436.99 |
| 1089 | 111 | 6786.57 |
| 1089 | 113 | 7148.94 |
| 1089 | 115 | 7524.05 |
| 1089 | 117 | 7912.33 |
| 1089 | 119 | 8313.83 |
| 1089 | 121 | 8728.72 |
| 1089 | 123 | 9157.32 |
| 1089 | 125 | 9599.76 |
| 1089 | 127 | 10056.23 |
| 1089 | 129 | 10526.88 |
| 1089 | 131 | 11012.04 |
| 1089 | 133 | 11513.25 |
| 1089 | 135 | 12030.76 |
| 1089 | 135.01 | 12033.35 |
Table 2: I need a measurement that looks at IPN 1089 and Level 127.33 and finds the next lowest number from table 1 using the IPN and the Level as the filter. It should be 127 as a result since it is less then 127.33 and has an IPN of 1089. This should be done for each of the IPN numbers.
| Line | IPN | Date | Level |
| 953 | 1089 | 5/1/2023 0:00 | 127.33 |
| 954 | 1091 | 5/1/2023 0:00 | 32.58 |
| 955 | 1093 | 5/1/2023 0:00 | 29.62 |
| 956 | 1123 | 5/1/2023 0:00 | 171.63 |
| 957 | 1125 | 5/1/2023 0:00 | 34.31 |
| 958 | 1127 | 5/1/2023 0:00 | 10.91 |
| 959 | 3947 | 5/1/2023 0:00 | 0 |
| 960 | 1138 | 5/1/2023 0:00 | 15.3 |
| 961 | 1140 | 5/1/2023 0:00 | 14.77 |
| 962 | 1142 | 5/1/2023 0:00 | 30.98 |
| 963 | 1144 | 5/1/2023 0:00 | 32.67 |
| 964 | 1146 | 5/1/2023 0:00 | 17.01 |
| 965 | 1164 | 5/1/2023 0:00 | 16.99 |
| 966 | 1166 | 5/1/2023 0:00 | 30.67 |
| 967 | 1258 | 5/1/2023 0:00 | 74.02 |
| 968 | 1276 | 5/1/2023 0:00 | 30.36 |
| 969 | 1278 | 5/1/2023 0:00 | 60.22 |