vba 我如何将查找值设置为 VLOOKUP 的日期范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16642000/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Might I set the Lookup Value to a date range for VLOOKUP?
提问by Ting Ping
Hi, I am trying to obtain the value of X,Y and Z by matching the tender closing date in cell G2 with vlookup.
嗨，我试图通过将单元格 G2 中的投标截止日期与 vlookup 匹配来获取 X、Y 和 Z 的值。
However, I am truly lost because it returned #n/a.
但是，我真的很迷茫，因为它返回了#n/a。
Anybody knows what could be done?
有谁知道可以做什么？
回答by Simon1979
You cannot use VLOOKUP to match columns to the left, you could however use INDEX. With t he example you put in the question enter in cell G3:
您不能使用 VLOOKUP 将列匹配到左侧，但是您可以使用 INDEX。使用您输入问题的示例，在单元格 G3 中输入：
=INDEX(A3:A10,MATCH(G2,E3:E10,0))
Then you should get the 'x' value relating to a tender that ended on the date entered in cell G2. You can then replicate changing the first range in INDEX to columns B and C for the respective y and z values.
然后，您应该获得与在单元格 G2 中输入的日期结束的投标相关的“x”值。然后，您可以复制将 INDEX 中的第一个范围更改为相应的 y 和 z 值的 B 和 C 列。
Bear in mind that INDEX is the same as VLOOKUP in that it will only return the first value, if two tenders finished on the same day it will only return the first it comes across, if you have more than one maybe SUMIF would work better, maybe along side COUNTIF to show how many there were.
请记住，INDEX 与 VLOOKUP 相同，因为它只会返回第一个值，如果两个投标在同一天完成，它只会返回它遇到的第一个，如果你有多个，也许 SUMIF 会更好，也许沿着 COUNTIF 显示有多少。
EDIT - Further Request for Multiple Columns
编辑 - 对多列的进一步请求
If you want to match the start AND end date you need to use an array formula as below, where the start date you are looking for is in cell G2 asd the end date in cell G3:
如果要匹配开始和结束日期，则需要使用如下数组公式，其中您要查找的开始日期在单元格 G2 中，结束日期在单元格 G3 中：
=INDEX(A3:A10,MATCH(1,(G2=D3:D10)*(G3=E3:E10),0))
NOTE This is an array formula so when you have typed the formula you need to press Ctrl + Shift + Enter, not just enter.
注意这是一个数组公式，因此当您输入公式时，您需要按 Ctrl + Shift + Enter，而不仅仅是 enter。
With this method it is checking two criteria for true/ false (1 and 0 respectively) so it checks each row and if columns D and E match your criteria it does 1*1 and matches the criteria of 1 that you entered. Any other combination would equal 0, eg if column D matched but E didn't then 1*0 = 0, if neither matched then 0*0 = 0.
使用此方法，它会检查真/假的两个标准（分别为 1 和 0），因此它会检查每一行，如果 D 列和 E 列符合您的条件，则它会执行 1*1 并符合您输入的 1 的条件。任何其他组合都等于 0，例如，如果 D 列匹配但 E 不匹配，则 1*0 = 0，如果两者都不匹配，则 0*0 = 0。
Using this you can then extend your lookup, changing the * to a + would return the first row where EITHER column matched the criteria:
使用它，您可以扩展您的查找，将 * 更改为 + 将返回 EITHER 列与条件匹配的第一行：
=INDEX(A3:A10,MATCH(1,(G2=D3:D10)+(G3=E3:E10),0))
You can add further columns in to the criteria using the the same method, I'm not sure what the limit is.
您可以使用相同的方法在条件中添加更多列，我不确定限制是多少。
回答by barry houdini
I understand that you aren't matching the date in G2 exactly, you simply need to get the range in which it fits. To do that and return the correct "x", "y" or "z" value you can use LOOKUP, e.g. to get "x" value
据我所知，你是不是匹配G2的日期正好，你只需要得到它适合的范围内。为此，您可以使用返回正确的“x”、“y”或“z”值LOOKUP，例如获取“x”值
=LOOKUP(G2,D3:D10,A3:A10)
=LOOKUP(G2,D3:D10,A3:A10)
That will give you 96.50 for your example data
这将为您的示例数据提供 96.50
change the A3:A10range to B3:B10or C3:C10for "y" and "z" values respectively
改变A3:A10范围B3:B10或C3:C10分别为“y”和“z”的值
