LEVEL 5 ｜ データを整理・抽出する ｜ 上級
Lesson 052

XLOOKUP関数について②

練習用Excelファイル lesson052-practice.xlsx

練習用ファイルは、パソコンからダウンロードしてください。

XLOOKUP関数について②

前回のレッスン051で、XLOOKUP の基本(3引数＋N/A対策の第4引数)を学びました。

今回は、XLOOKUP のもう一歩進んだ使い方を見ていきます。

  • 第5引数:VLOOKUP の TRUE に相当する、近似一致の拡張版
  • HLOOKUP の代わり にも使える(X は どっち向きでもいける)

XLOOKUP の「X」は eXchange(交換) や eXtended(拡張) の意味合いがあると言われています。

文字通り、V / H を1つにまとめた拡張版、という位置づけです。

第5引数:近似一致のモード指定

XLOOKUP の構文をフルで書くと、こうなります。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからないときの値, 一致モード)

5つ目の引数「一致モード」で、検索の仕方を切り替えられます。

値意味
0(または省略)完全一致(デフォルト)
-1完全一致 or 次に小さい値
1完全一致 or 次に大きい値
2ワイルドカード一致

VLOOKUP の TRUE が「超えない最大の値」だったのを覚えていますか？　あれは XLOOKUP では -1(次に小さい値)に相当します。

ポイントランクで試してみる

レッスン049で扱った「ポイントカードのランク判定」を、XLOOKUP で書いてみます。

:::excel
A1=ポイント
B1=サービス
A2=0
B2=サービスなし
A3=10
B3=ドリンク無料
A4=30
B4=1000円割引券
A5=50
B5=スイーツプレート
A6=100
B6=コース料理
:::

15ポイントの会員が受けられるサービスを取り出します。

=XLOOKUP(15, A:A, B:B, "該当なし", -1)

  • 検索値:15
  • 検索範囲:A列(ポイント数)
  • 戻り範囲:B列(サービス)
  • 見つからないときの値:"該当なし"
  • 一致モード:-1(次に小さい値)

結果:ドリンク無料

VLOOKUP の TRUE で書いたときと同じ結果になります。

VLOOKUP の TRUE と比べる

同じ判定を、両方で書き比べてみます。

=VLOOKUP(15, A:B, 2, TRUE)
=XLOOKUP(15, A:A, B:B, "該当なし", -1)

XLOOKUP のほうが、

  • 検索範囲と戻り範囲を 分けて 指定できる
  • 見つからないときの フォールバック を含められる
  • 一致モードを -1 / 1 で 意図的に 切り替えられる

VLOOKUP の TRUE は「次に小さい値」一択でしたが、XLOOKUP では 「次に大きい値」も指定できる ところが拡張ポイントです。

たとえば「目標金額をぴったり満たすか、それを超える最小の価格帯を返したい」のような、下から探していくケースでは「1」が役立ちます。

実務での出番は限定的ですが、こういう柔軟性があると知っておくと、いざというときに便利です。

XLOOKUP は HLOOKUP も兼ねる

ここがもう一つの目玉です。

VLOOKUP は「縦向きの表」しか使えず、横向きには HLOOKUP を別に覚える必要がありました。

XLOOKUP は、検索範囲と戻り範囲が「列」なら縦向き、「行」なら横向き として、勝手に動いてくれます。関数を使い分ける必要がない のです。

レッスン050で扱った「月別目標」の横向き表で確認してみましょう。

:::excel
A1=月
B1=1月
C1=2月
D1=3月
A2=目標
B2=100000
C2=120000
D2=150000
:::

「3月の目標は?」を XLOOKUP で取り出します。

=XLOOKUP("3月", B1:D1, B2:D2)

  • 検索値:"3月"
  • 検索範囲:B1:D1(行で並んだ月の見出し)
  • 戻り範囲:B2:D2(行で並んだ目標額)

結果:150000

検索範囲も戻り範囲も「行」を指定しただけ。HLOOKUP のときに必要だった「行番号は上から何番目か……」と数える必要がありません。

VLOOKUP / HLOOKUP では関数を使い分ける必要があったのが、XLOOKUP では 同じ関数のまま、範囲を縦にするか横にするかだけで切り替わる ── これが「X」の真骨頂です。

まとめると、XLOOKUP は何でもできる

ここまでで分かるように、XLOOKUP 1つで、

  • VLOOKUP の FALSE(完全一致)
  • VLOOKUP の TRUE(近似一致、しかも拡張)
  • HLOOKUP の縦横切り替え
  • IFERROR との組み合わせ

これらが 全部1つにまとまっている わけです。

「じゃあもう VLOOKUP / HLOOKUP は要らないの？」と思われるかもしれません。

新規で数式を書くなら、その通りです。実務では XLOOKUP に統一 してOK。VLOOKUP / HLOOKUP は、「過去に作られた既存ファイルを読むため」「古い Excel との互換性のため」 に知っておく、というのが現実的な使い分けです。

第6引数(検索アルゴリズム)について

XLOOKUP には、実は 6つ目の引数「検索モード」もあります。これは「検索のアルゴリズム」(上から探すか、下から探すか、二分探索を使うか)を切り替えるための引数です。

ただ、普通の業務ではまず触ることがありません。デフォルトの動きで困ったことが、ほとんどないからです。「そんな引数もあるんだな」と頭の片隅に置いておけば十分です。

本レッスンでは扱いません。

まとめ

  • XLOOKUP の 第5引数 で一致モードを切り替え
  • 0 ＝完全一致(デフォルト)／ -1 ＝次に小さい値／ 1 ＝次に大きい値／ 2 ＝ワイルドカード
  • VLOOKUP の TRUE は XLOOKUP の -1 に相当
  • 検索範囲と戻り範囲を「行」で指定すれば HLOOKUP の代わり にもなる
  • XLOOKUP 1つで V/H LOOKUP の機能をすべてカバー
  • 第6引数(検索モード)は通常触らなくてOK

XLOOKUP の基本と応用、これで一通り終わりました。列番号を数える呪縛から解放されて、検索系の関数がぐっと書きやすくなった ── 関数も時代を追うごとに進化している、ということですね。

ショートカット

※このレッスンに対応するショートカットはありません。

次のLessonへ Lesson 053 MATCH関数について →