求Excel Table3中Party列的非易失性跨表取值公式
2026-8-6
Excel Table3 Party列公式实现
根据需求，以下是满足所有条件的非易失性公式，无需数组输入、不依赖VBA：
=LET( RowNum, --SUBSTITUTE(SUBSTITUTE([@NumSelect],"p",""),"P",""), TargetTable, IF(ISNUMBER(SEARCH("p",[@NumSelect])), Table1, Table2), ShortVal, INDEX(TargetTable[ShortName], RowNum), IF(ShortVal<>"", ShortVal, INDEX(TargetTable[Name], RowNum)) )
公式逻辑说明
- RowNum：通过两次
SUBSTITUTE移除NumSelect中的大小写"p"，再用--转换为数字，得到目标表格的行号。 - TargetTable：用
SEARCH（不区分大小写）判断NumSelect是否包含"p"，确定引用Table1还是Table2。 - ShortVal：用
INDEX提取目标表格对应行的ShortName值。 - 最终判断：如果
ShortVal非空则返回它，否则返回对应行的Name值。
验证示例
Table1数据
| Name | Gender | ShortName | Occupation |
|---|---|---|---|
| Grace Turner | F | Singer | |
| Cadie Crawford | F | Tiger | Fine Artist |
| Paige Johnston | F | Archeologist | |
| Dexter Payne | M | Klondike | Veterinarian |
| Valeria Barnes | F | Chef | |
| Florrie Reed | F | Lawyer | |
| Emily Ferguson | F | Scientist | |
| Sam Hawkins | M | Alpha | Biochemist |
| Savana Ellis | F | Cook |
Table2数据
| Name | Gender | ShortName | Occupation |
|---|---|---|---|
| Vanessa Cooper | F | Producer | |
| Jasmine Morris | F | Beta | Baker |
| Evelyn Taylor | F | Economist | |
| Adelaide Roberts | F | Historian | |
| Blake Cunningham | M | Lion | Chef |
| Adelaide Harrison | F | Chemist | |
| Frederick Watson | M | Journalist |
Table3效果
| NumSelect | Party |
|---|---|
| p2 | Tiger |
| 3 | Evelyn Taylor |
| P8 | Alpha |
| 2 | Beta |
| 7 | Frederick Watson |
| p7 | Emily Ferguson |
内容的提问来源于stack exchange，提问作者Rycliff
相关产品推荐
相关产品推荐
