Excel:匹配列,并在给定日期后找到下一个值 [英] Excel: match column and find next value after a given date
问题描述
我有两个表:
表1 - 包含列'。
Cust_ID和注册日期
表2包含
Cust_ID和Invoice_date
我需要匹配cust_ID,并在注册日期后从发票日期列返回下一个日期。
我使用以下公式,但对我来说没有效果。
= INDEX($ G $ 2:$ G $ 13,MATCH(A4, $ F $ 2:$ F $ 13,-1))
请帮助。
如果您不想排序数据,那么您将需要一个像这样的数组公式:$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ($ G $ 2:$ G $ 13> = B2)*($ F $ 2:$ F $ 13 = A2),$ G $ 2:$ G $ 13-B2) :$ F $ 1 3 = A2),$ G $ 2:$ G $ 13-B2),0))
数组公式在编辑编辑模式时必须使用Ctrl-Shift-Enter而不是Enter进行确认。如果正确完成,Excel将围绕公式放入 {}
。
点击C2。将公式放在公式栏中。点击Ctrl-Shift-Enter。然后复制/下拉。
< img src =https://i.stack.imgur.com/CvpEy.pngalt =在此输入图像说明>
I am struggling with a formula and need help.
I have two tables: Table 1 - containing columns'.
"Cust_ID" and "Enrolled date"
Table 2 -containing
"Cust_ID" and "Invoice_date"
i need to match cust_ID and return the next date from invoice date column after enrolled date.
I used following formula but it didn't really worked for me.
=INDEX($G$2:$G$13,MATCH(A4,$F$2:$F$13,-1))
Please help.
If you do not want to sort your data then you will need an Array formula like this:
=INDEX($G$2:$G$13,MATCH(MIN(IF(($G$2:$G$13>=B2)*($F$2:$F$13=A2),$G$2:$G$13-B2)),IF(($G$2:$G$13>=B2)*($F$2:$F$13=A2),$G$2:$G$13-B2),0))
Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when editing edit mode. If done correctly then Excel will put {}
around the formula.
Click on C2. Put the formula in the formula bar. Click Ctrl-Shift-Enter. Then copy/drag down.
这篇关于Excel:匹配列,并在给定日期后找到下一个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!