Excel VLOOKUP 参照表がバラバラな時の対処法

Excel VLOOKUP 参照表がバラバラな時の対処法 Excel

本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！

はじめに：実務あるある！散らかった参照表との戦い

「このデータ、参照する表があっちにもこっちにもあって、集計が大変…」

そう、Excelの実務では、データがいつも綺麗に整っているとは限りませんよね。

今日のテーマは、まさにそんな「あるある」な悩みです。

シートのあちこちに、会社の料金表が散らばっている…。しかも、プランの並び順もバラバラ…。

Excel説明画像

「え、こんなカオスな状態から、どうやって正確な料金をVLOOKUPするの…？」

そんな絶望的な状況を、スマートな数式一本で解決する冒険に、今から皆さんをご招待します！

今回は、この難題を解決するための6つの異なるアプローチを紹介します。

実務で輝く王道テクニックから、あなたのExcel脳を刺激するマニアックな関数パズルまで、盛りだだくさんですよ！

【今回のルール】
この記事では、数式の「ロジック」そのものを楽しむことを最優先します！
そのため、エラー処理（指定した社名やプランが存在しない場合の処理など）は一切考慮しません。
あくまでも「正しく入力すれば、こう動く」という、関数の純粋な動きにフォーカスしていきますね！

さあ、あなたのExcelスキルを一段階レベルアップさせる、楽しい冒険に出発しましょう！

以下の数式を任意のセルに貼り付けると、上記の表を表示できます。

={"A社","","","","","","";"プラン1",450,"","","","","";"プラン2",550,"","","C社","","";"プラン3",780,"","","プラン3",1800,"";"","","","","プラン2",1500,"";"","B社","","","プラン1",1020,"";"","プラン2",1000,"","","","";"","プラン3",1200,"","","D社","";"","プラン1",680,"","","プラン2",1800;"","","","","","プラン1",1224;"","","","","","プラン3",2160}

ステップ1：冒険の準備 – 魔法の杖「名前の定義」を手に入れよう

いきなりですが、複雑な問題に立ち向かうとき、強力な武器があるのとないのとでは大違いです。

今回の冒険で私たちが手にする最強の武器、それは「名前の定義（ネームマネージャー）」です！

これは、セル範囲に分かりやすい「名前」をつける機能のこと。

A1:B4 のような無機質な番地ではなく、「A社」のような直感的な名前で範囲を扱えるようになる、便利な機能です。

Web版Excelでは、上部のリボンから「数式」タブ → 「ネームマネージャー」を選択します。

Excel説明画像

表示された画面で「+ 新規」をクリックし、以下の5つの名前を定義してください。

※シート名は適宜変えてください。

  • A社: =SAMPLE!$A$1:$B$4
  • B社: =SAMPLE!$B$6:$C$9
  • C社: =SAMPLE!$E$3:$F$6
  • D社: =SAMPLE!$F$8:$G$11
  • 表: =SAMPLE!$A$1:$G$11
Excel説明画像

これで準備は万端！まるで魔法の杖を手に入れた気分ですよね？

6つのアプローチで、散らかったデータを攻略せよ！

それでは、A13セルに「社名」、A14セルに「プラン」と入力し、B13セルとB14セルに入力された条件に基づいて、

正しい料金をB15セル以下に表示させる数式を組み立てていきましょう！

Excel説明画像

さあ、皆さんならどんな数式を組み立てますか？少しだけ考えてみましょう！

ヒント：準備ステップで定義した「名前」が、大きなカギになりますよ！

アプローチ1：INDIRECT + VLOOKUP（最強の王道コンビ）

考えかた

まず紹介するのは、今回の問題に対する最もスマートで、実務でも強く推奨される解決策です。

VLOOKUP関数を使うには、検索する「範囲」を教えてあげる必要があります。

でも、その範囲は「A社」だったり「B社」だったり、状況によって変わってしまいますよね。

もし、入力された社名に応じて、参照する範囲を動的に切り替えることができれば…。

その「動的な切り替え」を実現する魔法の呪文、それがINDIRECT関数です！

VLOOKUP関数のおさらい

本題に入る前に、VLOOKUP関数の基本を少しだけおさらいしましょう。

VLOOKUP関数は、=VLOOKUP(検索値, 範囲, 列番号, 検索方法)という形で使います。

