我正在努力在 Excel 中查找两个命名表之间的匹配项。如能得到任何帮助,我将不胜感激。
场景如下:
- 我在同一工作簿的两个不同工作表中有两个命名表。一个表名为 US,另一个表名为 India。
- 美国的表格包含团队名称、职位、资源名称、位置详情、2024 年薪资信息、预计 2024 年奖金以及 2024 年公司总体成本——这些列按顺序列出。
- 印度表是美国表的副本。不同之处在于,在印度表中,在 2024 列下,我想根据职位名称从美国表中提取相应的值,并将其与内部汇率相乘以获得 INR 值
我正在尝试使用 Vlookup,但出现了一些错误,我得到了 #NA。有人能帮我理解如何做到这一点吗?
谢谢。
更新:根据建议添加表格:
美国表格:
| Team | Resource Title | Reporting Manager Title | Resource Name | Reporting Manager | Resource Location | Resource Type | Resource Start Date | Resource End Date | Ongoing Cost | 2024 Cost | 2025 Cost | 2026 Cost |
| ------------------------ | ----------------------- | ----------------------- | ------------- | ----------------- | ----------------- | ------------- | ------------------- | ----------------- | ------------ | --------- | --------- | --------- |
| Deployments - Technology | Business Analyst | Sr. Business Analyst | TBD | TBD | ABC | RTH | Sept 01, 2024 | Dec 31, 2026 | £1.00 | £0.67 | £1.00 | £0.67 |
| Deployments - Technology | Tech Delivery PM | Tech Delivery Manager | TBD | TBD | XYZ | RTH | Sept 01, 2086 | Dec 31, 2088 | £63.00 | £42.00 | £63.00 | £42.00 |
| Deployments - Technology | Test Analyst | Sr. Test Lead | TBD | TBD | MOP | FTE | Sept 01, 2087 | Dec 31, 2089 | £64.00 | £42.67 | £64.00 | £42.67 |
| Deployments - Technology | Test Analyst | Sr. Test Lead | TBD | TBD | MOP | RTH | Sept 01, 2088 | Dec 31, 2090 | £65.00 | £43.33 | £65.00 | £43.33 |
| Design & Experience | Business Analyst | Sr. Business Analyst | TBD | TBD | ABC | RTH | Sept 01, 2025 | Dec 31, 2027 | £2.00 | £1.33 | £2.00 | £1.33 |
| PMO | Head of PMO | COO | TBD | TBD | ABC | FTE | Sept 01, 2046 | Dec 31, 2048 | £23.00 | £15.33 | £23.00 | £15.33 |
| PMO | Deployment Manager - I | Head of PMO | TBD | TBD | ABC | RTH | Sept 01, 2054 | Dec 31, 2056 | £31.00 | £20.67 | £31.00 | £20.67 |
| PMO | PMO Analyst | Sr. PMO Analyst | TBD | TBD | ABC | RTH | Sept 01, 2055 | Dec 31, 2057 | £32.00 | £21.33 | £32.00 | £21.33 |
| PMO | PMO Manager | Head of PMO | TBD | TBD | ABC | RTH | Sept 01, 2056 | Dec 31, 2058 | £33.00 | £22.00 | £33.00 | £22.00 |
| PMO | PMO Manager | Deployment Manager - II | TBD | TBD | ABC | RTH | Sept 01, 2057 | Dec 31, 2059 | £34.00 | £22.67 | £34.00 | £22.67 |
| PMO | PMO Sr. Manager | Head of PMO | TBD | TBD | XYZ | RTH | Sept 01, 2058 | Dec 31, 2060 | £35.00 | £23.33 | £35.00 | £23.33 |
| PMO | Deployment Manager - II | Head of PMO | TBD | TBD | XYZ | RTH | Sept 01, 2074 | Dec 31, 2076 | £51.00 | £34.00 | £51.00 | £34.00 |
| PMO | Sr. PMO Analyst | Head of PMO | TBD | TBD | XYZ | RTH | Sept 01, 2078 | Dec 31, 2080 | £55.00 | £36.67 | £55.00 | £36.67 |
| PMO | Sr. Programme Manager | Head of PMO | TBD | TBD | XYZ | RTH | Sept 01, 2079 | Dec 31, 2081 | £56.00 | £37.33 | £56.00 | £37.33 |
对于印度表,我需要完全相同的列,但在持续成本列中,我想从美国表中提取相应的“资源标题”数据并将其转换并以印度卢比显示。
例如,在印度表中,如果我输入Resource Title
,PMO Analyst
我希望我的印度表自动将相应的单元格更新Ongoing Cost
为=(32*80)
(假设内部转换率为 80)。
我希望这就是你要找的:
仅B24为手动输入;A23为
=CHOOSEROWS(US_table,1)
;A24 是
FILTER(A3:A16,$B$3:$B$16=$B$24,"")
;C24 是
=IF(ISNUMBER(FILTER(C3:M16,$B$3:$B$16=$B$24,"")),FILTER(C3:M16,$B$3:$B$16=$B$24,"")*80,FILTER(C3:M16,$B$3:$B$16=$B$24,""))
;如果您只是想要例如 I24(持续成本)使用
FILTER(J3:J16,$B$3:$B$16=$B$24)*80
。编辑:
假设您的美国表名为 USTable:
或者仅支付持续成本:
=FILTER(USTable[Ongoing Cost],USTable[Resource Title]=$B$24,"")*80
否则,更改
USTable
为您正在使用的任何表名