連載「仕事がサクサク進む！Excel関数活用術」、前回は本日の日付を出すTODAY関数、日数計算を行うDATEDIF関数、Excelの相対参照と絶対参照を紹介しました。
5回目の今回は、検索条件に一致したデータを抽出するVLOOKUP関数を詳しく解説していきます。
VLOOKUP関数は、たくさんあるExcelの関数の中でも使用頻度の高い関数です。しかし、「使い方が難しすぎる…」という声もしばしば。
１つひとつ手順を詳しく説明しますので、この機会にVLOOKUP関数の使い方を覚えてくださいね。
仕事がサクサク進む！Excel関数活用術（全6回）
目次
検索条件に一致したデータを抽出するVLOOKUP関数
VLOOKUP関数は、指定したセルの範囲から、検索条件に一致したデータのみを抽出したいときに使います。
例えば、あなたが経理業務または営業事務を担当していて、請求書を毎月作成しているとします。
「A社はりんごが50個、みかん100個だから○円…」と、1社ずつ請求書に商品名と単価、個数を記入して、売上を計算するのはとても手間がかかる仕事。
しかし、VLOOKUP関数をセルに記述した請求書を使えば、商品番号を入力するだけで「商品名」と「単価」の自動入力が実現します。
入力の手間を大幅に減らせるので、VLOOKUP関数は総務や経理、人事、営業と様々な部門の人が仕事で使う、Excelで人気の高い関数なのです。
VLOOKUP関数の4つの引数
VLOOKUP関数は仕事の効率アップに便利な関数です。しかし、指定する引数が4つもあってちょっと使い方が複雑。VLOOKUP関数の基本構文は以下のようになっています。
＝VLOOKUP（検索値,範囲,列番号,検索方法）
引数1「検索値」
データ（表）の中から、検索対象となるコードや番号が入力されたセル（B1など）を指定。
引数2「範囲」
抽出したいデータ（値）を検索するセルの範囲（A3：F12など）を指定。
引数3「列番号」
範囲に指定したデータ（表）を左から数えて何番目の列を参照するか（「1」「2」などの数値）を指定。
引数4「検索方法」
引数に指定した範囲を検索して、値が見つからなかったときにどう処理するかを指定。
指定した範囲から検索値に完全に一致する値を抽出するときは「FALSE」を、検索値に一致するデータがなく、検索値を超えない最大値（近似値）を抽出するときは「TRUE」※と指定。
※ 仕事でVLOOKUP関数を使う場合、引数に指定した範囲の中から一致したデータを抽出するケースがほとんど。そのため、使い始めのうちは、検索方法で指定する引数は「FALSE」と覚えておけばよいでしょう。成績や予算など基準値を超えない値から最大値を抽出するといった特殊な事情を除き、「TRUE」を使うことはまずありません。
VLOOKUP関数で請求書を自動作成してみよう
請求書の自動作成（商品番号を入力しただけの請求書に商品名と単価を自動入力させる）を例に、VLOOKUP関数の使い方を一緒に確認してみましょう。
STEP1：元データとなる商品一覧表を作成
以下の図を参考にして、元データとなる商品一覧表を作成してみてください。
STEP2：請求書を作成
VLOOKUP関数を入力する請求書を作成します。「単価×数量」の計算結果を表示するセル（E3～E11）と、請求金額の合計を表示するセル（E12）には、下の図のように数式を入れておきましょう。
STEP3：VLOOKUP関数で商品名・単価を自動表示
請求書に「商品コード」を入力すると、「商品名」を自動表示するよう、VLOOKUP関数を入力します。
① A3からA6のセルに商品コードを入力。
② 商品名のB3のセルに「=VLOOKUP()」と入力。
③ 商品コードを使って表Bの商品一覧から商品名を検索するので、引数の「検索値」はA3セルを指定。
④ 表Bの商品一覧から商品名を検索するので、引数の「範囲」は「$G$3:$I$6」と指定（抽出するデータを検索する表の範囲は固定なので、列と行に「$」で絶対参照のマークをつける）。
⑤ 表Bの範囲のうち、商品名が書かれた左から2番目の列をB3セルに抽出するので、引数のうち「列番号」は「2」と指定。
⑥ 表Aと表Bの商品コードが完全に一致するデータを抽出するので、引数のうち「検索方法」は「FALSE」と指定。
⑦ Enterキーを押す。B3セルに「りんご」と表示されます。
⑧ B3セルの右下にカーソルを持っていき、入力した数式をオートフィルでB6セルまでコピーします。
⑨ C列の単価（C3からC6セル）についても、手順②～⑧と同じ要領で、表Bから単価のデータを抽出します。表Bから抽出する単価の列番号は「3」です。引数「列番号」指定に注意してください。
⑩ 最後にD列の数量を手入力すれば、商品ごとの金額とすべての請求金額が自動計算されます。
引数の指定は3つだけ！検索条件に一致するデータを抽出するXLOOKUP関数
特定のデータに対応する値を抽出したいときには、新関数のXLOOKUP関数も使えます。
現在では2021年10月発売のExcel2021やOffice 365でのみ使用可能ですが、便利な関数で今後人気の出てくる関数となり得るため、覚えておくといいでしょう。
VLOOKUP関数との違いは、検索する範囲を直接指定するため列番号を指定する必要がなく、引数の指定方法がわかりやすくなっている点です。
また、検索値は検索範囲の中で必ず左側になければならないという制限がなくなっています。
XLOOKUP関数の引数
XLOOKUP関数は、引数が6つもありますがその中で必須なのは3つだけ。半分は省略可能です。指定方法が直感的に分かりやすいのが特徴です。
＝XLOOKUP（検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード）
引数1「検索値」（必須）
検索対象となるセルを指定。
引数2「検索範囲」（必須）
抽出したいデータを検索するセルの範囲を指定。
引数3「戻り範囲」（必須）
検索結果として返すセルの範囲を指定。
引数4「見つからない場合」（省略可）
検索値が見つからない場合、何を表示するかを指定。（テキストで表示）
引数5「一致モード」（省略可）
一致の種類を指定。（完全一致、完全一致または次に大きいもの など）
引数6「検索モード」（省略可）
検索する順序を指定。（既定の設定では先頭から末尾）
XLOOKUP関数を使ってみよう
先ほどVLOOKUP関数を使って作った請求書を、今度はXLOOKUP関数を使って作成してみましょう。
請求書の作成
今回指定する引数は3つのみです。引数の指定方法に注目してください。
① 商品名を抽出したいB3のセルに「=XLOOKUP（）」と入力。
② 検索値は「A3」セルを指定。
③ 検索範囲は「$G$3:$G$6」と指定。
④ 戻り範囲は「$H$3:$H$6」と指定。
※③と④の検索範囲と戻り範囲は固定なので、「＄」で絶対参照マークをつける。
⑤ Enterキーを押す。B3セルに「りんご」と表示されます。
⑥ B3のセルからオートフィルでB6のセルまで数式をコピーして完成です。
複数の列に検索結果を表示
また、XLOOKUP関数では、複数の列の検索結果を返すことが可能になっています。
商品名と同時に単価も表示させてみましょう。
① 検索値は「A3」セルを指定。
② 検索範囲は「$G$3:$G$6」と指定。
③ 戻り範囲に「$H$3:$I$6」を指定。
④ Enterキーを押すと、B3セルに「りんご」、C3セルに「100」と表示されます。
※このとき、「数式が複数の値を返したので、値が隣接する空のセルにスピルされました。」とメッセージが出ることがあります。これは、スピルといわれる“数式を入力したセルに隣接するセルにも、結果が表示される機能”があるためです。
XLOOKUP関数を使うときに注意しておきたいのは、検索範囲と戻り範囲のセルの高さを合わせること。例えば、「=XLOOKUP（A3,G3:G4, H3:H6）」というように、指定する範囲の行にずれがあるものはNGなので注意しましょう。
今回はVLOOKUP関数の使い方を説明しました。
慣れないうちは、引数の指定がちょっと複雑なので「自分には無理かも」と思うかもしれません。しかし、使い方をマスターすれば、何万件もある膨大なExcelデータの中から必要なものだけを効率よくピックアップできて、仕事がラクになりますよ。
次回は連載最終回。条件から必要な処理を返すIF関数とCOUNTIF関数をご紹介します。ではまた！
※画像はすべてExcel2019を使用
無料登録でオンラインの資格講座を体験しよう！
資格受け放題の学習サービス『オンスク.JP』では様々な資格講座のオンライン学習が可能です。
最短20秒の無料会員登録で、各講座の講義動画・問題演習の一部が無料体験できます。
※無料会員は、決済情報入力なしでご利用可能。
※自動で有料プランになることはありません。
関連する記事が他にもあります
