XLOOKUPは通常、条件に一致する最初の1行を返します。戻り範囲を複数列にしても、同じ条件の全明細を返すわけではありません。複数該当をすべて表示したい場合はFILTER、最後の1件ならXLOOKUPの検索モード-1を使います。
練習用Excelファイル（無料ダウンロード）
従来のサンプルは、氏名と生年月日の2条件で電話番号を1件検索する練習です。「同じ氏名の全件抽出」を試す場合は、同じ元表に本文のFILTER式を追加してください。Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
1件検索と全件抽出の使い分け
| 欲しい結果 | 方法 |
|---|---|
| 最初に一致する1件 | XLOOKUP |
| 最後に一致する1件 | XLOOKUPの第6引数を-1 |
| 一致する明細をすべて | FILTER |
| 全件のうち2件目 | FILTERの結果をINDEXで指定 |
ポイントXLOOKUPで商品名・分類・単価の3列を返す操作は「1行分の複数列」です。複数の明細行を返すこととは区別します。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。
氏名に一致する全行を返す
配布サンプルのB7:D10は氏名・生年月日・電話番号、B2は検索する氏名です。元表と重ならない空き領域、たとえばF7に入力します。
ポイントB2が木村洋子なら、同じ氏名を持つ2行が表示されます。生年月日列の結果が数値になった場合、抽出先の該当列を日付形式に設定します。元表のセル書式まで自動コピーする式ではありません。
氏名と生年月日の両方で抽出する
ポイント同姓同名を区別するには生年月日などの条件を追加します。同じ氏名・同じ生年月日の行が複数ある場合も、FILTERならすべて返します。実務の個人情報は閲覧権限や保存先にも配慮して管理してください。
練習用Excelファイル（無料ダウンロード）
氏名と生年月日の2条件で検索する練習ができます「after」シートで氏名と生年月日を変えて、電話番号を1件検索できます。補助列を使うVLOOKUP版や、色×サイズの表を一括検索する応用のシートも入っています。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
同じ氏名の2件目だけを返す
電話番号を2件目だけ取得する説明例です。先に該当件数を確認します。
注意これは元表の並び順で2件目です。日付が2番目に新しいという意味ではありません。また氏名に*や?を含む場合、COUNTIFのワイルドカード解釈と等号比較が異なるため、判定条件をそろえる必要があります。
1件だけでよい場合のXLOOKUP
ポイント元サンプルの&連結式と同じ目的を、条件比較で書いた式です。検索値の文字列連結による衝突を避けたい場合に使います。第6引数へ-1を追加すると最後の一致を返します。
▶ 条件を比較する式（B4）を、上の練習ツールで試す ／ 練習ファイルの式（B4）を、上の練習ツールで試す
練習用Excelファイル（無料ダウンロード）
氏名や生年月日を、表にない値に変えて、結果の出方も試せます。XLOOKUP対応のMicrosoft 365・Excel 2024・2021で開いてください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
複数該当の抽出ができない・結果が広がらないときの確認表
| 症状 | 主な原因 | 確認と対処 |
|---|---|---|
| #SPILL! | 抽出先に既存のデータや結合セルがある | 抽出先を空き領域にする。結合セルは解除する |
| 生年月日が 33126 のような数値になる | 抽出先の列の書式が日付になっていない | 抽出先の該当列を日付形式にする（元表の書式まではコピーされない） |
| 「該当なし」になる | 氏名の表記が違う（スペース・全角と半角など） | 元表から氏名をコピーして試す。余分なスペースを取り除く |
| #NAME? になる | XLOOKUP・FILTERが使えないExcel（2019・2016）で開いている | INDEX＋SMALLの補助列方式、またはVLOOKUPと補助列を使う |
抽出先に既存データや結合セルがあると#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でも利用できます。本文の対応版もご確認ください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
FILTER関数の基本的な書き方は、FILTER関数の使い方で説明しています。
コメント