XLOOKUP関数の練習問題（全5問）

色のついたセルに数式を入力して Enter。範囲はドラッグでも選べます。

0 / 5 問クリア
Q1

コードから商品名を取り出す

B8 に、B7 のコードに対応する商品名を XLOOKUP で取り出してください。

解説を見る

VLOOKUP のように表全体と列番号を渡すのではなく、探す列と取り出す列をそれぞれ範囲で指定します。列番号を数えないので、表の途中に列を挿入しても式が壊れません。完全一致が既定なので FALSE も不要です。

模範解答： =XLOOKUP(B7,A2:A4,B2:B4)

XLOOKUP関数は、検索する範囲と取り出す範囲を別々に指定して値を探す関数です。VLOOKUPの弱点を解消した後継にあたります。

書き方
=XLOOKUP(検索値, 検索範囲, 戻り範囲)
例
=XLOOKUP(B7,A2:A4,B2:B4)

XLOOKUP関数とは

XLOOKUP（エックスルックアップ）関数は、表から目的の値を探して取り出す関数です。VLOOKUP と目的は同じですが、VLOOKUP が苦手だったところを作り直した後継にあたります。

VLOOKUP を使っていて、こう思ったことがあるなら XLOOKUP の出番です。

  • 列番号を数えるのが面倒。列を挿入したら式が壊れた
  • 検索する列より左の列を取り出したいのにできない
  • FALSE の付け忘れで、たまに違う行が返ってくる
  • 見つからないときの #N/A を消すのに、毎回 IFERROR で包んでいる

これらは全部、XLOOKUP では起きません。

3つの引数

=XLOOKUP(検索値, 検索範囲, 戻り範囲)
位置 意味
1つめ 探したい値
2つめ どこを探すか（1列だけ）
3つめ どこから取り出すか（1列だけ）

VLOOKUP は「表全体」と「左から何列目か」を渡しました。XLOOKUP は探す列と取り出す列を、それぞれ直接指定します。

=VLOOKUP(B7,A2:C4,2,FALSE)     ← 表全体と列番号
=XLOOKUP(B7,A2:A4,B2:B4)       ← 探す列と取り出す列

VLOOKUPとの違い

VLOOKUP XLOOKUP
列の指定 番号で数える 範囲で指定する
列を挿入したとき 式が壊れる 壊れない
検索列より左 取り出せない 取り出せる
完全一致 FALSE が必要 既定が完全一致
見つからないとき #N/A 4つめの引数で指定できる

とくに大きいのが上の2つです。VLOOKUP の列番号は「表の左端から何番目か」なので、表の途中に列を1つ挿入すると、式は何も言わずに違う列を返します。エラーが出ないぶん、間違いに気づきません。

XLOOKUP は取り出す範囲そのものを指しているので、列が増えても参照はついて動きます。

左の列を取り出せる

VLOOKUP の最大の制約が「検索列より右しか取り出せない」ことでした。商品名から商品コードを引きたい、といった逆方向ができません。

XLOOKUP は探す範囲と取り出す範囲が独立しているので、左右どちらでも構いません。

=XLOOKUP("バナナ",B2:B4,A2:A4)    → B01

表を作り直したり、列を並べ替えたりする必要がなくなります。

つまずきポイント1: 見つからないときの表示は4つめに書く

見つからないときは #N/A が返ります。これを別の表示にしたいときは、4つめの引数に書きます。

=XLOOKUP(B7,A2:A4,B2:B4,"該当なし")

VLOOKUP では IFERROR で包む必要がありましたが、XLOOKUP は自前で持っています。

こちらのほうが安全です。IFERROR は式の中で起きたエラーをすべて隠すため、列番号の間違いによる #REF! まで「該当なし」に化けてしまいます。4つめの引数は見つからなかったときだけ働くので、他のエラーは隠しません。

つまずきポイント2: 既定ではワイルドカードが効かない

* や ? を使った部分一致は、そのままでは効きません。

=XLOOKUP("A*",A2:A5,B2:B5,"-")     → -        一致しない
=XLOOKUP("A*",A2:A5,B2:B5,"-",2)   → りんご    5つめに 2 を指定する

XLOOKUP の既定は完全一致で、* はただの文字として扱われます。VLOOKUP は完全一致でもワイルドカードが効くので、そこだけ挙動が違います。

5つめ・6つめの引数

普段は使いませんが、必要なときのために。

引数 値 意味
5つめ（照合の種類） 0 完全一致（既定）
-1 完全一致、なければ次に小さい値
1 完全一致、なければ次に大きい値
2 ワイルドカードを使う
6つめ（検索方法） 1 先頭から探す（既定）
-1 末尾から探す

-1 や 1 は、点数からランクを求めるような区切りの表を引くときに使います。

0 → ランク外 / 100 → C / 200 → B / 300 → A

=XLOOKUP(150,D1:D4,E1:E4,"-",-1)   → C     150 以下で最大は 100

6つめの -1 は、同じキーが複数あるときに最後のものを取りたい場合に使います。履歴表から最新の1件を取り出す、といった場面です。

つまずきポイント3: 範囲の大きさをそろえる

検索範囲と戻り範囲は、同じ行数にしてください。

=XLOOKUP("A02",A2:A5,B2:B3,"-")   → #VALUE!

ずれていると行の対応が取れないため、エラーになります。片方だけ行を足してしまう間違いが多いので、開始行と終了行を目で確かめてください。

使えるバージョンに注意

XLOOKUP は Microsoft 365 と Excel 2021 以降で使えます。Excel 2019 以前では #NAME? になります。

配布するファイルで使うときは、相手の環境を確認してください。社内で古いバージョンが混ざっている場合は、VLOOKUP のままにしておくほうが無難です。自分だけで使うファイルなら、XLOOKUP に寄せていくと保守が楽になります。

関連する関数

関数 用途
VLOOKUP 従来の検索関数。古い環境でも動く
INDEX / MATCH XLOOKUP と同じことを2つの関数で行う。古い環境での代替
IFERROR エラー全般を置き換える。XLOOKUP なら4つめの引数で足りる
HLOOKUP 横方向に検索する。XLOOKUP は縦横どちらも扱える

次に進むには

新しく式を書くなら XLOOKUP を選んで構いません。列番号を数えないぶん、あとから表を触っても壊れにくいのが一番の利点です。

古い環境に配るファイルを扱うなら、VLOOKUP も引き続き読めるようにしておいてください。既存のファイルを引き継いだときに、式を読む場面が必ず出てきます。