[エクセル中級] VLOOKUPの不便を解消！VLOOKUP以外で検索
2018/04/07
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関数に置き換えることで、参照先の指定を分離し、列が入れ替わったり、列の数が変わっても変更が必要ないようにできました。
副次的に、完全一致でない検索での逆順検索（降順の中で検索値以上の最小数）もできるようになりました。