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で指定 |
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
氏名に一致する全行を返す
配布サンプルのB7:D10は氏名・生年月日・電話番号、B2は検索する氏名です。元表と重ならない空き領域、たとえばF7に入力します。
氏名と生年月日の両方で抽出する
同じ氏名の2件目だけを返す
電話番号を2件目だけ取得する説明例です。先に該当件数を確認します。
1件だけでよい場合のXLOOKUP
▶ 条件を比較する式（B4）を、上の練習ツールで試す ／ 練習ファイルの式（B4）を、上の練習ツールで試す
複数該当の抽出ができない・結果が広がらないときの確認表
| 症状 | 主な原因 | 確認と対処 |
|---|---|---|
| #SPILL! | 抽出先に既存のデータや結合セルがある | 抽出先を空き領域にする。結合セルは解除する |
| 生年月日が 33126 のような数値になる | 抽出先の列の書式が日付になっていない | 抽出先の該当列を日付形式にする（元表の書式まではコピーされない） |
| 「該当なし」になる | 氏名の表記が違う（スペース・全角と半角など） | 元表から氏名をコピーして試す。余分なスペースを取り除く |
| #NAME? になる | XLOOKUP・FILTERが使えないExcel（2019・2016）で開いている | INDEX＋SMALLの補助列方式、またはVLOOKUPと補助列を使う |
抽出先に既存データや結合セルがあると#SPILL!になります。FILTERはMicrosoft 365・Excel 2024・2021向けです。Excel 2019・2016で複数件を返す場合はINDEX＋SMALLの補助列方式を使います。
無料Excelサンプルで復習する
従来のサンプルは、氏名と生年月日の2条件で電話番号を1件検索する練習です。「同じ氏名の全件抽出」を試す場合は、同じ元表に本文のFILTER式を追加してください。
Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
コメント