Excel 2010 中的二维查找
我有一个看起来类似于此
表的数据包含多个行标签标题（堆叠行标签）：
|20% |30% |
|25/01/11 |buy |1 |1.1 |
| |sell |0.8 |0.9 |
|27/01/11 |buy |1.02 |1.03 |
| |sell |1.1 |1.2 |
|01/02/11 |buy |1.05 |1.07 |
我正在尝试使用查找公式根据交易的日期和类型检索数据：说出 30 的乘数是多少2011 年 1 月 27 日卖出出价的百分比 任何帮助我将不胜感激！
UPD
必须有一个解决方案来解决此问题，而无需添加要查找的唯一列...Excel 2000 有一种方法，通过使用自然语言公式来实现此目的，正如您可以从本文的方法 2 中的示例 2 中看到的那样 - support.microsoft.com/kb/275170
UPD 2
有可能吗？日期不仅会有间隙，而且还会跳过一两天...就像示例
UPD 3
到目前为止发布的解决方案存在问题：
如果查找日期是 26/01/2011，公式将返回 N/A（在完美的解决方案中，它应该返回最接近的匹配项（向下舍入到之前的日期） - 即如果查找日期在 A 列中不可用，则应返回之前最接近的日期）
堆叠行标题意味着我将行排列成这样的组（日期和类型是行标题） 
I have a data that looks similar to this
Tables contains more than one row label heading (stacked row label):
|20% |30% |
|25/01/11 |buy |1 |1.1 |
| |sell |0.8 |0.9 |
|27/01/11 |buy |1.02 |1.03 |
| |sell |1.1 |1.2 |
|01/02/11 |buy |1.05 |1.07 |
I am trying to use a lookup formula to retrieve data based on the date and type of transaction: say what was the multiplier on 30% of sell bid on 27/01/11
Any help will me much appreciated!
UPD
there has to be a solution to this without adding a unique column against which to lookup... Excel 2000 had a way of doing it by using natural language formulas as you can see from this article in method 2, example 2 - support.microsoft.com/kb/275170
UPD 2
Is it possible at all? the dates will not only have gaps, but will also be skipping one or two days... just like in the example
UPD 3
There are problems with the solutions posted so far:
if the lookup date is 26/01/2011 the formula will return N/A (in the perfect solution it should return the closest match (rounded down to previous date) - i.e. if the lookup date is not available in the column A then the previous closest date should be returned)
stacked row header means that i have rows arranged into groups like this (date and type being the row headers)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果
F1包含所需日期，F2%、F3“买入”或“卖出”会考虑所有三个因素
编辑
根据您的编辑，要获得您想要的内容，您只需将精确匹配参数从
0更改为1。来自 Excel 帮助文件以便公式变为
此外，如果您想对 % 值执行相同的操作，请对其他
MATCH执行相同的操作If
F1contains the requierd date,F2the %,F3"buy" or "sell"takes all three factors into account
EDIT
based on your edit, to get what you want you only need to change the exact match parameter from
0to1. From Excel help fileso formula becomes
Also, if you want to do the same with the % values, do the same to the other
MATCHtoo假设您的数据从 A 列开始，并且您的查找日期位于单元格 F1 中，请尝试以下
操作：通过查找与日期匹配的行 (
MATCH)，将其偏移 1 以获得卖出行，并使用 INDEX 函数从 D 列中检索值。Assuming your data starts in Column A and your lookup date is in cell F1, try this:
This works by finding the row that matches the date (
MATCH), offsets it by 1 to get the sell row, and uses theINDEXfunction to retrieve the value from Column D.假设您有从 A1 开始的值。日期 25-01-2011 将在 A2，27/01 将在 A4 等。20%，将在 C1 和 30%，将在 D1 将
您想要的查找日期放入 F1，查找百分比 u想要在 G1（20 或 30%）中查找，在 H1 中查找您想要的类型（买入或卖出）
在 I1 中输入以下公式。你有结果了。
Consider you have the values starting from A1. The date 25-01-2011 will be at A2, 27/01 will be at A4 etc.20%, will be at C1 and 30%, will be at D1
Put the look up date u want in F1, look up percentage u want in G1(20 or 30%), look up type u want in H1(buy or sell)
Enter the below formuka in I1. You have the result.
在这些情况下，我发现在数据左侧包含一个“关键”列很有帮助，这可以大大简化公式。在数据左侧插入一列，并将其作为日期和买入/卖出的组合（A2 = B2 & "_" & C2）：
然后，只需执行 vlookup + match：
这种方法的优点是它使公式简单易读
In these situations, I find it helpful to include a "key" column on the left of the data, which simplifies the formulas a great bit. Insert a column to the left of your data and have it be the combination of date and buy/sell (A2 = B2 & "_" & C2):
Then, just do a vlookup + match:
The advantage of this approach is that it keeps the formulas simple and easy to read