「範囲」の一番左の列から「検索値」を探し、見つかったら、その行の「列番号」の値を返してくれる関数です。

例えば、こんな電話帳があったとします。

=VLOOKUP("佐藤", A2:B4, 2, FALSE) と入力すれば、A列から「佐藤」を探し出し、その2列目にある「090-4444-5555」を返してくれます。とても便利ですよね！

Excel説明画像

数式と解説

=VLOOKUP(B14,INDIRECT(B13),2,FALSE)

この数式、驚くほどシンプルですが、非常に強力です。分解してみましょう。

・INDIRECT(B13): ここが心臓部です。INDIRECT関数は、セルに入力された文字列を、本物の「セル参照」に変換する、という驚異的な能力を持っています。

B13セルには「B社」という文字列が入っていますよね？

INDIRECT関数は、この文字列を、先ほど私たちが定義した「B社」という名前のセル範囲（B6:C9）に変身させてくれるのです！

Excel説明画像

もし、B13セルを「C社」に変えれば、参照先も自動で「C社」の範囲に切り替わります。

・VLOOKUP(B14, ..., 2, FALSE): あとはおなじみのVLOOKUP関数です。

INDIRECTが作り出した動的な参照範囲（今回は「B社」の範囲）の中から、B14セルの「プラン3」を探し、その範囲の2列目にある料金を返します。

Excel説明画像

メリット＆デメリット

メリット: 数式が非常に短く、可読性が高い。参照表が増えても（名前の定義さえすれば）数式を修正する必要がない。

デメリット: INDIRECT関数は「揮発性関数」と呼ばれ、多用するとシートの再計算が遅くなることがある。（今回のケースでは全く問題ありません）

アプローチ2：IFS + VLOOKUP（堅実な初心者向け）

考えかた

「名前の定義とかINDIRECTとか、ちょっと難しそう…」と感じた方もご安心ください。

もっとシンプルに、IFS関数を使って「もしA社ならこの範囲、もしB社ならこの範囲…」と、一つひとつ条件分岐させる方法もあります。

数式と解説

=VLOOKUP(B14,IFS(B13="A社",A1:B4,B13="B社",B6:C9,B13="C社",E3:F6,B13="D社",F8:G11),2,FALSE)

・IFS(B13="A社",A1:B4, ...): IFS関数は、条件を上から順番に判定していきます。

まず、B13セルの社名をチェックし、一致した会社の料金表の範囲（例えば「B社」ならB6:C9）を、VLOOKUP関数に渡します。

Excel説明画像

・VLOOKUP(B14, ..., 2, FALSE): IFSが返してくれた、まさに今必要な会社の料金表の範囲を、VLOOKUPが検索します。

メリット＆デメリット

メリット: やっていることが非常に直感的で、初心者にも分かりやすい。

デメリット: 参照する会社の数が増えるたびに、数式を長く書き足していく必要がある。メンテナンス性が低い。

アプローチ3：LET + TEXTJOIN + FIND（力技の文字列操作）

考えかた

今度は、発想をガラリと変えましょう。

もし、散らばった表をすべて連結して、一つの「ながーい文字列」にしてしまったら？

あとは、その文字列の中から目的の社名とプラン名を探し出し、その間にある数字を抜き出せばいいはずです！

数式と解説

=LET(s,TEXTJOIN("-",,A1:B4,B6:C9,E3:F6,F8:G11),t,FIND(B14,s,FIND(B13,s)),u,FIND("-",s,t),
VALUE(MID(s,u+1,FIND("-",s,u+1)-u-1)))

数式が長いので、LET関数で処理を分割しています。

・s,TEXTJOIN(...): まず、4つの料金表をTEXTJOIN関数で、ハイフン区切りの一つの文字列にします。この文字列「s」は、A社-プラン1-450-...のような、とてつもなく長いものになります。

Excel説明画像

・t,FIND(B14,s,FIND(B13,s)): 次に、まずFIND(B13,s)で「B社」が文字列「s」の何文字目にあるかを探し、その位置を起点として、さらにFIND(B14,s,...)で「プラン3」を探します。

これでプラン名の開始位置「t」が分かります。

Excel説明画像

・u,FIND("-",s,t): プラン名の開始位置「t」以降で、最初に出てくるハイフンの位置「u」を探します。

Excel説明画像

