XLOOKUP関数の使い方|初心者向けに基本・具体例・エラー対処を解説

Excelで商品コードから商品名や単価を調べるとき、一覧表を何度も見返していませんか?

商品の種類が多いと、必要な情報を探すだけでも時間がかかります。

そんな作業に使えるのが、XLOOKUP(エックスルックアップ)関数です。

XLOOKUP関数は、Excelの表から指定した値を検索し、同じ行にある商品名や単価などを取り出す関数です。

たとえば、商品コード「P003」を検索して、対応する商品名「ファイル」を表示できます。

基本の書き方はこちらです。

=XLOOKUP(検索値,検索範囲,戻り範囲)

この記事では、5件の商品管理表を使って、XLOOKUP関数の入力方法を3ステップで解説します。

数式と検索結果を確認しながら、商品名・単価の取得、VLOOKUPとの違い、エラーが出たときの対処方法まで説明します。

※この記事は、主にWindows版Excel for Microsoft 365を想定しています。Excel 2024・Excel 2021でもXLOOKUP関数を利用できます。

目次

XLOOKUP関数とは?できることと基本の書き方

XLOOKUP関数は表から必要なデータを取り出す関数

XLOOKUP関数は、商品管理以外にも次のような場面で使えます。

  • 商品コードから商品名や単価を調べる
  • 社員番号から担当者名を取り出す
  • 顧客IDから登録情報を確認する
  • 管理番号から案件の状況を表示する

商品コードと商品名が別々の列に入力されていても、XLOOKUP関数なら対応するデータを探せます。

また、検索する列より右側にあるデータだけでなく、左側にあるデータも取り出せます。

XLOOKUP関数の基本構文と3つの引数

XLOOKUP関数の基本的な書き方は、次のとおりです。

=XLOOKUP(検索値,検索範囲,戻り範囲)

3つの引数には、それぞれ次の意味があります。

引数意味具体例
検索値探したい値P003
検索範囲探したい値が入っている範囲A2:A6
戻り範囲取り出したいデータが入っている範囲B2:B6
XLOOKUP関数の検索値・検索範囲・戻り範囲の仕組み

「引数」は、関数を動かすために指定する情報のことです。

XLOOKUPでは、まず次の3つを決めます。

  1. 何を探したい?
  2. どこから探す?
  3. 何を表示したい?

たとえば「P003という商品コードをA列から探して、B列の商品名を表示したい」という場合は、次の数式になります。

=XLOOKUP("P003",A2:A6,B2:B6)

探したい値を数式に直接入力する方法もあります。

ただ、仕事で繰り返し使うなら、検索する値を別のセルに入力する方法がおすすめです。

商品コードを変更するだけで、数式を書き直さずに別の商品を調べられます。

XLOOKUP関数には最大6つの引数がありますが、基本的な検索は最初の3つで使えます。

4つ目以降の引数では、値が見つからない場合の表示、完全一致・部分一致などの検索方法、検索する方向を指定できます。

この記事では、基本操作を覚えてから、それぞれの指定方法を紹介します。

XLOOKUP関数を使えるExcelのバージョン

XLOOKUP関数は、次のExcelなどで利用できます。

  • Excel for Microsoft 365
  • Excel 2024
  • Excel 2021

買い切り版のExcel 2019・2016には、XLOOKUP関数が搭載されていません。

ただし、同じWindowsパソコンでも、インストールされているExcelの製品・バージョンによって利用できる関数は異なります。

新しいExcelで作成したXLOOKUP入りのファイルを古いExcelで開くと、数式を正しく計算できない場合があります。

仕事でファイルを共有するときは、相手が使用しているExcelのバージョンも確認しておきましょう。

対応バージョンや関数の詳しい仕様は、Microsoft公式のXLOOKUP関数の解説でも確認できます。

XLOOKUP関数の使い方を3ステップで解説

ここからは、実際にExcelでXLOOKUP関数を入力してみましょう。

今回は、商品コードから商品名を取り出します。

ステップ1|商品管理表を用意する

まず、新しいExcelのシートを開き、A1セルからC6セルまで商品管理表を作成します。

ExcelのA1〜C1に見出しを入力し、A2〜C6に商品データを入力します。

下の表の左端にある数字はExcelの行番号です。数字の列は入力せず、A列・B列・C列の内容だけを入力してください。

