这项工作是否可行:

=TEXTJOIN(" | ",TRUE,IF(A2:A17=G9,IF(C2:C17=H9,IF(D2:D17=TRANSPOSE(G2:G5),E2:E17,""))))

通过 Ctrl Shift Enter

很遗憾，我没有textjoin，也无法自我测试.

Unfortunately I don't have textjoin and am unable to test myself.

为了详细说明，我相信您需要 TRANSPOSE()函数.现在，您正在将垂直范围与垂直范围进行比较.由于要比较的数组大小不同，这可能会导致问题，即 N/A#错误.我自己对此行为感到困惑，并在此处上对此问题提出了疑问.给出的答案对于阐述此Excel行为非常有用.

To elaborate, I beleive you are needing the TRANSPOSE() function. Right now you are comparing a vertical range against a vertical range. This can cause problems, the N/A# error, due to the different size of the arrays you are comparing. I myself was confused by this behaviour and asked a question on this matter on here. The answer given was very usefull to elaborate on this Excel behaviour.

希望这可以解决您的问题:)

Hopefully this solves your issue :)

编辑

我希望我可以尝试使用 TEXTJOIN()来帮助您，但是要获得一个临时补丁，直到有人可以进一步帮助您，您可以尝试实现一些 SUBSTITUTE()函数可捕获 FALSE 值，如下所示:

I wish I could play around with TEXTJOIN() to help you out, but to have a temporary patch until someone actually can help you further, you could try implement some SUBSTITUTE() functions to catch the FALSE values like so:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" | ",TRUE,IF(A2:A17=G9,IF(C2:C17=H9,IF(D2:D17=TRANSPOSE(G2:G5),E2:E17,""))))," | FALSE ",""),"| FALSE",""),"FALSE | ","")

输入为数组