・VALUE(MID(...)): 最後に、MID関数で、位置「u」の次から、その次に出てくるハイフンまでの間の文字（つまり料金）を抜き出し、VALUE関数で数値に変換します。

Excel説明画像

メリット＆デメリット

メリット: 文字列操作関数の強力さと面白さを体感できる。

デメリット: 実用性は皆無。参照表の構造が少しでも変わると破綻する。

…さて、ここまでの3つの方法は、すべてA社、B社…と個別に範囲を指定する必要がありました。

でも、それってなんだか「芸術性に欠ける」と思いませんか？

ここからは、最初に定義した広大な「表」（A1:G11）という名前だけを使い、このカオスな盤面から答えを探し出す、純粋な関数パズルに挑戦しましょう！

アプローチ4：INDEX + SUM + COLUMN/ROW（配列計算の王道）

考えかた

さて、配列計算の真骨頂です。

広大な「表」の範囲に対して、条件に合うセルの「行番号」と「列番号」を特定し、INDEX関数でピンポイントに抜き出します。

数式と解説

=INDEX(表,SUM((INDEX(表,,SUM((表=B13)*COLUMN(表)))=B14)*ROW(表)),SUM((表=B13)*COLUMN(表))+1)

この数式は、INDEX関数の中にINDEX関数が入っているような、入れ子構造になっています。

では、内側から解き明かしていきましょう。

1. プラン列の特定: SUM((表=B13)*COLUMN(表))

・(表=B13): 「表」の範囲全体で、B13セル（”B社”）と一致するかを判定し、TRUE/FALSEの配列を作ります。”B社”と書かれたB6セルだけがTRUE(1)になります。

Excel説明画像

・...*COLUMN(表): このTRUE/FALSE(1/0)の配列に、各列の列番号を掛け合わせます。結果、B6セルだけが「2」（B列の列番号）となり、他は0になります。

Excel説明画像

・SUM(...): 最後に合計することで、「2」という「B社」の列番号を特定します。

2. 料金の行番号の特定: SUM((INDEX(表,,プラン列)=B14)*ROW(表))

・INDEX(表,,プラン列): INDEX関数を使い、「表」のすべての行の、「プラン列」（先ほど計算した2列目）だけを取り出します。

Excel説明画像

・...=B14: 取り出したプラン列の中から、B14セル（”プラン3″）と一致するかを判定し、C8セルに対応する場所だけがTRUE(1)になる配列を作ります。

Excel説明画像

・...*ROW(表): この配列に行番号を掛け合わせ、合計することで、「8」という「プラン3」の行番号を特定します。

Excel説明画像

3. 最終的な料金の抽出: INDEX(表, 行番号, プラン列+1)

・INDEX(表, 8, 2+1): 最後に、INDEX関数を使い、「表」の中から「8行目」の「プラン列(2)の隣(+1)」、つまり3列目（C列）の値を抜き出します。

これで目的の料金が求まります！

Excel説明画像

メリット＆デメリット

メリット: 配列計算の仕組みを理解するための、最高のトレーニングになる。

デメリット: 数式が非常に難解で、他の人が解読するのがほぼ不可能。

アプローチ5：OFFSET + VLOOKUP（動的範囲生成）

考えかた

今度は、OFFSET関数を使って、条件に合う会社の料金表の「範囲そのもの」を動的に作り出し、それをVLOOKUP関数の参照範囲として渡す、というアプローチです。

数式と解説

=VLOOKUP(B14,OFFSET(A1,SUM((表=B13)*SEQUENCE(ROWS(表),,0)),SUM((表=B13)*SEQUENCE(,COLUMNS(表),0)),4,2),2,FALSE)

・SUM((表=B13)*SEQUENCE(...)): この部分が、OFFSETの移動量を計算しています。

アプローチ4と似ていますが、「B社」という文字がある場所までの行数と列数を計算し、A1セルからどれだけ移動すればよいかを特定しています。

Excel説明画像

・OFFSET(A1, 行移動量, 列移動量, 4, 2): OFFSET関数が、A1セルを基準に、特定した行数・列数だけ移動した場所から、高さ4・幅2のセル範囲を動的に生成します。

これで「B社」の料金表だけがメモリ上に浮かび上がります。

Excel説明画像