商品コードは「P001」のように、アルファベットと数字を組み合わせて入力します。

Excelの行番号A列:商品コードB列:商品名C列:単価
1商品コード商品名単価
2P001ノート300
3P002ペン150
4P003ファイル450
5P004ふせん200
6P005はさみ600

続いて、E1セルに「検索コード」、F1セルに「商品名」と入力します。

E2セルには、検索したい商品コード「P003」を入力してください。

商品管理表はこれで完成です。

この表を使って、商品コードから商品名を検索していきます。

ステップ2|XLOOKUP関数を入力する

F2セルを選択して、次の数式を入力します。

=XLOOKUP(E2,A2:A6,B2:B6)

Enterキーを押してください。

E2の商品コードをA2:A6から探し、対応するB列の商品名を表示する数式です。

検索範囲と戻り範囲は、対応するデータの並びをそろえて指定します。

たとえば、A2:A6の商品コードに対してB3:B7の商品名を指定すると、同じ位置にあるデータを取得するため、意図した商品名とずれることがあります。

今回の表では、検索範囲をA2:A6、戻り範囲をB2:B6にしましょう。

ステップ3|検索結果を確認する

正しく入力できていれば、F2セルには「ファイル」と表示されます。

セル内容
E2P003
F2ファイル

A4セルの「P003」と同じ行にあるB4セルの「ファイル」が取り出されています。

商品コードP003から商品名ファイルを取得するXLOOKUP関数の基本操作

E2セルの商品コードを「P001」に変えると、F2には「ノート」と表示されます。

「P005」に変えれば、結果は「はさみ」です。

数式を書き直さなくても、検索する商品コードを変えるだけで結果が切り替わります。

これがXLOOKUP関数の基本的な使い方です。

以降の例では、E2セルを「P003」に戻しておきましょう。

XLOOKUP関数の便利な使い方4選

XLOOKUPでは、商品名だけでなく単価を取得したり、検索方法を変えたりすることもできます。

ここからは、仕事で使いやすい4つの方法を紹介します。

別々の数式で商品名と単価を取得する

先ほどはF2セルに商品名を表示しました。

今度はG1セルに「単価」と入力し、G2セルに次の数式を入力してください。

=XLOOKUP(E2,A2:A6,C2:C6)

戻り範囲をC2:C6に変更すると、商品コードに対応する単価が表示されます。

E2が「P003」の場合は、次の結果になります。

検索コード商品名単価
P003ファイル450

この方法では、F2に商品名を取得する数式、G2に単価を取得する数式をそれぞれ入力しています。

表示したい項目を個別に指定できる方法です。

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

検索する商品コードが表に存在しない場合、XLOOKUP関数は通常 #N/A を返します。

たとえば、商品管理表に「P999」がない場合です。

まず、E5セルに「P999」を入力します。

次に、F5セルへ次の数式を入力してください。

=XLOOKUP(E5,A2:A6,B2:B6,"該当なし")

Enterキーを押すと、F5セルに「該当なし」と表示されます。

E5の検索コードF5の表示
P003ファイル
P999該当なし

4つ目の引数は、指定した値が見つからなかった場合に表示する内容です。

ただし、「該当なし」と表示されたときは、商品コードの入力間違いがないかも確認してください。

左側の列からデータを取り出す

XLOOKUP関数は、検索する列より左側にあるデータも取り出せます。

今度は、商品名から商品コードを調べてみましょう。

E7セルに「ファイル」を入力します。

F7セルを選択し、次の数式を入力してください。

=XLOOKUP(E7,B2:B6,A2:A6)

結果は「P003」です。

入力セル入力内容・結果
E7ファイル
F7XLOOKUPの数式
F7の表示結果P003

XLOOKUPでは、検索する列と取り出す列を別々に指定します。

そのため、取り出したいデータが検索列の左側にあっても問題ありません。

なお、同じ商品名が複数行にある場合は、既定では最初に一致した行のデータが返されます。

1つの数式で商品名と単価をまとめて取得する

XLOOKUP関数では、1つの数式から複数列のデータを取り出すこともできます。

E9セルに「P003」を入力します。

F9セルとG9セルが空いていることを確認してください。

F9セルに次の数式を入力します。

