VLOOKUP関数について②

前回は VLOOKUP の検索方法を FALSE(完全一致) に絞って学びました。

今回は、4つ目の引数を TRUE(近似一致) にしたケースを見ていきます。「ぴったり一致しなくても、近い値を見つけてくる」という、少し変わった動きです。

最初は不思議に思える機能ですが、これがピタリとはまる場面があります。代表的なのが、ポイントカードのランク判定 のような「範囲で値が変わる」ケースです。

TRUE はどんなときに使うのか

「○○以上なら△△、××以上なら□□」のような 段階的なルール を表現したいとき、TRUE が活躍します。

たとえば、お店のポイントカードでこんなランクがあるとします。

10ポイント以上 → ドリンク1杯無料
30ポイント以上 → 1,000円割引券
50ポイント以上 → スイーツプレート
100ポイント以上 → コース料理1回

ある会員さんが 15ポイント 持っているとき、受けられるサービスは何でしょうか。

15ポイントは「10ポイント以上、でも30ポイントには届いていない」状態。だから受けられるのは「ドリンク1杯無料」です。

「ぴったり15ポイント」というランクは表にありませんが、「15を超えない最大のランク(＝10ポイントの行)」が拾われる、というのが TRUE の動きです。

VLOOKUP関数の構文(おさらい)

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

引数は前回と同じ。違いは最後の 検索方法を TRUE にする ところだけです。

具体例で見てみる

ポイントランク表を作ります。

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

A列にポイント数、B列に受けられるサービス。これが「ポイントランク表」です。

ここで、ある会員が 15ポイント 持っているとき、受けられるサービスを取り出してみます。

=VLOOKUP(15, A:B, 2, TRUE)

検索値:15
範囲:A:B(ランク表)
列番号:2(サービス名)
検索方法:TRUE(近似一致)

結果は「ドリンク無料」が返ります。

ぴったり「15」という行はランク表にありませんが、「15を超えない、最大のランク」＝10ポイントの行 が拾われた、というわけです。

TRUE の動きを丁寧に追う

「15ポイントの会員さん」の場合を、もう少し丁寧に追ってみます。

ランク表を上から順に見ていって、

ステップ1:0 ポイント以上 → サービスなし(15は0以上なので候補)
ステップ2:10 ポイント以上 → ドリンク無料(15は10以上なので候補に更新)
ステップ3:30 ポイント以上 → 1000円割引券(15は30以上ではない)

「30以上を満たさなかった」ので、1つ前の候補(10の行) で確定。結果は「ドリンク無料」になります。

つまり、「検索値を超えない最大の値の行」を返す のが TRUE の動きです。

TRUE を使うときの大原則

TRUE で VLOOKUP を使うとき、絶対に守らないといけないルール があります。

ランク表の左端の列(範囲の1列目)は、必ず「昇順(小さい順)」に並べておくこと。

先ほどのランク表は、0 → 10 → 30 → 50 → 100 と、ポイント数が小さい順に並んでいました。これが大事です。

もし順番がバラバラだと、TRUE の検索結果が おかしくなる ことがあります。Excel は「順番に並んでいる」前提で、上から効率的に探すので、並んでいないと「ここまで来たから打ち切り」と勘違いしてしまうのです。

FALSE(完全一致)のときは、表の並びはどうでもよかったのですが、TRUE のときは並び順が命 です。ここはしっかり覚えてください。

検索値はセル参照にする

実務では、検索値を直接書かず、セル参照 にします。

たとえば、

C5 に「会員のポイント数」を入力
D5 に「受けられるサービス」を表示

C5 にポイント数を入れたら、D5 にサービスが自動で出る、という形。

D5 のセル: =VLOOKUP(C5, マスタ範囲, 列番号, TRUE)

D2 を 15 にすれば「ドリンク無料」、45 にすれば「1000円割引券」、120 にすれば「コース料理」。会員ごとのポイントを並べた表を作って、D列にこの数式を入れておけば、それぞれが何のサービスを受けられるか が一目で分かります。

なお、TRUE(近似一致)で検索するときは、検索値も比較対象(範囲の左端)も、基本的には数値 になります。今回のような「ポイント数」「点数」「金額」など、大小が比較できるデータで使う関数だと考えてください。

FALSE と TRUE の使い分け

ここまで学んだ VLOOKUP の2つの顔をまとめると、こうなります。

検索方法	動き	使うシーン
FALSE	完全一致	商品コード→商品名、社員番号→部署など、ぴったり一致で引く場合
TRUE	近似一致	ポイントランク、価格帯別の割引率など、範囲で結果が変わる場合
TRUE の前提	範囲の左端は昇順	並んでいないと誤動作

「ぴったり探したい → FALSE」「範囲で当てはめたい → TRUE」と覚えてください。

実務での頻度で言うと、FALSE のほうが圧倒的に多い です。

TRUE は「ポイントランク」「成績評価」「価格帯別の手数料」など、用途は限られますが、ハマる場面では本当に便利です。

まとめ

VLOOKUP の検索方法 TRUE は近似一致
検索値を超えない最大の値 の行が返る
ポイントランクなど 段階的なルール に最適
ランク表の左端は 必ず昇順に並べておく こと
検索値はセル参照にして、会員ごとに自動判定が定番

次回は、VLOOKUP の 横向き版 ともいえる HLOOKUP関数 を扱います。表が横に並んでいるときに使う関数です。

ショートカット

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