XLOOKUP関数は、検索する列と返す列を別々に指定する照合関数です。VLOOKUPと違って左側の列も引けて、列番号を数える必要もありません。見つからない場合の表示も第4引数で指定できます。ただし対応はExcel 2021以降とMicrosoft 365で、Excel 2019以前では使えません。
この記事でわかること
- XLOOKUP関数の6つの引数と、実際に書くのは3つでいい理由
- 一致モード・検索モードの4つの値の意味と選び分け
- 複数条件で引く／複数列をまとめて返す／最後の一致を取る書き方
#NAME?・#VALUE!・#SPILL!が出る原因と直し方- VLOOKUP・INDEX＋MATCH・FILTERとの使い分け
XLOOKUP関数の構文と引数
構文
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])| 引数 | 指定するもの | 省略時 |
|---|---|---|
| 検索値 | 探したい値 | 必須 |
| 検索範囲 | 検索値を探す1列（または1行） | 必須 |
| 戻り範囲 | 返したい値が入っている列（または行） | 必須 |
| 見つからない場合 | 該当なしのときに表示する値 | #N/A |
| 一致モード | 完全一致か、近い値を許すか | 0＝完全一致 |
| 検索モード | どちらの端から探すか | 1＝先頭から |
実務で書くのは前の4つです。後半2つは既定のままで問題ありません。
一致モードの4つの値
| 値 | 動作 | 使う場面 |
|---|---|---|
0 | 完全一致（既定） | 商品コード・社員番号など |
-1 | 完全一致、なければ次に小さい値 | 料金表・税率表の段階判定 |
1 | 完全一致、なければ次に大きい値 | 送料表など上限で区切る表 |
2 | ワイルドカード（* ? ~）を有効にする | 部分一致で探したい |
検索モードの4つの値
| 値 | 動作 |
|---|---|
1 | 先頭から末尾へ探す（既定） |
-1 | 末尾から先頭へ探す（＝最後に一致した行が取れる） |
2 | 昇順に並んだ範囲を高速に探す |
-2 | 降順に並んだ範囲を高速に探す |
基本の使い方
商品マスタが F列（商品コード）・G列（商品名）・H列（単価）に並んでいるとします。A2の商品コードから単価を引く式です。
=XLOOKUP($A2, $F$2:$F$50, $H$2:$H$50, "該当なし")検索するのはF列、返すのはH列。列番号を数える工程がありません。マスタに列を1本挿入しても、この式は壊れません。
第4引数で「該当なし」を出す
VLOOKUPでは IFERROR で包む必要があった処理が、第4引数だけで済みます。
=XLOOKUP($A2, $F$2:$F$50, $H$2:$H$50, 0)0 を指定すれば、未登録の行も数値として合計に含められます。IFERRORで包むと本来のエラーまで隠れますが、第4引数は「見つからなかったとき」だけに反応するため、原因の切り分けが楽になります。
範囲は絶対参照にする
$F$2:$F$50 のようにドル記号で固定します。固定せずに下へコピーすると、検索範囲と戻り範囲がそろって1行ずつ下へずれます。範囲を入力した直後にF4キーを押すと切り替わります。
実務でよく使う4パターン
① 左側の列を引く
商品名から商品コードを逆に引く場合です。VLOOKUPでは表の作り替えが必要でしたが、XLOOKUPは引数を入れ替えるだけです。
=XLOOKUP($A2, $G$2:$G$50, $F$2:$F$50, "該当なし")検索範囲と戻り範囲の左右の位置関係に制約がありません。
② 複数の列をまとめて返す
戻り範囲を複数列で指定すると、右方向へ値が並びます。
=XLOOKUP($A2, $F$2:$F$50, $G$2:$H$50, "")1つの式で商品名と単価の両方が入ります。この動作はスピルと呼ばれ、右隣のセルにデータが入っていると #SPILL! になります。
③ 複数条件で引く
条件を掛け算でつなぎ、1 を検索します。作業列は不要です。
=XLOOKUP(1, ($E$2:$E$50=$A2)*($F$2:$F$50=$B2), $H$2:$H$50, "該当なし")条件が成立する行だけ 1×1＝1、それ以外は 0 になる仕組みです。3条件以上にしたい場合は、同じ形で掛け算を増やします。
④ 最後に一致した行を取る
同じコードが何度も登場する履歴データで、最新の1件を取りたい場合です。検索モードに -1 を指定します。
=XLOOKUP($A2, $F$2:$F$500, $H$2:$H$500, "", 0, -1)第5引数（一致モード）を飛ばせないので、既定値の 0 を明示して6番目に -1 を置きます。引数を飛ばして書けない点が、ここでの注意点です。
⑤ 料金表で段階判定する
「1万円以上は送料無料、5千円以上は300円」のように段階で決まる表は、一致モードに -1 を指定します。
=XLOOKUP($A2, $F$2:$F$10, $G$2:$G$10, "", -1)検索範囲には各段階の下限値を並べます。一致する値がなければ、それより小さい側のいちばん近い段階が選ばれます。
うまくいかないときの原因と直し方
| 表示 | 原因 | 直し方 |
|---|---|---|
#NAME? | Excel 2019以前にはXLOOKUPが無い | VLOOKUPかINDEX＋MATCHに置き換える |
#N/A | 検索値が検索範囲に見つからない | 第4引数を指定する。原因は下記の4点を確認 |
#VALUE! | 検索範囲と戻り範囲の行数が違う | 両方の範囲を同じ行数にそろえる |
#SPILL! | 結果を書き出す先にデータがある | 出力先のセルを空ける |
#REF! | 参照していたセルや列が削除された | 範囲を指定し直す |
| 一部の行だけエラー | 範囲が相対参照のままコピーされた | $F$2:$F$50 の形に固定する |
#N/Aが出るときに見る4か所
見た目が同じでも一致しないことがあります。次の順が早道です。
- 検索値がマスタに存在しない — マスタ側をフィルタして実在を確認する
- 前後に余分な空白がある —
=TRIM(A2)で確認し、置換で除去する - 数値と文字列が混ざっている —
=ISNUMBER(A2)と=ISNUMBER(F2)の結果が食い違えば型の不一致 - 全角と半角が混ざっている —
=ASC(A2)で半角に寄せて比較する
型の不一致はセルの寄り方でも見分けられます。数値は既定で右寄せ、文字列は左寄せ。片方だけ左に寄っていたら型が違います。
古いExcelで開くと壊れる
XLOOKUPを含むブックをExcel 2019以前で開くと、数式が _xlfn.XLOOKUP(...) という表示になり、結果は #NAME? です。再保存すると数式ごと失われることがあるため、社外へ配布するブックや、環境がそろっていない相手と共有するファイルではVLOOKUPを使うほうが安全です。
対応しているのは Microsoft 365・Excel 2021以降・Excel for the web です。
似た関数との使い分け
| 関数・組み合わせ | 対応バージョン | 選ぶ場面 |
|---|---|---|
| XLOOKUP | 2021〜 | 環境が2021以降にそろっている。基本はこれ |
| VLOOKUP | 2016〜 | 2019以前の相手にも渡すブック |
| INDEX＋MATCH | 2016〜 | 左側の列を取りたいが、2019以前にも対応したい |
| FILTER | 2021〜 | 一致する行を1件でなく全件取り出したい |
| XMATCH | 2021〜 | 値ではなく「何番目にあるか」を知りたい |
XLOOKUPは一致した最初（または最後）の1件しか返しません。該当するすべての行がほしい場合はFILTER関数の担当です。
行と列の両方で交差検索する
XLOOKUPを入れ子にすると、縦横の見出しから交点の値を取り出せます。
=XLOOKUP($A2, $A$5:$A$30, XLOOKUP($B2, $B$4:$H$4, $B$5:$H$30))内側のXLOOKUPが列を1本まるごと返し、外側がその中から行を選ぶ構造です。INDEX＋MATCHを2つ組む書き方と結果は同じですが、引数の意味が読み取りやすくなります。
よくある質問
Q1：VLOOKUPで作った既存の式は、全部XLOOKUPに書き換えるべきですか？
書き換える必要はありません。VLOOKUPは廃止されておらず、動作も変わっていません。書き換えるのは「列を挿入して壊れた」「左側の列が必要になった」など、具体的な不具合が起きたところだけで十分です。
Q2：検索値の大文字と小文字は区別されますか？
区別されません。ABC と abc は同じ値として一致します。この点はVLOOKUPと同じです。
Q3：部分一致で探せますか？
探せます。一致モードに 2 を指定すると、*（任意の文字列）と ?（任意の1文字）が使えます。
=XLOOKUP("東京*", $F$2:$F$50, $H$2:$H$50, "", 2)* そのものを文字として探したいときは、直前に ~ を付けます。
Q4：料金表のように「〇〇円以上」で段階判定できますか？
できます。一致モードに -1（次に小さい値）を指定し、検索範囲には各段階の下限値を並べます。VLOOKUPの近似一致と違い、範囲が昇順に並んでいなくても結果が保証される点が扱いやすくなっています。
Q5：スピルした結果の一部だけを使えますか？
先頭セルに # を付けて =$J$2# と書くと、スピルした範囲全体を参照できます。一部だけを取り出したい場合は、INDEX で位置を指定するか、そもそも戻り範囲を必要な列だけに絞って書くほうが簡単です。
まとめ
- 引数は6つだが、実務で書くのは検索値・検索範囲・戻り範囲・見つからない場合の4つ
- 検索する列と返す列が独立するので左側の列も引ける・列番号も不要
- 第4引数があるためIFERRORで包む必要がない
- 最新の1件は検索モード
-1。引数を飛ばせないので0, -1と2つ書く - ⛔ Excel 2019以前では
#NAME?。配布するブックはVLOOKUPのままにする
※関数の対応バージョンはExcelの一般提供時点の情報にもとづいています。お使いの環境や更新プログラムの適用状況により、利用できる関数や表示が異なる場合があります。
あわせて読みたい
