excel - 查找和联接文本给出零而不是空白(Lookup and join text gives zero instead of blank)

Question

I am trying to lookup values based on two simple criterias.

(我试图根据两个简单的标准查找值。)

Here is my formula:

(这是我的公式：)

{=TEXTJOIN(". ";TRUE;IF(F1=A2:A6;IF(F2=B2:B6;C2:C6;"");""))}

However, I get 0 in the middle of the text join.

(但是，我在文本连接的中间得到0 。)

How can I ignore values in Text when it is actually empty or blank and get expected value of One. Two. Three. Five

(当Text实际为空或为空时如何忽略Text值并获得期望值One. Two. Three. Five) One. Two. Three. Five One. Two. Three. Five instead of One. Two. Three. 0. Five

(One. Two. Three. Five而不是One. Two. Three. 0. Five) One. Two. Three. 0. Five One. Two. Three. 0. Five , where cell B5 is ignored and blank.

(One. Two. Three. 0. Five ，其中单元格B5被忽略并为空白。)

  ask by Mataunited18 translate from so

Answer 1

Try using array formula as below to get the desired result.

(尝试使用以下数组公式来获得所需的结果。)

Place the TEXTJOIN inside IF .

(将TEXTJOIN放在IF 。)

=IF(F1=A2:A6, IF(F2=B2:B6, TEXTJOIN(". ", TRUE, C2:C6), ""), "")