XLOOKUPは通常、条件に一致する最初の1行を返します。戻り範囲を複数列にしても、同じ条件の全明細を返すわけではありません。複数該当をすべて表示したい場合はFILTER、最後の1件ならXLOOKUPの検索モード-1を使います。
1件検索と全件抽出の使い分け
| 欲しい結果 | 方法 |
|---|---|
| 最初に一致する1件 | XLOOKUP |
| 最後に一致する1件 | XLOOKUPの第6引数を-1 |
| 一致する明細をすべて | FILTER |
| 全件のうち2件目 | FILTERの結果をINDEXで指定 |
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。
氏名に一致する全行を返す
配布サンプルのB7:D10は氏名・生年月日・電話番号、B2は検索する氏名です。元表と重ならない空き領域、たとえばF7に入力します。
氏名と生年月日の両方で抽出する
同じ氏名の2件目だけを返す
電話番号を2件目だけ取得する説明例です。先に該当件数を確認します。
1件だけでよい場合のXLOOKUP
▶ 条件を比較する式（B4）を、上の練習ツールで試す ／ 練習ファイルの式（B4）を、上の練習ツールで試す
下の図は、練習ファイルの式（氏名と生年月日を & でつなぐ書き方）の解説です。上の「条件を比較する式」とは書き方が違いますが、結果は同じ 080-****-0001 です。
【実機で検証】同じ名前が 3 件あるとき、何が返るか
「山田」が 3 件ある表で、XLOOKUP と FILTER の結果を Excel の実機で比べました。
| ためしたこと | 結果 |
|---|---|
XLOOKUP("山田",A2:A6,C2:C6) | 1200（上から 1 件目だけ） |
| 検索モードを -1 にした XLOOKUP | 900（下から＝最後の 1 件） |
FILTER(A2:C6,A2:A6="山田") | 3 行すべて |
ROWS(FILTER(…))・SUM(FILTER(…)) | 3 件・合計 3600 |
| いない「田中」を FILTER | #CALC!（第 3 引数に “なし” と書くと「なし」） |
TEXTJOIN("、",TRUE,FILTER(…)) | 1200、1500、900（1 つのセル） |
XLOOKUP は何件あっても 1 件しか返しません。最初か最後のどちらかを選べるだけです。全部ほしいときは FILTER、1 つのセルにまとめたいときは TEXTJOIN と組み合わせます。
FILTER は結果を下のセルへ広げて表示します。すぐ下のセルに文字があると、#スピル!（#SPILL!）になりました。結果の数だけ下が空いているか確かめます。
複数該当の抽出ができない・結果が広がらないときの確認表
| 症状 | 主な原因 | 確認と対処 |
|---|---|---|
| #SPILL! | 抽出先に既存のデータや結合セルがある | 抽出先を空き領域にする。結合セルは解除する |
| 生年月日が 33126 のような数値になる | 抽出先の列の書式が日付になっていない | 抽出先の該当列を日付形式にする（元表の書式まではコピーされない） |
| 「該当なし」になる | 氏名の表記が違う（スペース・全角と半角など） | 元表から氏名をコピーして試す。余分なスペースを取り除く |
| #NAME? になる | XLOOKUP・FILTERが使えないExcel（2019・2016）で開いている | INDEX＋SMALLの補助列方式、またはVLOOKUPと補助列を使う |
抽出先に既存データや結合セルがあると#SPILL!になります。FILTERはMicrosoft 365・Excel 2024・2021向けです。Excel 2019・2016で複数件を返す場合はINDEX＋SMALLの補助列方式を使います。
よくある質問
XLOOKUP だけで、一致する行をすべて返すことはできますか？
できません。XLOOKUP が返すのは一致した1件だけです。試したところ、同じ氏名「木村洋子」が2人いる表で =XLOOKUP(B2,B7:B10,D7:D10) は1人目の電話番号（080-****-0001）、6つ目の引数を -1 にすると下から探して2人目（080-****-0004）でした。すべて返すときは FILTER 関数を使います（FILTER関数の使い方）。
一致した件数を知りたいときは？
=ROWS(FILTER(B7:D10,B7:B10=B2)) で、抽出した行の数を数えられます。この表では 2 で、=COUNTIF(B7:B10,B2) と同じでした。
一致した値を1つのセルに「、」でつなげて表示したいときは？
FILTER の結果を TEXTJOIN 関数でつなげます。=TEXTJOIN("、",TRUE,FILTER(D7:D10,B7:B10=B2)) は「080-****-0001、080-****-0004」になりました（TEXTJOIN関数の使い方）。
XLOOKUPで複数該当の2つ目（2番目）を取り出すには？
FILTER の結果を INDEX で指定します。=IF(COUNTIF(B7:B10,B2)<2,"2件目なし",INDEX(FILTER(D7:D10,B7:B10=B2),2)) で、同じ氏名の2件目の電話番号（080-****-0004）が返ります。元表の並び順での2件目です。
XLOOKUPで検索値が2つ以上ある場合（氏名と生年月日など）は、どう書きますか？
条件を掛け合わせて、検索値を 1 にします。=XLOOKUP(1,(B7:B10=B2)*(C7:C10=B3),D7:D10,"該当なし",0) で、氏名と生年月日の両方が合う最初の1件の電話番号が返ります。両方が合う行をすべて取り出すなら FILTER を使います。
FILTER関数の基本的な書き方は、FILTER関数の使い方で説明しています。
コメント