【スプレッドシート】VLOOKUP|いっぺんに複数の列指定をする方法


Vlookup関数と言えば、検索ウィンドウを手軽にスプレッドシートに挿入できる、便利な関数ですね。

一方、アウトプット列の指定は1つしかできないので、次のケースでは困ってしまいます…

答えを見る

B14 = ArrayFormula(VLOOKUP($A$14,$A$1:$E$6,{2,3,4,5},0))
全部いっぺんに取得したいけど、列指定は1列しかできないからなぁ….

もしB〜E列を、一度に列指定&アウトプットできたら…

このファイルはこちら

すごく便利ですね。

これ、ArrayFormula関数と配列を組み合わせることで、簡単に実現できます。


Step # 1:どうして通常、列指定は1つしかできないの?


Vlookup関数で、アウトプット範囲の列指定は、複合範囲の中で1列のみと定まっています。よって、アウトプット範囲が複数列にまたがっている場合、従来は列ごとにVlookupする必要がありました。


  1.  Vlookup関数のつくりかた
  2. = Vlookup(① 検索値, ② 複合範囲, ③ アウトプット範囲の列番, ④ True or False)

    詳細をみる

     ① 検索する値・数・なまえなど
    空欄セルのセル番号をこの検索値に指定することで、相手に探したい情報をそこに直接入力させることが出来、便利です。(本例)

     ② 検索範囲(左端)から、アウトプット範囲の列までの範囲
    もし、検索範囲とアウトプット範囲が同一の場合はただの1列。③を「1」にセットする。

     ③ 複合範囲中、アウトプット範囲となる列の番号
    正の整数で、1列のみ。複合範囲中、1番左の検索範囲を列1として、以右何列目にあるかで指定。

     ④ 検索値を検索範囲で探す時の、ルール
    False(0も可)は、「完全一致」する値を探し、True(1も可)は、「最も近い数値」を探します。




  3.  Vlookup関数をつかった例
  4.  E2 = Vlookup(D2,$A$2:$B$6,2,0)
    アウトプットする列が1つなら、列番(= 2)の指定も簡単なのに..
    次の方法を使えば、複数列の列番指定も簡単ですよ。

Step # 2:複数のアウトプット範囲を列指定する方法

  1.  { } 「中括弧(なかかっこ)」で、複数列を1列だと思わせる
  2. { } に複数列を内包することで、アコーディオンカーテンのように、「まとめると1列でも、よこに列を複数広げれる」、つまり複数列を1列だと思わせることができるんです。

    これを「配列」といいます。

    この方法を使えば、VLOOKUP関数の「列指定は1つだけルール」を騙すことができますね。


  3.   { }  をつかった例をみる
  4.  A1 = {1,2,3,4}

    Step # 3:ArrayFormula関数と{}で複数の列を指定【完成】

    1.  ArrayFormula関数は「一気処理のサイン」
    2. 従来個別に処理していた事例が、本例のように配列{}や拡大された範囲指定で一度で済まされる場合、ArrayFormulaを先頭につけることによって「今回は一度に処理するよ」という合図を関数全体に送る必要があります。

      入力はショートカットが便利です。

      Windows Ctrl + Shift + Enter  | Mac  + Shift+ Enter


    3.  実際につかってみる

    4. 以下のArrayFormula関数をB10セルに入力してみましょう。

      B10 = ArrayFormula(VLOOKUP($A$10, $A$2:$E$6,{2,3,4,5},0))

      A10が①検索値
      A2:E6が②複合範囲
      {2,3,4,5}が③アウトプット範囲の列番   となります。

      注意
      今回は、$A$10, $A$2:$E$6のように絶対参照を使用していますが、これは範囲中の中身が削除されても、対象範囲をA・E列の2:6間、検索値の入力をA10で固定する為です。

      これで、目標としていた「複数列をアウトプット範囲に指定して、Vlookupを一度で終わらせる」方法の完成です。

      お疲れ様でした。