I have a self made workbook in excel for by work. On one sheet (Lets call it Sheet 1) I list our main suppliers, their address, how many items we have purchased and total spent.
我有一个自己制作的excel工作簿。在一张纸上(我们叫它表1)，我列出了我们的主要供应商，他们的地址，我们购买了多少商品，总共花费了多少。
Eg. Supplier 1, Address, #Items purchased, Total
如。供应商1，地址，采购项目，总计
On a seperate sheet (Sheet 2) in the same workbook I have a full order breakdown (Date, Supplier, Client, Amount, Items etc) for about 10 years worth of data (Approx 1000 rows of data).
在同一工作簿中，我有一个完整的订单分类(日期、供应商、客户、金额、项目等)，大约10年的数据(大约1000行数据)。
Eg. Order Date, Client, Sales Order Number, Supplier, Item, Cost, etc.
如。订单日期，客户，销售订单号，供应商，项目，成本等。
In sheet 1, I want it to automatically display the date of the most recent order associated with that supplier based on the info from sheet 2. So it will look something like this:
在表1中，我希望它根据表2中的信息自动显示与该供应商相关的最新订单的日期。它看起来是这样的:
Eg. Supplier 1. Address, #Items purchased, Total, (Date from recent order on sheet 2)
如。供应商1。地址、所购商品编号、总数(见表2最近订单)
I think I need to use either a LOOKUP, or INDEX, or MATCH or a combination of all three.
我认为我需要使用查找、索引、匹配或三者的组合。
All solutions I have found online deal with a range of dates, I want this to have one specific date shown associated with that specific supplier. Un fortunately I cannot provide pictures as the data is sensitive. Can anyone help?
我在网上找到的所有解决方案都涉及一系列的日期，我希望这个有一个特定的日期显示与那个特定的供应商相关。很幸运我不能提供图片，因为数据是敏感的。谁能帮忙吗?
Edit: I do not know if this will help but to calculate the cost of certain items in the workbook I have used this formula: =LOOKUP(MATCH(N2,Calcs!B$1:B$147,0),Calcs!A$1:A$147,Calcs!C$1:C$147)
编辑:我不知道这是否会有所帮助，但我已经使用了这个公式:=查找(匹配(N2,Calcs!B$1: b147,0)，Calcs!
2 个解决方案
#1
1
Based on the version of Excel you would use one of the following:
根据Excel的版本，你可以使用以下其中一个:
2007 or earlier this Array Formula:
2007年或之前的数组公式:
=MAX(IF('Sheet2'!$D$1:$D$100=A2,'Sheet2'!$A$1:$A$100))
It is an Array so Ctrl-Shift-Enter when leaving edit mode.
它是一个数组，在离开编辑模式时按Ctrl-Shift-Enter键。
2010 or later:
2010年或以后:
=Aggregate(14,6,'Sheet2'!$A$1:$A$100/('Sheet2'!$D$1:$D$100=A2),1)
If you have the most current Excel with office 365 or are using the online version then:
如果你有最新的office 365或正在使用在线版本的Excel，那么:
=MAXIFS('Sheet2'!$A$1:$A$100,'Sheet2'!$D$1:$D$100,A2)
#2
0
Have you tried the following array formula?
你试过以下数组公式吗?
=MAX(IF($D$2:$D$7=$A2,$E$2:$E$7,0))
Note you can use Evaluate formula to see how it works, and you need to press ctrl+shift+enter after entering the formula to get the curly brackets at each end that change behaviour to an array forula.
注意，您可以使用Evaluate公式来查看它是如何工作的，您需要在输入公式之后按ctrl+shift+enter键，以获得在每个端点上改变数组forula行为的花括号。
Regards,
问候,
James
詹姆斯
#1
1
Based on the version of Excel you would use one of the following:
根据Excel的版本，你可以使用以下其中一个:
2007 or earlier this Array Formula:
2007年或之前的数组公式:
=MAX(IF('Sheet2'!$D$1:$D$100=A2,'Sheet2'!$A$1:$A$100))
It is an Array so Ctrl-Shift-Enter when leaving edit mode.
它是一个数组，在离开编辑模式时按Ctrl-Shift-Enter键。
2010 or later:
2010年或以后:
=Aggregate(14,6,'Sheet2'!$A$1:$A$100/('Sheet2'!$D$1:$D$100=A2),1)
If you have the most current Excel with office 365 or are using the online version then:
如果你有最新的office 365或正在使用在线版本的Excel，那么:
=MAXIFS('Sheet2'!$A$1:$A$100,'Sheet2'!$D$1:$D$100,A2)
#2
0
Have you tried the following array formula?
你试过以下数组公式吗?
=MAX(IF($D$2:$D$7=$A2,$E$2:$E$7,0))
Note you can use Evaluate formula to see how it works, and you need to press ctrl+shift+enter after entering the formula to get the curly brackets at each end that change behaviour to an array forula.
注意，您可以使用Evaluate公式来查看它是如何工作的，您需要在输入公式之后按ctrl+shift+enter键，以获得在每个端点上改变数组forula行为的花括号。
Regards,
问候,
James
詹姆斯