=XLOOKUP(E9,A2:A6,B2:C6)

Enterキーを押すと、F9に「ファイル」、G9に「450」が表示されます。

F9G9
ファイル450

今回はF9に数式を入力しただけですが、G9にも単価が表示されます。

このように、数式の結果が隣のセルにも広がることを「スピル」といいます。

XLOOKUP関数で商品名と単価を2列まとめて取り出す方法

個別取得とまとめて取得の違いは、次のとおりです。

方法数式の数特徴
個別取得2つ取り出す項目をそれぞれ指定する
まとめて取得1つ複数列の結果を一度に表示する

複数列を取得するときは、隣のセルを空けておく必要があります。

すでに別のデータが入力されていると、#SPILL! エラーになることがあります。

XLOOKUP関数の応用|部分一致・逆方向検索・複数条件

ここからは、基本の使い方に慣れた方向けの応用です。

XLOOKUP関数で部分一致検索をする方法

XLOOKUP関数で部分一致検索をするには、* や ? などのワイルドカードを使用し、5つ目の引数に 2 を指定します。

ワイルドカードとは、任意の文字を表すための記号です。

  • *:0文字以上の任意の文字
  • ?:任意の1文字

たとえば、商品名に「ノート」を含むデータを検索するとします。

E11セルに「ノート」と入力し、F11セルに次の数式を入力します。

=XLOOKUP("*"&E11&"*",B2:B6,A2:A6,"該当なし",2)

今回の商品管理表には「ノート」が登録されているため、結果は「P001」です。

たとえば商品名が「方眼ノート」や「ノートA5」であっても、「ノート」を含んでいれば検索対象になります。

ただし、XLOOKUP関数は一致するデータをすべて一覧表示するものではありません。今回の設定では、最初に一致した1件の商品コードを返します。

5つ目の引数にある 2 は、ワイルドカードを使用した照合を指定しています。

最後に一致したデータを取り出す方法

商品管理表や履歴データでは、同じ商品コードが複数行に登録されている場合があります。

XLOOKUP関数は、通常、先頭から検索して最初に一致したデータを返します。

最後に一致したデータを取得したい場合は、6つ目の引数で検索方向を変更できます。

ここでは元の商品管理表を変更せず、別の場所に履歴表を作ってみましょう。

I1セルからK5セルまで、次の表を入力してください。

Excelの行番号I列:商品コードJ列:商品名K列:単価
1商品コード商品名単価
2P003ファイル450
3P002ペン150
4P003ファイル(改訂版)500
5P001ノート300

M2セルに「P003」を入力します。

次にN2セルに次の数式を入力してください。

=XLOOKUP(M2,I2:I5,J2:J5,"該当なし",0,-1)

この数式は、商品コード「P003」をI2:I5の末尾から検索し、最初に見つかった行の商品名を返します。

今回の履歴表では、結果は「ファイル(改訂版)」です。

6つ目の引数 -1 は、検索範囲の末尾から先頭へ向かって探す指定です。

なお、取得できるのは表の中で最後に一致したデータであり、必ずしも日付が最も新しいデータとは限りません。

最新データを取得したい場合は、表の並び順や更新日時も確認してください。

複数条件で検索する方法

XLOOKUP関数は、複数の条件を組み合わせて検索することもできます。

たとえば、同じ商品名が複数登録されている表では、商品コードと商品名の両方を確認したい場合があります。

ここでは、2つの条件がどちらも一致する行から単価を取り出します。

E13セルに「P003」、E14セルに「ファイル」と入力してください。

F13セルに次の数式を入力します。

=XLOOKUP(1,(A2:A6=E13)*(B2:B6=E14),C2:C6,"該当なし")

この数式は、商品コードと商品名が両方一致する行の単価を取り出します。

A2:A6=E13 では商品コード、B2:B6=E14 では商品名が一致するかを調べます。

一致すればTRUE、一致しなければFALSEです。

この2つを掛け算すると、TRUEは1、FALSEは0として計算されるため、両方一致した行だけが1になります。

商品コード商品名コード一致商品名一致両方一致
P001ノートFALSEFALSE0
P002ペンFALSEFALSE0
P003ファイルTRUETRUE1
P004ふせんFALSEFALSE0
P005はさみFALSEFALSE0

