索引匹配与更新值按日期范围
I have two tables:
- Table with a list of sales with 3 parameters:
CustomerID, Country and date of purchase. - Table with prices and Date range for each price
I want to see the updated price for each sale based on the date range of the price.
How Can I do that?
I want to see 1100$ for the first sale and 1200$ to the 2nd sale
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
公式是：
此处的棘手部分是日期标准：您的购买日期必须从日期起更大或等于
，并且也必须低于或等于to日期。遵循该逻辑可以帮助您在确切的时间段，国家和客户的确切时间内跟踪价格。Formula is:
The tricky part here is the date criteria: your purchase date must be greater or equal to
From Dateand must be also lower or equal thanTo Date. Following that logic can help you out to track the price on a exact time period, country and client.提供您的
从日期开始的价格列以上顺序您可以使用索引/匹配：= index（$ c $ 2：$ c $ 3，匹配（D6，$ d $ 2：$ d $ 2：$ d $ 3， 1））1在Match> Match函数中告诉它“找到小于或等于Lookup_value的最大值。lookup_array必须放置在上升中命令。”Providing your
Price From Datecolumn is in ascending order you can use INDEX/MATCH:=INDEX($C$2:$C$3,MATCH(D6,$D$2:$D$3,1))The
1in theMATCHfunction tells it to "find the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order."