Vlookup一个值，然后比较日期

I am wanting to look up a value from a table, than take a date and compare it to the row and find which date corresponds to the date I enter.

我想要从表中查找值,而不是将日期与行进行比较并找出与我输入的日期相对应的日期。

| Column1 | Effective 1 | Effective 2 | Effective 3 |
|---------|-------------|-------------|-------------|
| abc     | 1/1/2016    | 1/1/2017    | 1/1/2018    |
| cba     | 1/2/2016    | 1/2/2017    | 1/2/2018    |

I want to look up colum1 and find for example cba; then want to link a date to which effective date, which is always the date less than. So, if the date I am looking up is for cba and it is 6/7/2017 it would correspond to effective 2 which is 1/2/17.

我想查找colum1并找到例如cba;然后想要将生效日期的日期链接起来,该日期始终是小于的日期。所以,如果我正在查找的日期是cba,它是6/7/2017,它将对应于有效2,即1/2/17。

I have tried index match match and also vlookups with embedded if statements and neither of them work.

我已尝试过索引匹配匹配以及带有嵌入式if语句的vlookups,但它们都不起作用。

1 个解决方案

#1

Using structured references (in a Table), the following formula will do what you want.

使用结构化引用(在表中),以下公式将执行您想要的操作。

=LOOKUP(2,1/((INDEX(myTable,MATCH(F2,myTable[Column1],0),0))< TODAY()),INDEX(myTable,MATCH(F2,myTable[Column1],0),0))

The formula uses documented but somewhat obscure features of the INDEX and LOOKUP formulas. See MSDN Help for more details:

该公式使用了INDEX和LOOKUP公式的文档但有些模糊的特征。有关详细信息,请参阅MSDN帮助:

MATCH ► find the row that matches the search term in F2 to use as row argument in INDEX

匹配►在F2中找到与搜索词匹配的行,以用作INDEX中的行参数

INDEX with a column argument of 0 ► return the entire row

列参数为0的INDEX返回整行

< TODAY() to do the less than comparison. You can use a cell reference instead of you need to.

()做的比较少。您可以使用单元格引用而不是您需要的。

1/(... will return 1 or DIV/0 errors depending on the comparison

1 /(...将返回1或DIV / 0错误,具体取决于比较

LOOKUP where the lookup_value is greater than anything in the array will match the last numeric value in lookup_array and return the value in the same position in results_array

LOOKUP其中lookup_value大于数组中的任何内容将匹配lookup_array中的最后一个数值,并返回results_array中相同位置的值

#1