符合IF条件的VLOOKUP [英] VLOOKUP with IF conditions are met
问题描述
表1:(报告)
SSN |服务日期
123456 | 10/01/2014
表2:(数据)
SSN |病例数|开始日期|结束日期
123456 | 0000000 | 01/01/2010 | 12/31/2012
123456 | 1111111 | 01/01/2013 | 05/31/2014
123456 | 2222222 | 06/01/2014 | 11/10/2015
如何根据服务日期进行VLOOKUP,范围的另一张纸的开始和结束日期?
在这种情况下,我想查找 SSN
并返回案例编号 2222222
因为这样的服务日期是活动的。
我在网上找到MATCH。我可以匹配案例的第一个结果与 SSN
匹配,但是如果不匹配,怎么去下一个案例?
= IF(E2> = INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),4 )& E2< = INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),5),YES,NO)
我正在使用Windows 7上的Excel 2013。
如果 SSN
在两张表格的A1中,您的案例号
是数字(除了 0000000
),那么你可以尝试:
= SUMIFS B:B,Sheet2!A:A,A2,Sheet2!C:C,< =& B2,Sheet2!D:D,> =& B2)
SUMIFS已解释这里(和其他地方)。
I have this problem at work to populate the worksheet with the right case number.
Sheet 1: (Report)
SSN | Service Date
123456 | 10/01/2014
Sheet 2: (Data)
SSN | Case Number | Start Date | End Date
123456 | 0000000 | 01/01/2010 | 12/31/2012
123456 | 1111111 | 01/01/2013 | 05/31/2014
123456 | 2222222 | 06/01/2014 | 11/10/2015
How can I do a VLOOKUP based on the Service Date to be within the "range" of the Start and End Date of another sheet?
In this case I would like to lookup the SSN
and return case number 2222222
because that is the case active for such date of service.
I was looking online and found "MATCH". I am able to match the first result of the case matches the SSN
, but how to go to the next case if it does not match?
=IF(E2>=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),4)&E2<=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),5),"YES","NO")
I am using Excel 2013 on Windows 7 at work.
If SSN
is in A1 of both sheets and your Case Number
s are numeric (other than 0000000
) then you might try:
=SUMIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!C:C,"<="&B2,Sheet2!D:D,">="&B2)
SUMIFS is explained here (and elsewhere!).
这篇关于符合IF条件的VLOOKUP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!