今回は、商品コードが「P003」、商品名が「ファイル」の行だけが1になります。

その行の単価は450なので、XLOOKUPの検索結果も450です。

ただし、商品コードが重複しないように管理されている表なら、商品コードだけを条件にする方法で十分な場合があります。

複数条件は、1つの検索値だけでは目的の行を特定できない場合などに使いましょう。

XLOOKUP関数とVLOOKUP関数の違い

Excelでデータを検索する関数として、VLOOKUP(ブイルックアップ)関数もよく使われます。

どちらも表から必要なデータを取り出す関数ですが、数式の書き方や検索できる方向に違いがあります。

検索できる方向と範囲指定の違い

比較項目XLOOKUP関数VLOOKUP関数
基本の引数検索値・検索範囲・戻り範囲検索値・表範囲・列番号・検索方法
検索方向左右どちらの列も取得可能基本的に表範囲の先頭列から右側
完全一致既定で完全一致完全一致にはFALSEなどを指定
返す列戻り範囲で指定列番号で指定
対応バージョンExcel 2021以降などより古いExcelでも利用可能

大きな違いの1つは、XLOOKUPでは取り出したい列をセル範囲で指定できることです。

同じ商品管理表で数式を比較する

最初の商品管理表で、商品コード「P003」から商品名を取り出す場合を比較します。

XLOOKUP関数

=XLOOKUP(E2,A2:A6,B2:B6)

VLOOKUP関数

=VLOOKUP(E2,A2:C6,2,FALSE)
XLOOKUP関数とVLOOKUP関数の違いを数式で比較

どちらも結果は「ファイル」です。

ただし、指定方法が異なります。

XLOOKUPでは検索範囲と戻り範囲をそれぞれ指定します。一方、VLOOKUPでは表の範囲と、取り出したい列番号を指定します。

表の列構成が変わる可能性がある場合は、XLOOKUPの方が数式を管理しやすいことがあります。

また、VLOOKUP関数では通常、検索対象の列が指定範囲の一番左にある必要があります。

XLOOKUP関数なら、取り出したいデータが左側にあっても検索できます。

ただし、Excel 2019以前の利用者とファイルを共有する場合など、VLOOKUP関数を選んだ方がよいケースもあります。

XLOOKUP関数がうまく動かないときの対処方法

XLOOKUP関数を入力しても、期待した結果が表示されない場合があります。

まずは、表示されているエラーや症状に合わせて確認しましょう。

症状最初に確認すること
#N/A検索値が存在するか
#NAME?関数名・Excelのバージョン
#VALUE!検索範囲と戻り範囲の形
#SPILL!結果を表示するセルにデータがないか
0が表示される戻り先のセルが空白か
コピーすると結果がおかしい参照範囲がずれていないか
XLOOKUP関数のよくあるエラーと確認ポイント

#N/Aが表示される場合

#N/A は、指定した検索値が見つからないときに表示される代表的なエラーです。

たとえば、商品コード「P999」がA列に存在しない場合です。

まず、次の点を確認してください。

  • 商品コードの入力に間違いがないか
  • 検索範囲に目的の値が含まれているか
  • 文字列と数値が混在していないか
  • 余分なスペースが入力されていないか

「P003」と「P003 」のように、末尾に余分な空白があると一致しない場合があります。

見つからない場合に表示する文字を指定したいときは、4つ目の引数を使用します。

=XLOOKUP(E2,A2:A6,B2:B6,"該当なし")

ただし、エラーが出なくなっただけで、商品コードの入力ミスが解決したわけではありません。

必要に応じて元データも確認してください。

#NAME?が表示される場合

#NAME? は、Excelが数式に使用した名前や関数を認識できない場合などに発生します。

よくある原因は次のとおりです。

  • 関数名のスペルが間違っている
  • 対応していないExcelのバージョンを使用している
  • 文字列をダブルクォーテーションで囲んでいない

たとえば、次の数式は関数名のスペルが間違っています。

=XLOKUP(E2,A2:A6,B2:B6)

正しくは XLOOKUP です。

スペルが正しいのに使用できない場合は、Excelのバージョンを確認しましょう。

#VALUE!・#SPILL!が表示される場合

#VALUE! が表示された場合は、数式に指定した値や範囲を確認しましょう。

