VLOOKUP有多个条件? [英] VLOOKUP With Multiple Criteria?

问题描述

我一直试图创建一个VLOOKUP以基于另一个列的提交"日期是否介于两个日期周期之间返回一个值.我的目的是在某个列(该周内)中提供所有未提交数据的项目的视图

I've been trying to create a VLOOKUP to return a value based on if another column's "Submission" date falls in between date periods. My aim is to provide a view of all projects who have not submitted data in a certain column (For that week)

在一个文档中，我有一个项目提交的档案，每次有人提交项目时都会添加新的一行值(以及提交的时间/日期).(宏会复制信息并保存).我已经为一年中的每一天分配了一个星期编号，并使用vlookup'd TODAY()来获取当前的星期信息.

In one document I have an archive of projects submissions, adding a new line of values every time someone submits their project (Along with the time/date of submission). (Macro copies across information and saves). I've assigned every day of the year a week number, and vlookup'd TODAY() to allow me to pull off the current weeks information.

如果列HN列的提交时间在两个日期之间，我想返回该列，以该项目为该周的最新提交.每个项目都有一个唯一的编号.

I'm trying to return column HN if the time it has been submitted is between the date periods, pulling off the NEWEST submission for that week, for that project. Each project has a unique number assigned to it.

(一周内可能有多次提交，因此SUMif无法正常工作-将值乘以提交项目的次数.)

(There can be multiple submissions in a week, so SUMif wouldn't work - multiplying the values by the amount of times the project has been submitted.)

如果需要更多信息，请告诉我

If any more information is required, please let me know

存档数据示例:

Project OBR        Last Report Submitted:       Costs
9977               29/01/2016 11:19             1050 
9977               30/01/2016 12:30             1070

(费用保留为小计)

此刻我正在尝试的示例:

Example of what I am trying at the moment:

=SUMIFS('[TUR Master Report.xlsm]Archive'!$HN:$HN,'[TUR Master Report.xlsm]Archive'!$B:$B,"<="&$DP:$DP,'[TUR Master Report.xlsm]Archive'!$B:$B,">="&$DO:$DO,'[TUR Master Report.xlsm]Archive'!$A:$A,$DT$2)

但是，由于我使用的是sumif，如果同一周有多个提交，则费用可以重复.

However, as I am using a sumif, the cost can be duplicated if multiple submissions are on the same week.

然后我有VLOOKUP，将TODAY()与星期数进行比较，以得出相关的星期数

Then I have VLOOKUPs comparing TODAY() with the week numbers to pull the relevant week off

推荐答案

首先，获取该时期的最早日期.这将是检索相关成本的标准.

First, get the earliest date for that period. This will be needed as criteria to retrieve the associated cost.

对于下面B2中的示例图片，

For the sample image below in B2 as,

=AGGREGATE(15, 6, '[TUR Master Report.xlsm]Archive'!$B$2:$B$13/
                  (('[TUR Master Report.xlsm]Archive'!$B$2:$B$13>=DO2)*
                   ('[TUR Master Report.xlsm]Archive'!$B$2:$B$13<=DP2)*
                   ('[TUR Master Report.xlsm]Archive'!$A$2:$A$13=A2)), 1)

现在将其用作另外两列匹配项中的一项条件以获取费用.

Now use that as one criteria in another two column match to retrieve the cost.

对于下面C2中的示例图片，

For the sample image below in C2 as,

=INDEX('[TUR Master Report.xlsm]Archive'!$HM:$HM,
           AGGREGATE(15, 6, ROW('[TUR Master Report.xlsm]Archive'!$1:$13)/
                           (('[TUR Master Report.xlsm]Archive'!$B$1:$B$13=B2)*
                            ('[TUR Master Report.xlsm]Archive'!$A$1:$A$13=A2)), 1))

没什么.

这篇关于VLOOKUP有多个条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！