excel-formula - Vlookup If Match 与数组的组合

Question

我有一个包含 3 个标准的数据表。成本中心、员工级别和工作国家/地区。这张纸看起来有点像这样

                Australia     China      Hong Kong     India
All    L10      10,000       10,001       10,003       10,004
All    L11      9,000         9,001        9,002        9,003
C9620  L10      10,005       10,006       10,007       10,008
C9620  L11      9,005        9,006        9,007         9,008

我有 50 个国家，100 多个成本中心，每个成本中心有 10 个级别。

我正在将数据移动到以列形式列出的另一种格式。工资列有我的公式，仅适用于所有国家的成本中心“全部”。我认为问题在于成本中心。

Country     Cost Center    Level    Salary
Australia      All          L10       10,000
Australia      C952         L10       #N/A
Austraila      C845         L10       #N/A
China          All          L10       10,001
China          C952         L10       #N/A
China          C845         L10       #N/A

我使用了一个数组公式来得到我的答案。- Median Salary by Cost Center'!$B$14:$B$1682 = Cost Centers - Logic Board'!$G14 = Cost Center - Logic Board'!$H14 = Employee Level - Median Salary by Cost Center'!$C$13:$AR$1698 = All Data - Logic Board'!$E14 = Country -Median Salary by Cost Center'!$C$13:$AT$13 = Country

=VLOOKUP(IF('Median Salary by Cost Center'!$B$14:$B$1682='Logic Board'!$G14,'Logic Board'!$H14,"NA"),'Median Salary by Cost Center'!$C$13:$AR$1698,MATCH('Logic Board'!$E14,'Median Salary by Cost Center'!$C$13:$AT$13,0),FALSE)

预期的结果是工资列一直向下填充。目前它仅适用于成本中心“全部”。所有其他成本中心都提出了#N/A

任何想法将不胜感激。

标签： excel-formulaexcel-2013

Answer 1

最简单的方法可能是没有公式。使用 Power Query，您只需单击几下即可转换表格。

将列标签添加到前两列，然后将表添加到 Power Query 编辑器。选择前两列，然后使用变换功能区上的Unpivot > Other columns命令。重命名、重新排列和排序数据并将其加载到工作表中。

如果您不想使用 Power Query，可以使用此公式。请参阅屏幕截图以获取单元格地址并进行相应调整。请注意，此公式不应与整列引用一起使用，否则会变得非常慢。

=INDEX($C$2:$F$5,MATCH(J2&K2,INDEX($A$2:$A$5&$B$2:$B$5,0),0),MATCH(I2,$C$1:$F$1,0))