XLOOKUPは通常、条件に一致する最初の1行を返します。戻り範囲を複数列にしても、同じ条件の全明細を返すわけではありません。複数該当をすべて表示したい場合はFILTER、最後の1件ならXLOOKUPの検索モード-1を使います。
無料Excelサンプルで一緒に試す
従来のサンプルは、氏名と生年月日の2条件で電話番号を1件検索する練習です。「同じ氏名の全件抽出」を試す場合は、同じ元表に本文のFILTER式を追加してください。
Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
1件検索と全件抽出の使い分け
| 欲しい結果 | 方法 |
|---|---|
| 最初に一致する1件 | XLOOKUP |
| 最後に一致する1件 | XLOOKUPの第6引数を-1 |
| 一致する明細をすべて | FILTER |
| 全件のうち2件目 | FILTERの結果をINDEXで指定 |
XLOOKUPで商品名・分類・単価の3列を返す操作は「1行分の複数列」です。複数の明細行を返すこととは区別します。
氏名に一致する全行を返す
配布サンプルのB7:D10は氏名・生年月日・電話番号、B2は検索する氏名です。元表と重ならない空き領域、たとえばF7に入力します。
=FILTER(B7:D10,B7:B10=B2,"該当なし")B2が木村洋子なら、同じ氏名を持つ2行が表示されます。生年月日列の結果が数値になった場合、抽出先の該当列を日付形式に設定します。元表のセル書式まで自動コピーする式ではありません。
氏名と生年月日の両方で抽出する
=FILTER(B7:D10,(B7:B10=B2)*(C7:C10=B3),"該当なし")同姓同名を区別するには生年月日などの条件を追加します。同じ氏名・同じ生年月日の行が複数ある場合も、FILTERならすべて返します。実務の個人情報は閲覧権限や保存先にも配慮して管理してください。
同じ氏名の2件目だけを返す
電話番号を2件目だけ取得する説明例です。先に該当件数を確認します。
=IF(COUNTIF(B7:B10,B2)<2,"2件目なし",INDEX(FILTER(D7:D10,B7:B10=B2),2))これは元表の並び順で2件目です。日付が2番目に新しいという意味ではありません。また氏名に*や?を含む場合、COUNTIFのワイルドカード解釈と等号比較が異なるため、判定条件をそろえる必要があります。
1件だけでよい場合のXLOOKUP
=XLOOKUP(1,(B7:B10=B2)*(C7:C10=B3),D7:D10,"該当なし",0)元サンプルの&連結式と同じ目的を、条件比較で書いた式です。検索値の文字列連結による衝突を避けたい場合に使います。第6引数へ-1を追加すると最後の一致を返します。
結果が広がらない場合と旧Excel
抽出先に既存データや結合セルがあると#SPILL!になります。FILTERはMicrosoft 365・Excel 2024・2021向けです。Excel 2019・2016で複数件を返す場合はINDEX＋SMALLの補助列方式を使います。
元表を並べ替えると「2件目」の内容も変わります。順番に業務上の意味があるなら、日付・番号などの並び替え基準を決めてください。
無料Excelサンプルで復習する
従来のサンプルは、氏名と生年月日の2条件で電話番号を1件検索する練習です。「同じ氏名の全件抽出」を試す場合は、同じ元表に本文のFILTER式を追加してください。
Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
コメント