[エクセル中級] VLOOKUPの不便を解消！VLOOKUP以外で検索

VLOOKUP便利ですよね！

何かを検索して表示したいなんてこと、たくさんあると思います。

例えば、都道府県名から地方を表示する場合。

でもVLOOKUPにも不便なところがあると思います。今回はVLOOKUPをより便利にした数式を紹介します。

VLOOKUPの不便なところ

先頭列しか検索できない

VLOOKUPは左端からしか検索できません。（横方向に検索するHLOOKUPでは一番上の行のみ）

今回は都道府県の 右に 地方が書いてあるのでいいですが、これが逆になっていると検索できませんね。

結果の列番号を指定しないといけない

VLOOKUPの検索結果は、範囲指定した中の何列目を表示するかで規定します。この例では、2列目に「地方」が書いてあるので列番号に「2」と指定しています。

ここでA列とB列の間に1列挿入すると、範囲は「A1:C8」と更新されますが、列番号は「2」のままなので表示できなくなってしまいます。

この問題そのものの対処法としては、列番号を「COLUMN(B1)-COLUMN(A1)+1」などと参照指定すれば解決できますが、毎回やると大変な上に、式が煩雑になって分かりにくくなります。

融通の利くVLOOKUP

そこで、VLOOKUPの機能を「検索」と「対応値の表示」に分割して、それぞれ別の関数に置き換えると《融通の利くVLOOKUP》に変身させられます！

MATCH関数で検索！

まず、MATCH関数で該当する都道府県（ここでは東京）が何番目にあるのかを検索します。

MATCH関数はVLOOKUP関数のうち、「左端から検索をする」部分のみを担ってくれます。

VLOOKUP関数では以下のいずれかを［検索方法］で指定します。

• 完全一致
• 昇順で並んでいるものから検索値以下の最大数

MATCH関数では、1つ増えて、逆順での検索も可能になっています（［照合の種類］で指定）。

• 完全一致
• 昇順で並んでいるものから検索値以下の最大数
• 降順で並んでいるものから検索値以上の最小数

上から3番目に「東京」が見つかりました。

INDEX関数

次に「3番目がどこの地方か」を表示するためにINDEX関数を使います。

INDEX関数では、配列（セルの範囲）を指定し、その中から［行番号］番目のデータを返します。今回は、［行番号］にMATCH関数の結果を入れることで、検索結果に対応する場所を指定します。

ここでは、B1:B8 の中で、（MATCH関数で東京を検索した）「3」行目なので、「関東」が結果になります。

まとめ

VLOOKUP関数の「検索」をMATCH関数、「対応データの参照」をINDEX関数に置き換えることで、参照先の指定を分離し、列が入れ替わったり、列の数が変わっても変更が必要ないようにできました。

副次的に、完全一致でない検索での逆順検索（降順の中で検索値以上の最小数）もできるようになりました。

 - Excel
 - INDEX, MATCH, VLOOKUP, エクセル中級, 検索