时间戳

首页 > 解决方案 > Excel：在列中查找值，然后在该行中搜索日期值，将值返回到左侧

excel - Excel：在列中查找值，然后在该行中搜索日期值，将值返回到左侧

问题描述

我正在寻找一个公式（或代码），它在列中找到一个值，然后在日期之间的该行中找到该值，当多个日期返回最大值时，然后将值返回到左侧。我不能使用辅助列。我尝试了几个嵌套索引/匹配公式，但没有成功......

对于以下示例：

参数 1：在 A 列中查找Fork

参数 2：在该行 (4) 中，查找介于 2018 年 1 月 1 日和 18 年 1 月 5 日之间的日期

参数3：当在该日期范围内找到多个日期时，返回最大值

参数 4：返回该日期左侧列的值

结果：D 列中为黑色。

    A       B       C           D       E           F             G
1   ID      Colour 1Date 1      Colour 2Date 2      Colour 3      Date 3
2   Plate   Green   1-Jan-18    Red     23-Jan-18   L blue        14-Feb-18
3   Bowl    Blue    6-Jan-18    Brown   28-Jan-18   Yellow/Green  19-Feb-18
4   Fork    Yellow  2-Jan-18    Black   4-Jan-18    Turquoise     24-Feb-18
5   Knive   Purple  16-Jan-18   White   7-Feb-18    Maroon        1-Mar-18
6   Spoon   Pink    21-Jan-18   Orange  12-Feb-18   L pink        6-Mar-18

我希望这是可能的！非常感谢提前。

标签： excelexcel-formulaexcel-2010

解决方案

试试这个

其中
I10= 要搜索的 ID
I11= 最小日期（2018 年 1 月 1 日）
I12= 最大日期（2018 年 1 月 5 日）

J10==MATCH($I$10,$A$1:$A$6,0)如果是 ID，则获取行号

=INDEX(INDEX($A$1:$G$6,$J$10,),
       MATCH(
             MAXIFS(INDEX($A$1:$G$6,$J$10,),
                    INDEX($A$1:$G$6,$J$10,),">="&$I$11,
                    INDEX($A$1:$G$6,$J$10,),"<="&$I$12
                   ),
             INDEX($A$1:$G$6,$J$10,),0
             ) - 1
      )


INDEX($A$1:$G$6,$J$10,)获取 ID 的数据行（在公式中重复多次）获取满足条件的
MAXIFS(...)最大日期获取返回颜色的日期的列号>=<=
MATCH(MAXIF(...), [ID data row],0)MAXIF
INDEX( [ID data row], MATCH(MAXIF(...), [ID data row],0) - 1)

没有替代公式MAXIFS

=INDEX(INDEX($A$1:$G$6,$J$10,),
            MATCH(
                  MAX(IFERROR(({0,0,1,0,1,0,1})*(INDEX($A$1:$G$6,$J$10,)>=$I$11)*(INDEX($A$1:$G$6,$J$10,)<=$I$12)*(INDEX($A$1:$G$6,$J$10,)),0)),
            INDEX($A$1:$G$6,$J$10,),0
            ) - 1
      )

以数组公式的形式输入（包含CtrlShiftEnter而不是仅包含Enter）

推荐阅读