なにがしたいか
Excelの関数を使って条件に一致する複数セルを一気にもってきたい。
ないようでたまにあるケースです。
例えばこういう表があったときに「NGのIDの一覧が欲しい！」みたいなケースです。
| ID | Status |
|---|---|
| 1 | NG |
| 2 | OK |
| 3 | NG |
| 4 | NG |
| 5 | OK |
こういう文字列が取得できるとゴールです。
1, 3, 4
答え
一覧を出したいセルに次の関数を入力します。
=TEXTJOIN(", ",TRUE,IF(B1:B5="NG",A1:A5,""))
そして「Ctrl + Shift + Enter」で決定します。
無事に 1, 3, 4 と出たと思います。
めでたしめでたし
解説
２つの関数（というか仕組み）を使っています。
1つ目は「TEXTJOIN」、2つ目は「配列数式」です。
TEXTJOIN()
それほど難しい関数ではありません。
複数の文字列を渡すとjoin(結合)してくれるだけです。
=TEXTJOIN("/",TRUE,"1","2","3")
とすれば、
1/2/3
となります。
文字列は可変個でいくらでも指定できます。そして実はここには「配列」を指定することもできます（後述）。
配列数式
こっちが少しやっかいですので簡単にだけ。
データをいわゆる配列として扱うことができます。
今回の例で説明すると。
| ID | Status |
|---|---|
| 1 | NG |
| 2 | OK |
| 3 | NG |
| 4 | NG |
| 5 | OK |
という表で以下の関数を入力して
=IF(B1:B5="NG",A1:A5,"")
「Ctrl+Shift+Enter」で決定したあとにセルの数式を見ると
{=IF(B1:B5="NG",A1:A5,"")}
みたいに {} で囲まれたように表示されると思います。
これが配列数式になってるよという目印です。
そしてIF文の意味を見てみると、もし Status（B1～B5） にNGがあったら ID（A1～A5） の値を返してくださいもしNGじゃなかったら "" にしてください、という意味です。 B1:B5 と A1:A5 の数（行の数）が同じなので対応する値を返すことができます。 VLOOKUPとかと同じ感じです。
さらに今回はここで「配列数式」というものを使っているので（Ctrl+Shift+Enterで決定しているので）、条件に一致したデータが複数あれば複数個を配列にしてそのまま扱うことができます。
なんとさらにTEXTJOINは配列を引数として受け取って結合することが出来るのです。
つまり、
IF(B1:B5="NG",A1:A5,"") で StatusがNG の ID一覧 を配列にする
↓
TEXTJOIN(", ",TRUE, ＊ID一覧の配列＊ ) で ID一覧の配列 を "," で結合している
ということで、
=TEXTJOIN(", ",TRUE,IF(B1:B5="NG",A1:A5,""))
これで条件にあった複数セルを（配列と言うかたちにして文字列を結合して）一気に持ってこれました。