VLOOKUP関数、使いこなしていますか?

日ごろの業務で使い慣れてくると、最初は複雑だと思っていた引数の指定もお手のもの。いろいろなシーンで使いたくなりますよね。

本連載ではVLOOKUP関数の基本と構造化参照について解説してきましたが(リンクは記事末尾にあります)、今回はさらに踏み込んだワザを紹介しましょう。ポイントは「2つの条件」を使うことです。

エクセル時短】第51回では、VLOOKUP関数の上級ワザとして、2つの条件を指定して値を検索する方法を解説します!

VLOOKUP関数+構造化参照をあらためて理解する

まず、VLOOKUP関数の構文をおさらいします。

VLOOKUP検索値, 範囲, 列番号, 検索の型

そして今回、参照する表はこちら。テーブルに変換し、「価格表」という名前を付けています。

【エクセル時短】VLOOKUPの応用ワザ。2つの条件で値を検索するために組み合わせる関数とは?

サービスの提供実績を4つの会員種別ごとにまとめた表です。この表を参照し、最終的に「サービスNo」と「会員種別」という2つの条件に一致する実績を求めます。

まずは練習として、「サービスNo」に対応する「サービス名」を取り出すVLOOKUP関数を入力してみます。

1VLOOKUP関数の数式を入力する

【エクセル時短】VLOOKUPの応用ワザ。2つの条件で値を検索するために組み合わせる関数とは?

こちらは取り出した値をまとめる表です。VLOOKUP関数の引数のうち、[検索値]はセルC2の「サービスNo」、[範囲]は「価格表」テーブル、[列番号]はCOLUMN関数を使い、「価格表」にある「サービス名」の列番号を取得するようにしました。参照する表にデータの増減があっても数式を修正しなくて済むように、構造化参照しているわけですね。数式を書き出すと以下のようになります。

=VLOOKUP(C2,価格表,COLUMN(価格表[サービス名]),FALSE)

2「サービス名」を取り出せた

【エクセル時短】VLOOKUPの応用ワザ。2つの条件で値を検索するために組み合わせる関数とは?

意図通り、「サービスNo」に対応する「サービス名」を取り出すことができました。

構造化参照とINDIRECT関数を組み合わせる

さて、ここからが本番です。

今度は「サービスNo」を[検索値]にしつつ、「会員種別」をもう1つの条件として指定できるVLOOKUP関数の数式を作ります。しかし、[検索値]は1つしか指定できません。

そんなときは[列番号]で指定する構造化参照に「INDIRECT」(インダイレクト)関数を組み合わせます。

12つの条件を指定したVLOOKUP関数の数式を入力する

【エクセル時短】VLOOKUPの応用ワザ。2つの条件で値を検索するために組み合わせる関数とは?

検索値]はセルC2の「サービスNo」、[範囲]は「価格表」テーブルと、ここまでは先ほどと同じ。[列番号]のCOLUMN関数にINDIRECT関数を組み合わせているところに注目してください。数式は以下の通りです。

=VLOOKUP(C2,価格表,COLUMN(INDIRECT("価格表"&"["&B2&"]")),FALSE)

2「サービスNo」と「会員種別」に対応する実績を取り出せた

【エクセル時短】VLOOKUPの応用ワザ。2つの条件で値を検索するために組み合わせる関数とは?

「サービスNo」と「会員種別」の2つの条件を満たす実績を取り出せました。

引数[列番号]のCOLUMN関数にネストしたINDIRECT関数は、指定したセルの値で別のセルを参照する関数です。構文は以下のようになります。

INDIRECT参照文字列,参照形式

2つ目の引数[参照形式]はTRUEまたはFALSEで指定しますが、省略するとExcelで標準の形式(A1形式)が指定されます。通常は省略してかまいません。

「INDIRECT("価格表"&"["&B2&"]")」のうち、「&」は文字列を連結する演算子です。つまり、この数式はセルB2の値によって「価格表[会員]」や「価格表[非会員(特別)]」などとなります。

それがCOLUMN関数の引数となり、VLOOKUP関数の[列番号]を切り替えることができる、というわけです。

【エクセル時短】VLOOKUPの応用ワザ。2つの条件で値を検索するために組み合わせる関数とは?

試しに会員種別を「非会員(特別)」に切り替えると、実績の値が自動的に変化しました。なお、「会員種別」列は入力規則でドロップダウンリストを設定しておくと便利です。

いかがでしたか? VLOOKUP関数で複数の条件を使えるようにするには、参照先となる表を分ける方法もあります。ただ、似たような表をいくつも作るよりは、今回のように1つの表で完結したほうがスッキリするでしょう。構造化参照と合わせて活用してくださいね。

関連記事

【エクセル時短】は「少しでも早く仕事を終わらせたい!」というビジネスパーソンのみなさんに、Excelの作業効率をアップするワザをお届けする連載です。毎週木曜日更新。
過去の記事一覧【エクセル時短まとめ】