Excel formulae for max or min of multiple occurrences of vlookup

https://stackoverflow.com/questions/64206037/excel-formulae-for-max-or-min-of-multiple-occurrences-of-vlookup

【原帖】

I need to do a complicated vlookup/maxif type of selection. The data I have is as below

Row Col G    Col H   Col I   colJ        col K

1    Bench  Strip   Block   BenchAbove   BenchBelow

2    1       1       4

3    1       1       5

4    1       1       6

5    1       1       7

6    1       1       8

7    8       1       4         ??           ??

8    8       1       5

9    8       1       6

10   8       1       7

11   8       1       8

12   9       1       4                       

13   9       1       5

14   9       1       6

15   9       1       7

…..this list is long (this is a sample only)

For every combination of (Strip, block) like say (1,4) There are benches like 1, 8 and 9. So bench above for 8 is 1 and bench below for 8 is 9. I need to determine the bench above and bench below for each row. There are no bench above 1 and no bench below 9.

I dont think vlookup is the solution here. Not sure if MAX(IF..) can help either. What would be the best formulae to obtain say on row 7, block combination is 1,4. The bench in question is 8. The bench above is 1 and bench below is 9. So 2 formulae will be required on Col J and Col I above.

The expected answer for the above sample data is :

Row Col G    Col H   Col I   colJ        col K

1    Bench  Strip   Block   BenchAbove   BenchBelow

2    1       1       4      -             8

3    1       1       5      -             8

4    1       1       6      -             8

5    1       1       7      -             8

6    1       1       8      -             8

7    8       1       4      1             9

8    8       1       5      1             9

9    8       1       6      1             9

10   8       1       7      1             9

11   8       1       8      1             9

12   9       1       4      8             -

13   9       1       5      8             -

14   9       1       6      8             -

15   9       1       7      8             -

【回答】

这个问题的难点是如何按相对位置取组内数据。

比如Strip=1,Block=4这组数据的Bench是(1,8,9)，则BenchAbove=相对于当前行的"上一条Bench "，即(null,1,8)，BenchBelow=现对于当前行的"下一条Bench"，即(8,9,null)。

Excel公式或VBA都难以表达“相对位置”，更没法操作组内数据，所以很难解决这个问题，勉强写出代码也不通用。假如需求改成：求相对于当前行的"上2条Bench "，或"第2至第4条Bench"，那现有的VBA 或公式就要推倒重来了，Office 365也不行。

能够方便操作Excel的脚本语言还有Python  pandas和esProc SPL，后者解决这个问题更简单了，SPL可以操作分组后的数据，且有专门语法表达相对位置，比如[-1]表示上一条，[1]或[+1]表示下一条。具体到这个问题，可以这样写：

SPL很擅长处理xls/csv上的运算，这里：http://www.raqsoft.com/p/script-over-csv-xls 有更多资源。