複数条件で表引きする方法として、次の3種類の方法を紹介します。
- VLOOKUP関数+作業列を使う方法
- XLOOKUP関数を使う方法(Excel 2021以降またはMicrosoft 365)
- INDEX関数+MATCH関数+配列数式を使う方法
やりたいこと
ここでは、図1 の電話番号簿から電話番号を検索します。検索のキーワードは「支店名」と「部署名」の2つです。
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-01.png?resize=736%2C294&ssl=1)
図1 セルB2~B3に入力した支店名と部署名を「電話番号簿」から探し、電話番号を求めたい。
方法1…VLOOKUP関数+作業列を使う方法
まずは、表引き関数の代表格である VLOOKUP関数による方法を見ていきます。VLOOKUP関数はどのバージョンのExcelでも使用できる点がメリットです。VLOOKUP関数の基本的な使い方は「VLOOKUP関数 ● 表を縦方向に検索してデータを取り出す」を参照してください。
VLOOKUP関数で検索できるのは、表の 1列目に限られます。検索したいデータが 複数ある場合は、下準備として表の左隣に作業列を設け、複数列のデータを連結しておきます。今回の例では、図2のように「支店名 & 部署名」の列を用意します。
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-02.png?resize=736%2C310&ssl=1)
図2 電話番号簿の左隣の列のセルD3に「=E3&F3」と入力して、支店名と部署名を連結する。この数式をセルD8までコピーする。
VLOOKUP関数の引数[検索値]にも「支店名 & 部署名」を指定します。[範囲]に作業列を含めた電話番号簿のセル範囲を指定します。[列番号]に「電話番号」欄の列番号「4」を指定し、[検索の型]に完全一致検索の「FALSE」を指定すると、図3のように該当の電話番号を取り出せます。
表を縦に検索してデータを取り出す
=VLOOKUP(検索値, 範囲, 列番号, [検索の型])
セルB4 | =VLOOKUP(B2&B3,D3:G8,4,FALSE)
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-03.png?resize=736%2C310&ssl=1)
図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)
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-04.png?resize=736%2C310&ssl=1)
図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]キーで確定
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-05.png?resize=736%2C310&ssl=1)
図5 セルB4に図の数式を入力して、[Ctrl]+[Shift]+[Enter]キーを押す。
「大阪支店経理部」は[検査範囲]の5番目の位置にあるので、 セルB4に「5」と表示されます。セルB4を選択して数式バーを見ると、数式が「{ }」で囲まれています。配列数式は自動的に「{ }」で囲まれる仕組みになっています。
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-06.png?resize=736%2C310&ssl=1)
図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]キーで確定
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-07.png?resize=736%2C310&ssl=1)
図7 セルB4に数式を入れ直して[Ctrl]+[Shift]+[Enter]キーを押すと、大阪支店の経理部の電話番号が表示される。
メモ
![](https://i0.wp.com/officek.net/wp-content/uploads/2021/11/efv-tablesearch-01-08.png?resize=783%2C100&ssl=1)
メモ