多个VLOOKUP LOOKUP IF语句？ [英] Multiple VLOOKUP LOOKUP IF Statement?

问题描述

我有下面的表格。

我需要做的是有一个VLOOKUP / LOOKUP语句，它看起来是比较下的一个月，并告诉我该月是否包含在列中'月'，如果是，那么看发送和付费。如果两列都为是，则返回是，否则返回付款列中的值。

I've got the below tables.
What I need to do is have a VLOOKUP/LOOKUP statement which looks a the month under 'Compare' and tells me if that month is included under the column 'Month,' If it is, then look at Sent and Paid. If both columns have Yes in then then return Yes, otherwise return the value in the Paid column.

然而，需要检查是否说第一个二月份在这几个月之间，所以在这个例子中，如果1月1日至1月1日之间，请使用Jan目前我正在使用VLOOKUP和LOOKUP的组合：

It does however need to check if say 1st Feb is inbetween the months, so in this example, if 1st Feb is between 1st Jan and March, use the data from Jan. At the moment I am doing this using a combination of VLOOKUP and LOOKUP:

=VLOOKUP(LOOKUP(I21,$L$4:$L$15),$L$4:$Q$15,6,FALSE)

I21是比较月份，L4-L15是月列，L4-Q15是发送完整的范围，6是发送列。目前我不包括我需要做的付费列，如上所述。

I21 is the "Compare" month, L4-L15 is the "Month" column, L4-Q15 is the full range up to Sent, and 6 is for the Sent column. At the moment I am not included the Paid column which I need to do as explained above.

  Month          Sent?          Paid?
  1st Jan        Yes            Yes
  1st Mar        Yes            No
  1st Jun        No             No
  1st Oct        N/A            N/A

 Compare
 1st Jan
 1st Feb
 1st Mar
 ...
 1st Jun

推荐答案

这个答案依赖于这些假设：

This answer relys on these assumptions:

1. 和比较值为 DateSerial 数字（不是字符串）


2. 月份数据按升序排列（如您的样本数据）


3. 您不知道哪列code>付款？是在。我假设 R

1. Month and Compare values are enterd as DateSerial numbers (not strings)
2. Month data is sorted ascending (as in your sample data)
3. You havn't said which column Paid? is in. I have assumed R

将此公式放在一个中间单元格中（让我们假设这个例子为$ code> A2 ）

Place this formula in an intermediate cell (lets assume A2 for this example)

=MATCH(I21,$L$4:$L$7,1)

这将返回小于或等于 Comp的目标值的 Month 的索引是值。所以对于 1月2日索引是 1 Jan

This will return the index of the Month that is the larget value less than or equal to the Compare value. So for 1 Feb the index is for 1 Jan

放置这个公式以获得所需的结果

Place this formula to get the required result

=IF(AND(INDEX($Q$4:$Q$7,A2)="Yes",INDEX($R$4:$R$7,A2)="Yes"),"Yes",INDEX($R$4:$R$7,A2))

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