・VLOOKUP(B14, ..., 2, FALSE): OFFSETが作り出した、まさに今必要な会社だけの料金表の範囲を、VLOOKUPが検索します。

Excel説明画像

メリット＆デメリット

メリット: OFFSET関数の強力な動的範囲生成能力を学べる。

デメリット: OFFSETも揮発性関数であることと、数式が複雑であること。

アプローチ6：INDIRECT + ADDRESS（座標からの逆引き）

考えかた

アプローチ4とロジックは似ていますが、最後の締め方が異なります。

まず、料金が入力されているセルの「行番号」と「列番号」を特定した後、それをADDRESS関数で「セル番地」（例: “C8″）に変換し、INDIRECT関数でそのセルの中身を抜き出します。

数式と解説

=INDIRECT(ADDRESS(SUM((INDEX(表,,SUM((表=B13)*COLUMN(表)))=B14)*ROW(表)),SUM((表=B13)*COLUMN(表))+1))

・SUM(...): アプローチ4と全く同じ方法で、目的の料金セルの「行番号」と、料金セルの「列番号」を計算します。今回の例では、行番号が「8」、列番号が「3」と計算されます。

Excel説明画像

・ADDRESS(8, 3): 計算された行番号「8」と列番号「3」から、ADDRESS関数が「$C$8」のようなセル番地の文字列を生成します。

・INDIRECT("$C$8"): 最後に、INDIRECT関数がこの「$C$8」という文字列を、本物のC8セルへの参照に変換し、中の値を表示させます。

Excel説明画像

メリット＆デメリット

メリット: 行番号と列番号からセル番地を組み立てるプロセスが、アプローチ4より少しだけ分かりやすい。

デメリット: 非常に難解で、揮発性関数を2つも使っている。

まとめ：最強の技は「分かりやすさ」

散らかった参照表という、実務で誰もが頭を抱える問題に対して、6つもの異なるアプローチで立ち向かってみました。

「…それで、結局、どの数式を使えばいいの？」

もちろん、最適なのはアプローチ1の「VLOOKUP + INDIRECT」です！

数式は、ただ動けばいいというものではありません。

他の人が見たときに、そして未来の自分が見たときに、「何をやっているか」がすぐに理解できること。

この「可読性」こそが、実務においては最も重要なのです。

しかし、今回挑戦した関数パズルたちは、決して無駄ではありません。

これらの難解な数式を理解しようとすることで、Excelの配列計算の仕組みや、各関数の持つ本当の力を、より深く知ることができます。

その知識は、いつかあなたがもっと複雑な問題に直面したとき、きっと新しい解決策への扉を開いてくれるはずですよ！

今回登場した関数リスト

  • VLOOKUP(検索値, 範囲, 列番号, [検索方法]): 表の左端の列を検索し、同じ行の指定した列の値を返します。
  • INDIRECT(参照文字列, [参照形式]): 文字列をセル参照に変換します。
  • IFS(論理式1, 値が真の場合1, [論理式2, ...]): 複数の条件を順番に判定し、最初に真になった条件に対応する値を返します。
  • LET(名前1, 値1, [名前2, ...], 計算): 数式内で計算結果に名前をつけ、可読性を高めます。
  • TEXTJOIN(区切り文字, 空のセルを無視, 文字列1, [文字列2, ...]): 区切り文字を挟んで、複数の文字列や範囲を連結します。
  • FIND(検索文字列, 対象, [開始位置]): ある文字列の中から、指定した文字列が最初に現れる位置を返します。
  • MID(文字列, 開始位置, 文字数): 文字列の指定した位置から、指定した文字数だけを抜き出します。
  • VALUE(文字列): 数値に変換できる文字列を、数値に変換します。
  • INDEX(配列, 行番号, [列番号]): 配列の中から、指定した行と列の位置にある値を返します。
  • OFFSET(参照, 行数, 列数, [高さ], [幅]): 基準セルから指定した行数・列数だけずれた位置にある、指定した高さ・幅のセル範囲への参照を返します。
  • SEQUENCE(行数, [列数], [開始], [ステップ]): 連続した数値の配列を生成します。
  • ROWS/COLUMNS(配列): セル範囲の行数/列数を返します。
  • ADDRESS(行番号, 列番号, [参照の種類], [参照形式], [シート名]): 行番号と列番号から、セル番地の文字列を生成します。
タイトルとURLをコピーしました