基于多个列和多个IF条件的TEXTJOIN [英] TEXTJOIN based on multiple columns and multiple IF conditions

问题描述

我正在尝试基于多个列进行TEXTJOIN，在这种情况下，列B和D在选定范围内(如果列A = G9并且列C = H9)，而忽略列D中不在G2范围内的项目:G5(如果D列= G2:G5范围内的项目).我尝试的一切似乎都导致NA#

I am trying to do a TEXTJOIN based on multiple columns, in this case Column B and D within a selected range (If column A = G9 and column C = H9) while ignoring items in Column D that are not in range G2:G5 (if Column D = an item in range G2:G5). Everything I try seems to result in NA#

推荐答案

这项工作是否可行:

=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 | ","")

输入为数组

这篇关于基于多个列和多个IF条件的TEXTJOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！