VLOOKUP関数の構文は、次のようになっていますね。
= VLOOKUP (検索値, 検索範囲, 戻り値の列番号, 検索条件)
今日は、この構文の最後の引数、「検索条件」のお話です。
「検索条件」は、「TRUE(近似一致)」または「FALSE(完全一致)」で指定します。
この記事で扱っている内容
検索条件TRUEの動作とはどんなものか?
検索条件TRUEの場合の動作について、一般にはこのように説明されます。
TRUE を指定するか省略した場合、近似値が返されます。 つまり、完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。
この「TRUE」を使いこなすことで、効率化に貢献する場合があります。ただし、実際に「TRUE(近似一致)」を指定したら、どういう動作をするのか?? 、また省略した場合はどう動作するのか? ということについて、正確に知った上で使わないと、期待した通りの結果が得られません。
検索するのが数値の場合と文字列の場合の違いは?
検索範囲が数値の場合は多くのサイトで解説されていますが、検索範囲が文字列の場合はほとんど解説がありません。
本記事では、この「文字列の場合」も詳しく比較しています。
検索範囲は「昇順」でなければならない。その理由とは?
また、VLOOKUP関数の仕様上、「検索範囲は昇順に並べかえて下さい」となっていますが、はたして昇順になっていない場合は、どんな動作をするのでしょうか?
この記事では、それを実際に検証してみたいと思います。
※検索条件を省略した場合は?
Excelの仕様では、この「検索条件」を省略した場合は「TRUE」(近似一致)となる、ということですが、実は、次の二通りの記述で動作が異なります。
- = VLOOKUP (検索値, 検索範囲, 戻り値の列番号, ) —- 「FALSE(完全一致)」となる(省略していないとみなされる)
- = VLOOKUP (検索値, 検索範囲, 戻り値の列番号) —- 「TRUE(近似一致)」となる(省略しているとみなされる)
違うのは最後の「, (コンマ)」一個だけです。
「, (コンマ)」があることで、省略していないとみなされ、空白値「ゼロ」があるとみなされます。「ゼロ」は「FALSE」と同義ですので、このような動作になります。
新しいXLOOKUP関数についての記事を公開しました
時はうつって、ただいま2023年2月です。
VLOOKUP/HLOOKUP関数を置き換える、と言われる「XLOOKUP関数」についての記事を、新しく公開しました。
XLOOKUP関数の近似一致についての記事のほか、全体的に刷新された点、応用例などを掲載しております。よろしければ参考にご覧ください。
「近似一致」と「完全一致」の動作を詳しく比較する(数値の場合)
次に、両者の動作をくわしく比較し、「近似」とは実際にどんな意味なのかを検証します。まず、数値の場合です。
B2, E2セルの数式はそれぞれ、次の通り入力されています。
- 近似一致: =VLOOKUP(A2,Sheet2!$A$1:$B$3,2,TRUE)
- 完全一致: =VLOOKUP(D2,Sheet2!$A$1:$B$3,2,FALSE)
検索値「以下」の最大値が戻り値となる
数値に対して近似一致を指定した場合、「検索値以下の最大値」が戻り値となります。
※解説書には「検索値未満の最大値」と書いてあるけど?
次のような表現がされている解説書が多いです。
「完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。」(Officeサポートサイト「HLOOKUP関数」より)
ここに「検索値未満」の言葉がありますが、「完全に一致する値が見つか」った場合は、その一致した値が戻りますので、
通して意味を考えれば、結局「検索値以下の最大値」が正しいのです。
前半の「完全に一致する値が見つからない場合は」を抜かして、単に「検索値未満の最大値」と言うと、これは誤りになります。
検索範囲が「昇順でない」の場合の動作
では、こんどは、このシートの検索範囲を、上下逆に(つまり降順に)配置してみましょう。
「完全一致」の結果はさきほどと全く同じですが、「近似一致」のほうは、なんともいいがたい、不可解な結果が出ます。
興味のある方は、「なぜこんな戻り値になるのだろう?? どういう検索の仕方をしているのかな・・・」と考えてみるのも良いですが、これは簡単に理解できる結果ではありません。
VLOOKUP関数は、検索範囲が昇順(小さい順)にきちんと並んでいないと、不可解な、意味のない結果を返す、ということがわかります。
参考: どうやら「二分探索(バイナリサーチ)」と呼ばれるアルゴリズムで検索しているようです。単純に「上から順にみている」といったものでは全くありません。 →Wikipedia 「二分探索」
VLOOKUPで、数値データを「近似一致」で検索した場合の動作、結論。
結論としては、
検索範囲を正確に昇順に整列した条件のもとでのみ、検索値を超えない範囲の最大値を返す。
検索範囲が昇順でない場合は、確実な動作が見込めません。
正確に動作させるには、念のため、検索範囲の方を昇順で並べ替えておくとよいでしょう。
また、「検索値を超えない範囲の最大値」を戻り値とするため、実際上は「切り捨て」と同様の意味になります。もしこれを「四捨五入」にしたい場合には、
=VLOOKUP(ROUND(A2),Sheet2!$A$1:$B$3,2,TRUE)
のように、あらかじめ検索値を四捨五入しておくとよいでしょう。
2023年追記: 新しいXLOOKUP関数の近似一致の動作
Office2021では、VLOOKUP, HLOOKUP関数を置き換える新関数として「XLOOKUP関数」が追加されました。
XLOOKUP関数には、「一致モード」と「検索モード」という、２つの引数があります。
[一致モード](オプション)
一致の種類を指定します:
0 – 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。
-1 – 完全一致。 見つからない場合は、次の小さなアイテムが返されます。
1 – 完全一致。 見つからない場合は、次の大きなアイテムが返されます。
2 – *、?、および 〜 が特別な意味を持つワイルドカードの一致。
Microsoft Support 「XLOOKUP関数」
[検索モード](オプション)
使用する検索モードを指定します。
1 – 先頭の項目から検索を実行します。 これが既定の設定です。
-1 – 末尾の項目から逆方向に検索を実行します。
2 – 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。
-2 – 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。
Microsoft Support 「XLOOKUP関数」
詳しくは、別記事を製作中ですが、取り急ぎ
- 一致モード=1(見つからない場合は次の大きなアイテム)
- 検索モード= 1 / -1 / 2
での検索を試してみました。
左側の運賃表は、わざと昇順に並べ替えず、でたらめな順番にしてあります。
XLOOKUP関数の、検索モード１、検索モード-1 ともに、昇順になっていないにも関わらず、正しい運賃を検索できました。
モード2、つまり「バイナリ検索」は、高速なんだそうですが、昇順になっていない場合には、VLOOKUP関数の近似一致と同じように、正しい結果を出せませんでした。
「近似一致」と「完全一致」の動作を詳しく比較する(文字列の場合)
次に、文字列で検索する場合を見てみましょう。
文字列の場合は、近似一致の結果は、前方一致のようです。
ただし、
「検索値→範囲」 ではなく、
「範囲の各値→検索値」の前方一致
ですね。
- 「1こ」→N/A
- 「1こめの次」→1
- 「1こめ半だ」→1.5
複数が一致する場合はできるだけ多くの文字が一致するものが返っているようです。
「1こめ半」→1も1.5も一致するが1.5が返っている
「2こめちょい」→2のみ一致するため2が返っている
「2こめとちょい」→2も2.3も一致するが2.3が返っている
この項は、2度改訂しております。経緯を簡単に記し、ご参考に供させていただきます。
初稿→サンプルに「2こめとちょい」がない状態→結論は現在と同じでした。
第2稿→「2こめ」の下に「2ことちょい」を追加したところ、動作が不可解となったため、いったん「文字列では使えない」という結論に変えましたが
第3稿(現在)→読者様のご指摘により、「2ことちょい」は、昇順だと「2こめ」より前なのに、誤って後に置いていたことから「昇順で並べ替え」のルールに反して、動作が不可解になっていたことが判明。「2ことちょい」→「2こめとちょい」に直したところ、やはり昇順になっていれば前方一致となりました。
VLOOKUPで、文字列データを「近似一致」で検索した場合の動作、結論。
結論としては、
文字列に対してVLOOKUPを使用した場合、近似値で検索すると「前方一致」となる。
ただし、検索値→範囲の各値の前方一致ではなく、範囲の各値→検索値 の前方一致となる
※文字列の場合も、範囲が昇順に並べ替えられていない場合は、まったく意味のない結果を返します。
近似一致で適用できない場合は、他の関数と併用しよう
以上のような条件にあてはまるようなケースでは、VLOOKUP関数の「近似一致」を使うことができます。ぶぶん
しかし、特に文字列による検索の場合などは、いま検証したような動作では使えない場合がとても多いと思います。
そのような場合には、LOOKUP関数、MATCH関数、INDEX関数、OFFSET関数などと組み合わせて、あらかじめ検索値または検索範囲を変化させ、「完全一致」のVLOOKUP関数に落とし込むことで、思い通りの検索を行うことができます。
このような例については、いずれ別の記事で書いていきたいと思います。
コメント
いつも勉強させて頂いてます。
vlookup関数の近似値は理解できました。
交通費参照に利用しております。
しかし、うまくできないことがあります。
例えば
「徒歩」、距離が3000メートル以下の場合、交通費補助500円、
「早歩き」、距離が5000メートル以下の場合、交通費補助700円、
「走る」、が7000メートル以下の場合、交通費補助900円、
の場合、テーブルをつくり、vlookupで第三引数をtrueで検索できるのですが、
ここに、もう１つ条件をつけた場合がうまくできないです。
その条件は
「徒歩」が「自転車」の場合で距離が距離が3000メートル以下の場合も500円
を加える場合がうまくいきません。
=if(countif(範囲、検索条件）>=1,if(参照セル)=<3000,交通費補助500円,vlookup(距離、表、列、true))
範囲が「徒歩、早歩き、走る、、、」
検索条件が「自転車」
（参照セル）が距離
表は
0 ～3000m 500円
501 ～5000m 700円
701 ～7000m 900円
901 ～ 1200円
みたいに考えたのですが、、、、です。
どうぞご教授お願い致します。
コメントありがとうございます。少し条件を確認させていただきたいのですが
■交通手段が「徒歩」「早歩き」「走る」「自転車」の4種類あるのでしょうか?
■距離別の補助金額表は、交通手段を問わず一種類のように見えますが、違うでしょうか? 交通手段によって、同じ距離でも補助金額は違うのでしょうか?
つまり、徒歩でも自転車でも、3000メートル以下は500円で同じである、というように見えますが、何かたぶん私が読み取れていない条件がありますね?
例えば、交通手段によってすべて、補助金額の体系が異なる、と言う場合には、交通手段別に4種類の参照範囲を用意し、IF()で切り替えていくのがスムーズだと思うのですが、いかがでしょうか?
ご教授ありがとうございます。
アドバイス通り、
if関数で切り替え（入れ子、入れ子）にしたら、なんかできました。
いつも、countifの引数やif関数の引数設定が上手くいかず、めげます。
今回はアドバイス頂いたこともあったのか、うまくできました。
ありがとうございました。
うまくいったのですね、よかったです!
とても長くて複雑な式になると思うのですが、最新のExcelですと、LAMBDA関数を使ってカスタム関数の中にその複雑なものを入れ込んで、ワークシートの方はシンプルに記述することもできるようになりました。
ご興味わくようでしたら、ぜひおためしください。
初めてメールさせていただきます。
ブログ拝見して参考にさせていただいております。
vlookupのtureについて実質検索値以下との見解とてもよくわかりました。スッキリしました!
ありがとうございました。
ところでテキストにvlookupのtureについては検索値未満と記載されているのですがmatchの検索方法でも1 には検索値以下と記載されているのですが明確な理由があるのでしょうか？
初めてのメールで質問するのは失礼かと思いましたが機会があればブログでもご説明いただければと思います。
ブログの更新楽しみにしています。
コメントありがとうございます。そして、細かいところまで読んでいただいてありがとうございます。
(記事中のHLOOKUP関数の解説のところ、リンクが何か変わっており矛盾しておりましたので修正しました。)
「見つからない場合は検索値未満」と「検索値以下」の両表現が混在しているのは、確かに不可思議なのですが、もしかしたら、もとの解説が英文でそのようになっており、翻訳した表現がそのまま残っているだけなのかもしれないですね。
HLOOKUP function | Microsoft Support
if an exact match is not found, the next largest value that is less than lookup_value is returned
多くの企業で365への転換が進んでまして、時代はXLOOKUPになっていくのかもしれません。XLOOKUP関数の解説には、またなにか違う独特の表現が使われています。そのうちこれも追記しないといけませんね。
XLOOKUP 関数 | マイクロソフトサポート
コメントそしてご指摘ありがとうございます。
まったく初歩的な確認ミスでお恥ずかしい限りでございます。文字列の場合のサンプルを再確認したところ、11/30で「2こめとちょい」としたところが「2ことちょい」となっており、昇順に並べ替えした場合は「2こめ」よりれ上にあるべきでした。
この結果、「2ことちょい」の部分が不可解な結果となり、全体として「文字列の場合は使えない」という結論となっておりましたが、
「2こめとちょい」に変えたところ、11/30改訂版のように「前方一致」の結果となりました。
VLOOKUP関数は、一般に「範囲を昇順に並べ替える必要がある」ということは常識の範囲なのですが、初めて学ぶ方もそうでない方も含めて、「では、昇順でなければ一体どうなってしまうのか」を見たことがない方が多数いらっしゃいます。
本記事は、そういった方むけに、「実際にやってみる」ことによって、並べ替えの必要性を理解しよう、という主旨で書いてみたものです。
はからずも、執筆側のミスにより、ますますその必要性がはっきりした・・・という展開でございました。
今回、この趣旨が分かりにくい文脈になっていることも修整させていただきました。
ご指摘ありがとうございました。また検索で見かけられた際には、当ブログをよろしくお願いいたします。
GoogleでVLOOKUP,TRUEを検索したところ、こちらのサイトが１番にヒットしたので立ち寄りました。
VLOOKUPの動作に誤解があるようでしたのでコメントを残しておきますね。
記事中でVLOOKUPの返り値がおかしくみえるところは、検索キーがきちんと昇順になっていないせいで、理解し難い挙動になっています。
たとえば、
『最も不可解なのは「2ことちょい」(近似一致)→1.5』
とありますが、これは検索対象(A列)が昇順（アイウエオ順）になっていないからです。
「2こ“め”」と「2こ“と”ちょい」を比べると、タ行の“と”の方がマ行の“め”より先ですよね。
「1こめ半」(1.5) ＜ 「2ことちょい」 ＜ 「2こめ」(2) なのでここは1.5が返ります。
他の所についても
•「2こ」→1.5なのも同様に 「1こめ半」(1.5) ＜ 「2こ」 ＜ 「2こめ」(2) です。
•「1こ」→N/Aになるのは、 「1こ」＜「１こめ」 で検索にヒットしないからですね。
検索値と検索対象を昇順で並べてみると理解しやすくなると思います。
なお、「2ことちょい」は辞書順でややこしい位置に来るので「2こめとちょい」に改めました。
カンマ区切りで見にくいでしょうから、ExcelにCSV形式で読み込ませてみてください。
検索対象, 値
1こめ, 1
1こめ半, 1.5
2こめ, 2
2こめとちょい, 2.3
2こめ半, 2.5
3こめ, 3
検索値, TRUEで検索, FALSEで検索
(1こめ), #N/A, #N/A
(1こめ半), #N/A, #N/A
1こ, #N/A, #N/A
1こめ, 1, 1
1こめの次,1 , #N/A
1こめ半, 1.5, 1.5
1こめ半だ, 1.5, #N/A
2こ, 1.5, #N/A
2こめ, 2, 2
2こめとちょい, 2.3, 2.3
2こめ半, 2.5, 2.5
2こめ半だ, 2.5, #N/A
3こめ, 3, 3