検索

キーワード


【Excel】VLOOKUP関数で、検索値に対応する二つの値を表示させる方法

  • 公開日:2020-10-05 17:48:00
  • 最終更新日:2021-01-25 12:32:54
【Excel】VLOOKUP関数で、検索値に対応する二つの値を表示させる方法

こんにちは、新人エンジニアの田口です。

今回は具体的な例を用いてVLOOKUP関数の活用場面を見ていきます。


主に以下の内容について解説します。

・VLOOKUP関数で検索して表示させたい値が2つある場合

・列番号や絶対参照やを使ってコピペに対応したVLOOKUP関数の数式をつくる方法

説明にあたって、以下の事柄について知識があることを前提とします。

・VLOOKUP関数の基本的な書式

・引数など関数に関わる用語

・相対参照、絶対参照の違い


本文中でわからないことがあった場合は以下の記事をご覧ください。

【Excel】関数入門|数値と文字列の違いや、代表的な関数について解説

【Excel】大きな表からデータを転記・検索する!VLOOKUP関数の使い方

【Excel(一般)】コピーで参照がずれてしまう時の対処砲 - 絶対参照と相対参照について


検索値に対応する2つの値を表示させる方法

次のようなケースでVLOOKUP関数を使ったデータの検索の動きを確認します。

左下図のような「発注明細」シートがあるとして、指定した商品コードに対応する「単位」と「単価」の2つの値を表示させてみます。


例:商品コードで検索して、「商品一覧」表の「単位」と「単価」を取り出す

VLOOKUP関数を使ったデータの検索の動きを確認するサンプルデータ


別々に組み立ててみる

今回のように表示させたい値が2つあるような場合には、2つ同時に求めようとはせず、別々に数式を考えていきます。


「単位」を求める

まずは「単位」を求める数式を組み立てていきます。


条件

検索値 : 商品コードを

範囲  : 商品一覧表から探して

列   : 3列目にある「単位」を表示する

検索方法: 完全一致検索(FALSE)


=VLOOKUP(B3,商品一覧!$B$3:$E$7,3,FALSE)


「単位」を求める数式


「単価」を求める

「単価」を求める方法もほとんど同じです。

「単価」のデータは指定した範囲の4列目にあるので、以下のように数式化すれば完了です。


条件

検索値 : 商品コードを

範囲  : 商品一覧表から探して

列   : 4列目にある「単価」を表示する

検索方法: 完全一致検索(FALSE)


=VLOOKUP(B3,商品一覧!$B$3:$E$7,4,FALSE)

「単価」を求める数式


参照型を使い分けてコピペに対応させる

これまで「単位」と「単価」にそれぞれ別の数式を入力しました。

しかし、直接入力では時間がかかるのに加え、入力ミスの問題があります。

可能であればコピペで対応したいところですが、D3セルをE3セルにそのままコピーしてみてもうまく行きません。


単価」に表示したい値

=VLOOKUP(B3,商品一覧!$B$3:$E$7,4,FALSE)


コピペで表示される値

=VLOOKUP(C3,商品一覧!$B$3:$E$7,3,FALSE)


数式を比べると、検索値と列が異なっていることがわかります。

このようなケースでは、引数のセル参照を少し工夫することでVLOOKUP関数をコピーして使いまわせるようになります。


H3「検索値」を複合参照にして列を固定する

検索値を見ると、参照がBからCと右に一列ずれてしまっています。

この場合は「$B3」のように「列だけ絶対参照」をつけることによって、参照元を商品コード列のみに固定します。


「列番号」は別セルに入力する

前述の列番号は数値を入力して指定しているだけなので、セル参照をしていた検索値のようにコピーして値が変わることはありませんでした。

逆を言えば、列番号も同じようにセルを参照させて指定すればうまくいくということです。

そこで問題は、どのセルを参照するかということになります。


このケースでは、「発注明細」シートの表の見出しの上に列番号を追加します。

そして、VLOOKUP関数の「列」でこの列番号を指定しましょう。

この時、「D$1」というように、「行だけ絶対参照」にすることで、数式を右にコピーした時だけ参照が変化するようになります。