XLOOKUPでは、検索範囲と戻り範囲の行数や列の向きが合っていないと、正しい結果を取得できません。

まずは、次のように範囲を指定し間違えていないか確認してください。

たとえば、次の数式では検索範囲が5行、戻り範囲が4行になっています。

=XLOOKUP(E2,A2:A6,B2:B5)

今回の表では、検索範囲と戻り範囲をどちらも5行にそろえます。

=XLOOKUP(E2,A2:A6,B2:B6)

なお、#VALUE! にはほかの原因もあるため、エラーが解消しない場合は数式全体を確認してください。

一方、#SPILL! は、複数の結果を表示しようとしたときに、表示先が使用できない場合などに発生します。

複数列をまとめて取り出す数式を入力している場合は、結果が広がる先のセルにデータがないか確認してください。

不要なデータを消す前に、その内容が必要なものではないかを確認しましょう。

0や意図しない結果が表示される場合

XLOOKUP関数で0が表示された場合は、検索した行の戻り先が空白になっていないか確認してください。

C4セルに入力されている「450」を、一時的に削除します。

E2セルが「P003」になっていることを確認し、H2セルに次の数式を入力してください。

=XLOOKUP(E2,A2:A6,C2:C6)

C4セルが空白の場合、H2セルには0が表示されます。

確認が終わったら、C4セルに「450」を入力し直してください。

H2セルも450に戻れば、正しく元のデータを参照できています。

また、次の点も確認してください。

  • 戻り範囲に間違った列を指定していないか
  • 商品コードに重複がないか
  • 検索範囲と戻り範囲の行の対応がずれていないか
  • 元データに正しい数値が入力されているか

XLOOKUP関数は、既定では最初に一致した行のデータを返します。

商品コードが重複している場合、意図した行とは別の結果が返されることがあります。

なお、単価が本当に0円という場合もあります。

0を非表示にする前に、元のセルが空白なのか、0が入力されているのかを確認してください。

数式をコピーすると参照範囲がずれる場合

XLOOKUP関数を下の行にコピーしたとき、検索範囲がずれてしまうことがあります。

たとえば、次の数式をF2に入力したとします。

=XLOOKUP(E2,A2:A6,B2:B6)

そのままF3へコピーすると、参照先が次のように変わります。

=XLOOKUP(E3,A3:A7,B3:B7)

検索する値はE3に変わっても、商品管理表の範囲は固定しておきたいところです。

その場合は、検索範囲と戻り範囲を絶対参照にします。

E3セルに「P001」を入力してください。

F2セルの数式を次のように修正します。

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6)

F2セルをコピーしてF3セルに貼り付けると、検索値の参照先だけがE3に変わります。

F3セルには「ノート」と表示されます。

コピー後のF3セルの数式は、次のとおりです。

=XLOOKUP(E3,$A$2:$A$6,$B$2:$B$6)

今回は $A$2:$A$6 と $B$2:$B$6 に $ を付けているため、数式をコピーしても商品管理表の範囲は変わりません。

一方、E2には $ を付けていないため、下の行にコピーするとE3に変わります。

複数行の商品コードをまとめて検索するときに便利な書き方です。

まとめ|まずは商品コードの検索から試してみよう

XLOOKUP関数は、表の中から指定した値を探し、対応するデータを取り出せる関数です。

基本の書き方は次のとおりです。

=XLOOKUP(検索値,検索範囲,戻り範囲)

基本的な検索なら、最初の3つの引数を指定すれば使えます。

商品コードから商品名を取り出す場合は、次の数式です。

=XLOOKUP(E2,A2:A6,B2:B6)

見つからない場合の表示を変えたいときは4つ目の引数、部分一致で検索するときは5つ目の引数を使います。

まずはE2セルに「P003」と入力して、F2セルに「ファイル」と表示されるか確認してみてください。

基本の検索ができたら、単価の取得や、見つからない場合の表示変更にも挑戦できます。

毎回一覧表を探す代わりに、商品コードを入力して必要な情報を取り出せるようになります。

Excelのほかの関数も知りたい方は、Excelでよく使う関数15選で、仕事に使いやすい関数を紹介しています。

参考情報

この記事では、Microsoftの公式資料を参照しています。

※仕様・対応バージョンは2026年10月29日時点のMicrosoft公式資料を参考にしています。

目次