複数条件で表引きする方法として、次の3種類の方法を紹介します。
- VLOOKUP関数＋作業列を使う方法
- XLOOKUP関数を使う方法（Excel 2021以降またはMicrosoft 365）
- INDEX関数＋MATCH関数＋配列数式を使う方法
やりたいこと
ここでは、図1 の電話番号簿から電話番号を検索します。検索のキーワードは「支店名」と「部署名」の2つです。
図1 セルB2～B3に入力した支店名と部署名を「電話番号簿」から探し、電話番号を求めたい。
方法1…VLOOKUP関数＋作業列を使う方法
まずは、表引き関数の代表格である VLOOKUP関数による方法を見ていきます。VLOOKUP関数はどのバージョンのExcelでも使用できる点がメリットです。VLOOKUP関数の基本的な使い方は「VLOOKUP関数 ● 表を縦方向に検索してデータを取り出す」を参照してください。
VLOOKUP関数で検索できるのは、表の 1列目に限られます。検索したいデータが 複数ある場合は、下準備として表の左隣に作業列を設け、複数列のデータを連結しておきます。今回の例では、図2のように「支店名 & 部署名」の列を用意します。
図2 電話番号簿の左隣の列のセルD3に「=E3&F3」と入力して、支店名と部署名を連結する。この数式をセルD8までコピーする。
VLOOKUP関数の引数［検索値］にも「支店名 & 部署名」を指定します。［範囲］に作業列を含めた電話番号簿のセル範囲を指定します。［列番号］に「電話番号」欄の列番号「4」を指定し、［検索の型］に完全一致検索の「FALSE」を指定すると、図3のように該当の電話番号を取り出せます。
表を縦に検索してデータを取り出す
=VLOOKUP(検索値, 範囲, 列番号, ［検索の型］)
セルB4 | =VLOOKUP(B2&B3,D3:G8,4,FALSE)
図3 セルB4に VLOOKUP関数を入力すると、大阪支店の経理部の電話番号が表示される。
メモ
方法2…XLOOKUP関数を使う方法
XLOOKUP関数なら単独で複数条件の表引きを行えるので、作業列を用意する手間がかかりません。ただし、XLOOKUP関数は Excel 2021より前のバージョンでは使用できないので注意してください。XLOOKUP関数の基本的な使い方は「XLOOKUP関数 ● さまざまな条件を指定してデータを取り出す (1)完全一致検索の場合」を参照してください。
XLOOKUP関数の引数［検索値］と［検索範囲］を「支店名 & 部署名」の形式で指定し、［戻り値範囲］に電話番号の列を指定します。「大阪支店経理部」は［検索範囲］の 5番目の位置で見つかるので、［戻り値範囲］の 5番目の位置から電話番号が取り出されます。
さまざまな条件を指定してデータを取り出す
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
セルB4 | =XLOOKUP(B2&B3,E3:E8&F3:F8,G3:G8)
図4 セルB4に XLOOKUP関数を入力すると、大阪支店の経理部の電話番号が表示される。
方法3…INDEX関数＋MATCH関数＋配列数式を使う方法
XLOOKUP関数が使えないバージョンで、作業列を使いたくない場合は、INDEX関数＋MATCH関数を配列数式として入力する方法があります。入力する数式は
{=INDEX(G3:G8,MATCH(B2&B3,E3:E8&F3:F8,0))}
なのですが、いきなりだと難しいので順を追って説明します。
ひとまず INDEX関数を置いておいて、MATCH関数の説明から始めます。MATCH関数は、［検査値］が［検査範囲］の何番目にあるかを求める関数です。完全一致検索を行うには［照合の型］に「0」を指定します。
今回のように［検査値］と［検査範囲］に「支店名 & 部署名」形式で指定する場合は、配列数式として入力しなければなりません。配列数式にするには、数式の入力後に［Enter］キーではなく［Ctrl］＋［Shift］＋［Enter］キーで確定します。
指定した範囲の中から検索値の位置を求める
=MATCH(検査値, 検査範囲, ［照合の型］)
セルB4 | =MATCH(B2&B3,E3:E8&F3:F8,0)
※［Ctrl］＋［Shift］＋［Enter］キーで確定
図5 セルB4に図の数式を入力して、［Ctrl］＋［Shift］＋［Enter］キーを押す。
「大阪支店経理部」は［検査範囲］の5番目の位置にあるので、 セルB4に「5」と表示されます。セルB4を選択して数式バーを見ると、数式が「{ }」で囲まれています。配列数式は自動的に「{ }」で囲まれる仕組みになっています。
図6 「大阪支店経理部」は［検査範囲］の5番目の位置にあるので、セルB4に「5」と表示される。
MATCH関数の戻り値が「5」になることがわかったら、ここからが本番です。セルB4にINDEX関数を入力し直します。INDEX関数は、［参照］のセル範囲の中から［行番号］目のセルを返す関数です。［参照］に電話番号のセル範囲を指定し、［行番号］に先ほどのMATCH関数を指定します。［Ctrl］＋［Shift］＋［Enter］キーで確定すると、「=INDEX(G3:G8,5)」が実行されて「電話番号」欄の「5」行目の位置にあるデータが表示されます。
指定した行と列の位置にあるセルを取り出す
=INDEX(参照, 行番号, ［列番号］, ［領域番号］)
セルB4 | =INDEX(G2:G3,MATCH(B2&B3,E3:E8&F3:F8,0))
※［Ctrl］＋［Shift］＋［Enter］キーで確定
図7 セルB4に数式を入れ直して［Ctrl］＋［Shift］＋［Enter］キーを押すと、大阪支店の経理部の電話番号が表示される。
メモ
メモ