まとめ:参照型をうまく使い分けて、汎用性の高い数式をつくる。

前述した2点を組み合わせて数式を修正すると、次のようになります。

=VLOOKUP($B3,商品一覧!$B$3:$E$7,D$1,FALSE)

これで簡単にコピーして再利用ができる数式になりました。

参照型をうまく使い分けて、汎用性の高い数式を作成した例


ドラッグコピーをしても利用できることを確認


VLOOKUP関数は、ただ書き方がわかるだけではその力を発揮できません。

思ったように動作しないなと感じた時は、表の作り方を見直したり、参照型を変えてみたり、他の関数と一緒に使ったりといった柔軟さが必要となります。

Excelの知識を組み合わせて、便利で使いやすい数式づくりを心がけましょう。


その他の活用例

VLOOKUP関数の様々な使い方について解説しています。

【Excel】IF関数の代わりにVLOOKUP関数を使った条件分岐のやり方。

【Excel】VLOOKUP関数で「TRUE」を使って近似値検索を行うパターンの動きを確認する


【著者】

田口

元建築・リフォーム営業のエンジニア。現在はDXの需要に合わせてネットワークやクラウド、セキュリティといったインフラ方面の勉強中。

よく読まれている記事
【Excel】箱ひげ図の見方と作成方法について

【Excel】箱ひげ図の見方と作成方法について

こんにちは。新人エンジニアの前山です。Excel グラフの作り方 ではグラフの作成方法とレイアウトの編集について基本的な事項を解説しました。本記事では、Excelで作成できる箱ひげ図の見方とを作成方法についての解説を行います。箱ひげ図とは箱ひげ図とは、データのバラツキ、どの部分に集中しているかなどを「箱」と「ひげ」を用いてわかりやすく表したものとなります。大量のデータを扱う場合、平均とのみを活用す

【Excel】複数条件で判定|AND関数,OR関数を使った条件分岐|IF関数

【Excel】複数条件で判定|AND関数,OR関数を使った条件分岐|IF関数

AND関数、OR関数を使って、IF関数で複数の条件を指定するこんにちは、新人エンジニアの田口です。今回は具体的な例を用いてIF関数の活用場面を見ていきます。主に以下の内容について解説します。・IF関数で部分一致、完全一致の条件指定を行う・OR関数・AND関数説明にあたって、以下の事柄について知識があることを前提とします。・IF関数の基本的な書式・引数など関数に関わる用語本文中でわからないことがあっ

【Excel】グラフの作り方、レイアウトの編集方法

【Excel】グラフの作り方、レイアウトの編集方法

こんにちは。新人エンジニアの前山です。本記事では、Excelでのグラフの作り方とタイトルや目盛線、凡例といったよく使用する機能の解説を行います。データテーブルや近似曲線はあまり使用頻度は高くありませんが、ここぞというところで使用すれば、グラフの品質を1段階上げることができます。グラフの作り方1.グラフにしたい表の選択 2.挿入>おすすめグラフのクリック 3.作成するグラフ種類の選択今回は例として集

【Excel】日付処理の活用例 - 前日、翌日の求め方

【Excel】日付処理の活用例 - 前日、翌日の求め方

こんにちは。新人エンジニアの前山です。前回の記事 ではExcelでの日付の取り扱いと基本的な使い方を記載しました。本記事ではその応用として、任意の日付の前日、翌日日付の求め方と関数の紹介をしたいと思います。関連記事: 【Excel】日付処理の活用例 - 指定した日付までの日数の求め方 【Excel】日付処理の活用例 - 月初、月末の求め方前日、翌日の計算方法前日、翌日日付には主に3つの求め方があり

【Excel】日付処理で使用する関数、シリアル値について

【Excel】日付処理で使用する関数、シリアル値について

こんにちは。新人エンジニアの前山です。本記事ではExcelで使用される日付関連の情報をご紹介します。Excel内では日付はシリアル値という数値で管理されており、仕組みを知れば、柔軟な日付計算が可能になります。また、日付には様々な関数が用意されており、翌日前日、期間、月初、月末、営業日などが求められます。日付についての理解を深めれば日々の業務がぐっと楽になります。シリアル値とはシリアル値とは、Exc