ExcelのXLOOKUP関数は、特定の値に対応するデータを検索して返す強力なツールですが、複数の一致する結果を返すには工夫が必要です。特に、同じ検索条件で複数の行を返したい場合、XLOOKUP単体では対応できません。そこで、今回は初心者の方にもわかりやすく、XLOOKUPを使って複数行を返す方法をご紹介します。
目次
Excelのイメージ
XLOOKUP関数の基本をおさらい
XLOOKUP関数とは？
XLOOKUP関数は、指定した検索値に対応するデータを、指定した範囲から検索して返す関数です。例えば、社員番号を検索してその社員の名前を取得する場合などに使用します。
基本的な書式
XLOOKUP関数の基本的な書式は以下の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, , , )
例えば、社員番号がセルにあり、社員番号の一覧がB2\:B10、対応する社員名がC2\:C10にある場合、社員名を取得するには以下のように記述します。
=XLOOKUP(, B2:B10, C2:C10)
XLOOKUPで複数行を返す方法
FILTER関数を使う方法
XLOOKUP関数は、検索値に対応する最初の一致するデータしか返しません。しかし、FILTER関数を組み合わせることで、検索値に対応するすべてのデータを取得することができます。
例えば、社員番号がセルにあり、社員番号の一覧がB2\:B10、対応する社員名がC2\:C10にある場合、社員番号に対応するすべての社員名を取得するには以下のように記述します。
=FILTER(C2:C10, B2:B10=, "該当なし")
この式では、B2\:B10の範囲でセルの値と一致するすべての行に対応するC2\:C10の値を返します。一致するデータがない場合は「該当なし」と表示されます。
TEXTJOIN関数を使って1つのセルにまとめる方法
FILTER関数で取得した複数の結果を1つのセルにまとめて表示したい場合、TEXTJOIN関数を使用します。
例えば、上記のFILTER関数をTEXTJOIN関数で囲むことで、複数の社員名をカンマ区切りで1つのセルに表示することができます。
=TEXTJOIN(", ", TRUE, FILTER(C2:C10, B2:B10=, "該当なし"))
この式では、B2\:B10の範囲でセルの値と一致するすべての行に対応するC2\:C10の値をカンマ区切りで1つのセルに表示します。
XLOOKUPで複数行を返す際の注意点
複数行を返すにはFILTER関数が必要
XLOOKUP関数単体では、検索値に対応する最初の一致するデータしか返すことができません。複数の一致するデータを取得するには、FILTER関数を組み合わせる必要があります。
結果が複数行になる場合、出力範囲に十分な空きセルが必要
FILTER関数を使用して複数の結果を返す場合、出力範囲に十分な空きセルが必要です。空きセルがない場合、#SPILL!エラーが表示されます。
複数の条件で検索する場合は、AND関数やOR関数を使用
複数の条件で検索する場合、FILTER関数の条件部分でAND関数やOR関数を使用します。
例えば、社員番号がセル、部署がB2セルにあり、社員番号の一覧がC2\:C10、部署の一覧がD2\:D10、対応する社員名がE2\:E10にある場合、社員番号と部署が一致するすべての社員名を取得するには以下のように記述します。
=FILTER(E2:E10, (C2:C10=)*(D2:D10=B2), "該当なし")
この式では、C2\:C10の範囲でセルの値と一致し、かつD2\:D10の範囲でB2セルの値と一致するすべての行に対応するE2\:E10の値を返します。
よくある質問や疑問
Q1: XLOOKUP関数は複数の条件で検索できますか？
はい、できます。XLOOKUP関数では、検索値として複数の条件を指定することができます。例えば、社員番号と部署名の組み合わせで検索する場合、以下のように記述します。
=XLOOKUP(1, (C2:C10=)*(D2:D10=B2), E2:E10)
この式では、C2\:C10の範囲でセルの値と一致し、かつD2\:D10の範囲でB2セルの値と一致する最初の行に対応するE2\:E10の値を返します。
Q2: FILTER関数で取得した結果を並べ替えることはできますか？
はい、できます。FILTER関数で取得した結果を並べ替えるには、SORT関数を組み合わせます。
例えば、上記のFILTER関数で取得した結果を昇順に並べ替えるには以下のように記述します。
=SORT(FILTER(C2:C10, B2:B10=, "該当なし"))
この式では、B2\:B10の範囲でセルの値と一致するすべての行に対応するC2\:C10の値を昇順に並べ替えて返します。
Q3: 複数の条件で検索した結果を1つのセルにまとめることはできますか？
はい、できます。複数の条件で検索した結果を1つのセルにまとめるには、FILTER関数とTEXTJOIN関数を組み合わせます。
例えば、上記の複数の条件で検索した結果をカンマ区切りで1つのセルにまとめるには以下のように記述します。
=TEXTJOIN(", ", TRUE, FILTER(E2:E10, (C2:C10=)*(D2:D10=B2), "該当なし"))
この式では、C2\:C10の範囲でセルの値と一致し、かつD2\:D10の範囲でB2セルの値と一致するすべての行に対応するE2\:E10の値をカンマ区切りで1つのセルにまとめて返します。
今すぐ解決したい！どうしたらいい？
いま、あなたを悩ませているITの問題を解決します！
「エラーメッセージ、フリーズ、接続不良…もうイライラしない！」
あなたはこんな経験はありませんか？
✅ ExcelやWordの使い方がわからない💦
✅ 仕事の締め切り直前にパソコンがフリーズ💦
✅ 家族との大切な写真が突然見られなくなった💦
✅ オンライン会議に参加できずに焦った💦
✅ スマホの重くて重要な連絡ができなかった💦
平均的な人は、こうしたパソコンやスマホ関連の問題で年間73時間（約9日分の働く時間！）を無駄にしています。あなたの大切な時間が今この悩んでいる瞬間も失われています。
LINEでメッセージを送れば即時解決！
すでに多くの方が私の公式LINEからお悩みを解決しています。
最新のAIを使った自動応答機能を活用していますので、24時間いつでも即返信いたします。
誰でも無料で使えますので、安心して使えます。
問題は先のばしにするほど深刻化します。
小さなエラーがデータ消失や重大なシステム障害につながることも。解決できずに大切な機会を逃すリスクは、あなたが思う以上に高いのです。
あなたが今困っていて、すぐにでも解決したいのであれば下のボタンをクリックして、LINEからあなたのお困りごとを送って下さい。
ぜひ、あなたの悩みを私に解決させてください。
まとめ
XLOOKUP関数は、特定の値に対応するデータを検索して返す強力なツールですが、複数の一致する結果を返すにはFILTER関数を組み合わせる必要があります。FILTER関数を使用することで、検索値に対応するすべてのデータを取得することができます。また、TEXTJOIN関数を使用することで、複数の結果を1つのセルにまとめて表示することも可能です。
Excelの関数は非常に多機能で、組み合わせることでさまざまな処理が可能です。ぜひ、今回ご紹介した方法を活用して、より効率的なデータ処理を実現してください。
他にも疑問やお悩み事があればお気軽にLINEからお声掛けください。
コメント