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

      2016/07/01

VLOOKUP便利ですよね!

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

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

1

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

SPONSORED LINK

VLOOKUPの不便なところ

先頭列しか検索できない

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

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

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

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

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

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

SPONSORED LINK

融通の利くVLOOKUP

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

MATCH関数で検索!

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

2

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

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

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

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

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

3

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

INDEX関数

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

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

4

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

5

まとめ

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

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

itjo レスポンシブ 本文下

 - Excel , , , ,