我有一份数据表,其中包含多个列中的多个值。我有一个数量和一个当前价格,将它们相乘可得出当前收入 (CurRev)。
我想用这个查找表从新价格中获得新的收入(NewRev),但不知道如何在查找中执行多个 if。
我想要的是建立一个新列,检查“产品”、“层级”和“位置/州”,并从查找表(上方)中给我新的价格,然后将其乘以数量。
例如数据
> Product, Tier, Location, Qty, CurRev, NewRev
> Product1, Tier1, VIC, 2, $1000.00, $6000 (2 x $3000)
> Product2, Tier3, NSW, 1, $100.00, $200 (1 x $200)
> Product1, Tier3, SA, 5, $250.00, $750 (5 x $150)
> Product3, Tier1, ACT, 5, $100.00, $500(5 x $100)
> Product2, Tier3, QLD, 2, $150.00, $240 (2 x $240)
最糟糕的情况是,如果我只得到新的利率,我就可以创建另一列
答案1
此解决方案对您的数据做出了两个重大假设:1) 每种产品恰好有三层;2) 查找表中的产品 ID 位于合并单元格中。如果不是这种情况,请告诉我。
以下公式将为您提供NewRev
第 18 行记录的值。
=VLOOKUP(B18,INDIRECT("B"&MATCH(A18,$A$1:$A$10)&":J"&MATCH(A18,$A$1:$A$10)+2),MATCH(C18,$A$1:$J$1,0)-1,FALSE)*D18
此公式使用INDIRECT
函数根据产品 ID 定义要使用查找表的哪一部分。通过使用函数查找适当的位置来VLOOKUP
获得列号。VLOOKUP
MATCH
答案2
我认为如果 A 列在每一行都显示相关产品会更容易。如果这样做,您可以使用此公式
=INDEX(C$2:J$10,MATCH(1,INDEX((A$2:A$10=A18)*(B$2:B$10=B18),0),0),MATCH(C18,C$1:J$1,0))*D18
....遵循标准 INDEX/MATCH/MATCH 设置从列标题和行标题的交叉点获取数据(这里稍微复杂一些,因为必须匹配两个条件才能获取行)