Excel XLOOKUP関数の使い方｜別シートの商品名・単価を自動入力する手順

2026年8月17日

別の表から、コードやIDに対応する名称・金額などを探して転記する作業は、件数が増えるほど手間がかかります。検索する列や返したい列が変わるたびに、数式の指定方法で迷うこともあります。

ExcelのXLOOKUP関数を使うと、指定した値を表から検索し、同じ行にある別の値を取得できます。VLOOKUP関数のように戻り列の番号を数える必要がなく、検索列の左右どちらにある値も取得できます。

この記事では、XLOOKUPの基本的な使い方を、商品コードから商品名と単価を取得する例で解説します。誤入力を減らせるよう、数式を直接入力せず、「関数の挿入」と「関数の引数」ダイアログを使って設定します。

この記事でできること

XLOOKUPで指定する「検索値」「検索範囲」「戻り範囲」の役割が分かります。

関数の挿入ダイアログを使い、別シートを検索するXLOOKUPを設定できます。

数式をコピーするときの範囲の固定や、値が見つからない場合の表示を設定できます。

XLOOKUPの操作例：商品コードから情報を取得する

XLOOKUPの操作を確認するため、今回は「売上入力」と「商品マスタ」の2つのシートをサンプルとして使います。「売上入力」シートの商品コードを検索値にして、「商品マスタ」シートから対応する商品名と単価を取得します。

シート	主な列	役割
売上入力	注文日、注文番号、商品コード、商品名、単価、数量、売上金額	商品コードを入力し、検索結果を表示する
商品マスタ	商品コード、商品名、カテゴリ、単価	検索元になる商品情報を管理する

操作を完了すると、商品名セルには次の数式が設定されます。

=XLOOKUP(C2,商品マスタ!$A$2:$A$11,商品マスタ!$B$2:$B$11,"未登録")

数式全体を手入力する必要はありません。以降の手順では、関数の引数ダイアログで、検索する値、探す範囲、表示する範囲を1つずつ指定します。

XLOOKUPを使えるExcelを確認する

XLOOKUPは、Microsoft 365、Excel 2024、Excel 2021などで利用できます。Microsoft公式では、Excel 2019とExcel 2016ではXLOOKUPを使用できないと案内されています。

利用できるバージョンと引数の最新情報は、Microsoft公式のXLOOKUP関数で確認できます。

Excel	XLOOKUP
Microsoft 365	利用可能
Excel 2024	利用可能
Excel 2021	利用可能
Excel 2019	利用不可
Excel 2016	利用不可

注意

この手順で作ったブックをExcel 2019やExcel 2016の利用者へ共有すると、XLOOKUPを再計算できません。社内で複数のExcelバージョンを使っている場合は、作成前に共有先の環境も確認してください。

この記事では、Microsoft Office Home and Business 2021の画面を想定して手順を記載しています。

XLOOKUPの基本的な指定方法

XLOOKUPには6つの引数がありますが、基本の検索で必須になるのは「検索値」「検索範囲」「戻り範囲」の3つです。この記事では、商品が見つからない場合の表示も指定します。

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])

引数	今回指定する内容	必須
検索値	売上入力シートの商品コード	必須
検索範囲	商品マスタの商品コード列	必須
戻り範囲	商品名または単価の列	必須
見つからない場合	「未登録」または空欄	任意
一致モード	今回は省略。省略時は完全一致	任意
検索モード	今回は省略。省略時は先頭から検索	任意

XLOOKUPで検索値を商品マスタの商品コード列から探し、同じ行の商品名と単価を取得する流れ

商品コードのように、入力した値と同じものを探したい場合は、既定の完全一致をそのまま使えます。一致モードや検索モードを無理に指定する必要はありません。

関数の挿入ダイアログでXLOOKUPを設定する

1．売上入力シートと商品マスタを確認する

「売上入力」シートでは、C列に商品コード、D列に商品名、E列に単価を配置します。

「商品マスタ」シートでは、A列に商品コード、B列に商品名、C列にカテゴリ、D列に単価を配置します。

XLOOKUPで検索する商品コードは、両方のシートで同じ形式にそろえてください。たとえば、売上入力側が文字列のP001であれば、商品マスタ側も同じP001にします。

2．商品名を表示するセルから関数の挿入を開く

「売上入力」シートを開き、最初の商品名を表示するD2セルを選択します。「数式」タブの「関数の挿入」をクリックするか、数式バー左側の「fx」をクリックします。数式を直接入力すると、かっこやカンマ、参照範囲の誤入力が起きやすいため、この記事ではダイアログから設定します。

3．XLOOKUP関数を選択する

「関数の挿入」ダイアログが開いたら、「関数の検索」へXLOOKUPと入力して「検索開始」をクリックします。検索結果から「XLOOKUP」を選択し、「OK」をクリックします。

続いて「関数の引数」ダイアログが開きます。「検索値」「検索範囲」「戻り範囲」などの入力欄が表示されることを確認します。

