XLOOKUP関数で別シートから検索・抽出する方法｜複数条件の数式と実例

XLOOKUPで別シートから値を取り出すには、検索範囲と戻り範囲に「シート名!範囲」を指定します。たとえば「データ」シートのB列から果物IDを探し、同じ行のF列の価格を返す式は次のとおりです。

無料Excelサンプルで、まず試してみる

果物IDと産地を指定すると、別シートの表から価格を取り出せます。完成済みの数式を使い、条件を変えたときの結果を確かめましょう。

別シートのXLOOKUP検索のExcelサンプルをダウンロード（無料）

Excelファイル（.xlsx）／登録不要／数式・練習データ入り
対応：Microsoft 365・Excel 2021・Excel 2024

ダウンロードしたら、この3ステップ

1. ダウンロードしたファイルを開き、「検索練習」シートを選びます。

2. 果物IDのB2は21のまま、黄色いB3セルの「青森」を「山形」に変更します。

3. B5の価格が800から1,500に変われば成功です。「データ」シートの果物ID21・産地「山形」の価格を取得しています。

B8はIDだけで探した先頭の1件、A11以降は同じIDの全件です。B3の産地を変えて動くのは2条件検索のB5です。下の結果との違いは、本文で順番に説明します。

開いたときに保護ビューが表示され、入力できない場合は、ファイルの入手元を確認して「編集を有効にする」を選んでください。

B2は数式を入力するシートの検索値です。「データ」シートのB2とは別のセルなので区別してください。XLOOKUPはMicrosoft 365・Excel 2021・2024で利用でき、Excel 2019・2016では利用できません。

まず動かしてみよう｜この記事のサンプルをその場で試せます

練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。

サンプルデータとシートの準備

ファイルには「検索練習」と「データ」の2シートがあります。「データ」のA1:F8は次の表です。「検索練習」の黄色いB2・B3を書き換えて試せます。B5には2条件検索の完成式が入っています。基本から試すときは、コピーしたファイルでB5の式を順に置き換えてください。

▶ 別シートから探す式（B6）を、上の練習ツールで試す

別シートから検索する基本の手順

果物IDが21の行は2行ありますが、通常のXLOOKUPは上から最初に一致する行を返すため、山形の1500になります。青森の800を選びたい場合は、産地も検索条件に加えます。

シート名を間違えずに指定するには

数式入力中に「データ」シートのタブをクリックし、対象範囲をドラッグするとシート参照が入力されます。シート名に空白がある場合も、'売上 データ'!$B$2:$B$8のようにシングルクォートで囲めば指定できます。

$はコピー時に範囲がずれないための固定です。検索値のB2を変えれば結果が変わり、式を下へコピーすれば検索値はB3、B4と移動します。検索値も固定したい場合は$B$2にします。

複数条件で別シートを検索する

検索練習シートのA3に「産地」、B3に「青森」を入力します。B5の式を次に置き換えてください。

B2が21、B3が青森なら結果は800です。B3を山形に変えると1500になります。

• 'データ'!$B$2:$B$8=B2で果物IDが一致する行を調べます。
• 'データ'!$E$2:$E$8=B3で産地が一致する行を調べます。
• 2つの条件を掛けると、両方がTRUEの行だけが1になります。
• XLOOKUPで1を探し、同じ行の価格を返します。

この方法なら、条件を文字列としてつなぐ際の衝突を避けられます。条件が3つなら比較式をもう1つ掛けます。各条件の範囲と戻り範囲は、同じ行数にそろえてください。

▶ 2条件の式（B5）を、上の練習ツールで試す

「&」で条件をつなぐ場合の注意

複数条件を&で連結する書き方もあります。たとえば、このデータで次の式を使うと1500になります。

この表では使えますが、区切りなしの連結では「1と23」「12と3」が同じ「123」になります。条件の値が変わる実務の表では、上記の条件ごとに比較して掛け合わせる式が分かりやすく、こうした衝突も避けられます。

2列以上をまとめて取り出す・一致する全行を取り出す

▶ 2列を取り出す式（B8）を、上の練習ツールで試す ／ FILTERで全行を取り出す式（A11）を、上の練習ツールで試す

同じ行の複数列を返す

戻り範囲を複数列にすると、最初に一致した行の複数項目を横へ表示できます。次の式は「データ」のE～F列、つまり産地と価格を返します。

検索練習シートのB8などへ入力し、右隣のC8を空けてください。B2が21なら「山形」「1500」が並びます。

同じIDの行をすべて取り出す

XLOOKUPの「複数列を返す」ことと、「一致した複数行を全部返す」ことは別です。後者にはFILTERを使います。検索練習シートのA11など、6列×2行分の空きがある場所に次の式を入力してください。

B2が21なら、さくらんぼの山形・青森の2行が返ります。文字の一部を条件に行を取り出す場合は特定の文字を含む行を抽出する方法をご覧ください。

別シートから検索できないときの確認点

数値の21と文字列の「21」は、完全一致検索で一致しない原因になります。ISNUMBERやTYPEで双方の型を調べ、必要なら文字列を数値に一括変換する方法でそろえてください。先頭の0が必要なコードは、文字列に統一して扱います。

戻り先の元セルが空白なら、0が返る場合があります。これは「見つからなかった」とは別です。「該当なし」は検索条件に一致する行がない場合の表示です。

よくある質問

Excel 2019・2016ではどうすればよいですか？

1条件の基本例なら、VLOOKUPで次のように書けます。データのB列からF列までの5列を対象にし、5列目の価格を完全一致で取り出します。

この式も同じIDが複数あると先頭の行を返します。複数条件では補助列などを用いた別の設計が必要です。

別シートと別ファイルの参照は同じですか？

この記事は同じブック内の別シートを対象にしています。別ファイルではブック名を含む参照になり、ファイルの移動や名前の変更にも注意が必要です。ここで示したシート名だけの式をそのまま使うことはできません。

複数のシートを一度に検索できますか？

1つの別シートを参照する例とは異なり、検索するシートを選ぶ、またはデータをまとめる設計が必要です。複数シートからシートを選んで検索する方法を参照してください。

解説を読んだら、サンプルで試してみましょう

別シートのXLOOKUP検索のExcelサンプルをダウンロード（無料）

開くシートと最初の操作をもう一度確認する

関連する解説・参考資料

• XLOOKUPの部分一致・ワイルドカード検索
• FILTERで条件に合う行をすべて抽出
• Microsoft公式：XLOOKUP関数