このダイアログでは各引数を別々の入力欄へ指定できるため、数式の区切り位置を意識せずに設定できます。

4．「検索値」に商品コードを指定する

「関数の引数」ダイアログの「検索値」欄をクリックし、「売上入力」シートのC2セルを選択します。今回探したい値は、D2セルと同じ行に入力されている商品コードです。

「検索値」欄にC2と表示されたことを確認します。セルをクリックして指定すれば、セル番地を手入力する必要はありません。

ダイアログ右側の計算結果には、C2セルへ入力されている商品コードが表示されます。意図したセルを参照できているか、次の項目へ進む前に確認します。

5．「検索範囲」に商品コード列を指定する

「検索範囲」欄をクリックし、「商品マスタ」シートへ切り替えます。商品コードが入力されているA2からA11までを選択します。ダイアログが範囲選択の邪魔になる場合は、入力欄右側の範囲選択ボタンをクリックして一時的に折りたたみます。

「検索範囲」欄に商品マスタ!A2:A11と表示されたことを確認します。

そのままF4キーを押下し$A$2:$A$11にしておきます。これは、数式を下の行へコピーしても範囲がずれないよう、行番号と列記号に$が付けて絶対参照にしておくということです。

6．「戻り範囲」を指定して結果を確認する

「戻り範囲」欄をクリックし、「商品マスタ」シートの商品名が入力されているB2からB11までを選択します。検索範囲と戻り範囲が、どちらも2行目から11行目までになっていることを確認し、絶対参照（$を付ける）にしたうえで「OK」をクリックします。

D2セルへ商品コードに対応する商品名が表示されれば設定完了です。

7．数式を下の行までコピーする

D2セルを選択し、セル右下のフィルハンドルを下方向へドラッグします。各行では商品コードのセルだけがC3、C4と変わり、商品マスタの検索範囲と戻り範囲は固定されたままになります。

コピー後に検索範囲がA3:A12のようにずれている場合は、元の数式へ戻り、商品マスタの範囲を絶対参照へ変更します。

戻り範囲を変えて別の値を取得する

商品名を取得できたら、戻り範囲を変更して別の値も取得してみます。操作例として、「売上入力」シートのE2セルへ単価を表示します。E2セルを選択して「関数の挿入」からXLOOKUPを開き、検索値と検索範囲は商品名のときと同じ内容を指定します。

=XLOOKUP(C2,商品マスタ!$A$2:$A$11,商品マスタ!$D$2:$D$11)

変更するのは、3番目の引数「戻り範囲」です。商品名を取得するときはB2:B11、単価を取得するときはD2:D11を指定します。関数の引数ダイアログで「戻り範囲」を使い分けると、同じ検索値から異なる情報を取得できます。

E2セルの数式も下の行までコピーします。この例では商品名と単価を取得していますが、社員番号から氏名や部署を取得する場合なども、同じ考え方で検索範囲と戻り範囲を指定できます。

検索値が見つからない場合の表示を設定する

基本の数式では、検索値が検索範囲に存在しないと#N/Aが表示されます。操作例として、売上入力シートへ商品マスタにない商品コードを入力し、表示を確認します。

D2セルを選択し、数式バー左側の「fx」をクリックして「関数の引数」ダイアログを開きます。「見つからない場合」欄へ未登録と入力して「OK」をクリックし、11行目までオートフィルします。

=XLOOKUP(C2,商品マスタ!$A$2:$A$11,商品マスタ!$B$2:$B$11,"未登録")

未登録の商品コードを入力すると、#N/Aの代わりに「未登録」と表示されます。エラーを空欄にすることもできますが、商品マスタへの登録漏れに気づけるよう、商品名には「未登録」と表示する方が確認しやすくなります。

単価などを未登録時に空欄とする場合は、関数の引数ダイアログの「見つからない場合」欄へ""を入力します。

=XLOOKUP(C2,商品マスタ!$A$2:$A$11,商品マスタ!$D$2:$D$11,"")

XLOOKUPとVLOOKUPの違い

同じ商品名をVLOOKUPで取得する場合は、次のように範囲内の列番号と完全一致を示すFALSEを指定します。

=VLOOKUP(C2,商品マスタ!$A$2:$D$11,2,FALSE)

XLOOKUPでは検索範囲と戻り範囲を別々に選択します。列番号を数える必要がなく、検索列より左側にある値も取得できます。MicrosoftもXLOOKUPをVLOOKUPの改良版として案内しています。

比較項目	XLOOKUP	VLOOKUP
戻り列の指定	戻り範囲を選択する	表の左端から列番号を数える
検索方向	左右どちらも可能	基本的に右方向
完全一致	既定値のため省略可能	FALSEを指定する
列を追加した場合	指定範囲が変わらなければ影響を受けにくい	列番号がずれる場合がある
Excel 2019・2016	利用不可	利用可能