Excel(エクセル）のXLOOKUP関数(エックス・ルックアップ)は、VLOOKUP関数の後継関数です。
エクセルのバージョンがExcel2021以上、もしくはMicrosoft365(サブスク）を利用しているユーザーで利用出来ます。
表を縦方向にも横方向にも検索して抽出することが出来ますが、検索値が一つだけだと抽出データが重複してしまう場合があります。
今回は、XLOOKUP関数の使い方を復習しつつ、『複数の条件』を検索値にするXLOOKUP関数の使い方を紹介します。
最後の見出しでは、XLOOKUP関数を使わず、『INDEX関数とMATCH関数を使った検索方法』も紹介しますので、XLOOKUP関数の利用がどうしても苦手だという方は、ぜひ参考にしてみて下さい。
XLOOKUP関数とは？(おさらい）
XLOOKUP関数を普段使わない方は、使い方をおさらいをしましょう。
使い方を熟知されている方は下の見出しまで読み飛ばしてください。
XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の両方の機能を兼ね備え、さらに大幅に機能が強化された新関数です。
XLOOKUP関数では以下の機能を利用することが出来ます。
指定した検索値に『当てはまる値』を、指定した検索範囲から縦方向や横方向で調べて、指定した位置から抽出することが出来る関数
XLOOKUP関数の書式
XLOOKUP関数の書式(構文)は下のとおりで、６つの引数が存在します。
＝XLOOKUP(検索値,検索範囲,戻り範囲, [見つからない場合] , [一致モード] , [検索モード] ）
XLOOKUP関数は構文が長いので一見複雑なように感じます。
しかし、XLOOKUP関数の後半３つの引数は省略可能な上、VLOOKUP関数より引数の設定が簡単なので、XLOOKUP関数の方が断然便利です。
XLOOKUP関数の使い方( おさらい）
XLOOKUP関数の使い方を手順に沿って紹介します。
使い方を熟知されている方は下の見出しまで読み飛ばしてください。
下の表の「商品名」に該当する「商品番号」をXLOOKUP関数を使って抽出します。
手順１. XLOOKUP関数を挿入
値を抽出したいH3セルにXLOOKUP関数を挿入するため、「＝XLOOKUP(」と入力します。
＝XLOOKUP(
手順２. XLOOKUP関数の第１引数を入力
続いて第１引数「検索値」を設定するため、商品名「風邪薬」が入力されているG3セルをクリックし、”,”(カンマ)を入力します。
＝XLOOKUP(G3,
手順３. XLOOKUP関数の第２引数を入力
続いて第２引数「検索範囲」を設定するため、検索値である「商品名」列をドラッグで選択します。選択し終えたら、”,”(カンマ)を入力します。
=XLOOKUP(G3,D3:D8,
手順４．XLOOKUP関数の第３引数を入力
続いて第３引数「戻り範囲」を設定します。「戻り範囲」とは値を抽出するセル範囲のことを言います。
「商品番号」列をドラッグで選択します。
=XLOOKUP(G3,D3:D8,B3:B8
手順５．XLOOKUP関数を確定
第４引数を入力したら、「Enter」キーを押して関数を確定させてください。
そうすると、表の中から商品名「風邪薬」の商品番号「25」が抽出されました。
VLOOKUP関数よりXLOOKUP関数の方が最も優れている点は、「検索範囲が戻り範囲の右側でも大丈夫」「列番号の指定が不要」ということです。
XLOOKUP関数の特徴は以下の記事で紹介していますので、興味のある方は覗いてみて下さい。
「検索値」が重複している場合
「検索値」が重複している場合にXLOOKUP関数を使うとどうなるでしょうか。
下の表で商品番号「18」は５行目と９行目に存在します。
この商品番号「18」の「商品名」をXLOOKUP関数で抽出しようとすると、上側(５行目）の「ヘアワックス」が抽出値として抽出されます。
XLOOKUP関数は下方向から検索することも出来ます。
６つ目の引数「検索モード」に「-1」を使って、以下のXLOOKUP関数を挿入します。(４，５つ目の引数は使わないので、カンマ「,」で区切ります）
=XLOOKUP(G3,B3:B9,D3:D9,,,-1)
そうすると、下側の「商品名」の「ドライヤー」が抽出されます。
このようにXLOOKUP関数には「検索モード」を指定することで検索する方向を変更出来ます。
しかし、３つ以上の複数の検索値が重複している場合や、抽出したい値が表のどの位置にあるか分からない場合には役に立ちません。
この場合は、「商品名」と「商品カテゴリ」の２つを検索値(２つの条件)を指定し、値を抽出する必要があります。
XLOOKUP関数で複数条件(２つ)を指定する方法
それでは、下の表を使って、店舗「B店」の商品名「シャンプー」という２つの条件で「値段」を抽出する方法を紹介します。
結論から先に言ってしまうと、下の２つの操作を実施するだけで複数条件に対応出来ます。
複数条件に対応する方法
１．表にそれぞれの条件を結合したXLOOKUP関数用のキー列を挿入する
２．XLOOKUP関数の1つ目の引数「検索値」に”＆”を利用する
それでは、実際に作業手順を紹介します。
手順１．表に「キー」列を追加(２つの文字列を結合)
「商品名」の右に新規に１列を追加し、「店舗」と「商品名」を「＆」演算子を利用して文字を結合します。
=B3&C3
下のセルにも数式をコピーして文字を結合します。
手順２．XLOOKUP関数を挿入(第１引数を指定 )
XLOOKUP関数を挿入し、第１引数に２つの検索条件「店舗」と「商品名」を＆演算子で結合して指定します。
=XLOOKUP(G3&H3,
手順３．XLOOKUP関数を挿入(第２引数を指定)
次に２つ目の引数「検索範囲」を指定するため、手順１で作成したキー列をドラッグして指定します。
必要に応じて絶対参照の「$」を指定してください。
=XLOOKUP(G3&H3,$D$3:$D$10,
手順４．XLOOKUP関数を挿入(第３引数を指定)
最後に３つ目の引数「戻り範囲」(抽出したい範囲）を指定するため、「値段」列をドラッグして指定します。
必要に応じて絶対参照の「$」を指定してください。
=XLOOKUP(G3&H3,$D$3:$D$10,$E$3:$E$10)
手順５．２つの複数条件の抽出結果を確認
関数を確定すると、「B店」の「シャンプー」の値段「1,000」円を抽出することが出来ます。
このように「＆」演算子をキー列の作成と、XLOOKUP関数の中にそれぞれ利用することで、複数の条件で検索値を抽出することが可能となります。
XLOOKUP関数で３つの複数条件で利用する際も「＆」演算子を用いることで可能となりますので、次の見出しで紹介します。
XLOOKUP関数で複数条件(３つ)を指定
今度は下の表を使って、店舗「B店」の商品名「シャンプー」の「一般会員」という３つの条件で「値段」を抽出する方法を紹介します。
それでは、実際に作業手順を紹介します。
手順１．表に「キー」列を追加(３つの文字列を結合)
「商品名」の右に新規に１列を追加し、「店舗」と「商品名」と「会員区分」を「＆」演算子を利用して文字を結合します。
=B3&C3&D3
下のセルにも数式をコピーして文字を結合します。
手順２．XLOOKUP関数を挿入(第１～３引数を指定 )
続いて、XLOOKUP関数の第１～第３引数を指定していきます。
XLOOKUP関数を挿入し、第１引数に３つの検索条件「店舗」と「商品名」と「会員区分」を「＆」演算子で結合して指定します。
=XLOOKUP(H3&I3&J3,
次に２つ目の引数「検索範囲」を指定するため、手順１で作成したキー列をドラッグして指定します。
必要に応じて絶対参照の「$」を指定してください。
=XLOOKUP(H3&I3&J3,$E$3:$E$10,
最後に３つ目の引数「戻り範囲」(抽出したい範囲）を指定するため、「値段」列をドラッグして指定します。
必要に応じて絶対参照の「$」を指定してください。
=XLOOKUP(H3&I3&J3,$E$3:$E$10,$F$3:$F$10
手順３．３つの複数条件の抽出結果を確認
関数を確定すると、「B店」の「ブラシ」の「一般価格」の値段「500」円を抽出することが出来ます。
このようにキー列を設ければ、４つの複数条件でも５つの複数条件でも利用することが可能です。
HLOOKUP関数、VLOOKUP関数でも複数条件は可能
上の見出しでXLOOKUP関数で複数条件の使い方を紹介しました。
同様に、VLOOKUP関数やHLOOKUP関数でも複数条件を利用することが出来ます。
以下のリンク記事で手順を紹介していますので、合わせて参考にしてみてください。
VLOOKUP関数の複数条件の使い方
HLOOKUP関数の複数条件の使い方
「キー」列を追加せずに複数条件を指定する方法
XLOOKUP関数で複数条件を指定する場合、「キー」列を追加する必要があるので表のサイズが変わってしまいます。
そんな時は、XLOOKUP関数を利用せず、INDEX関数とMATCH関数を組み合わせて利用することで、表の中から任意の値を抽出することが可能です。
INDEX関数とMATCH関数の使い方
INDEX関数（インデックス）は、指定された行と列が交差する位置にあるセルの値を抽出する関数です。
INDEX関数の構文は下のとおりです。
＝INDEX(配列,行番号,[列番号])
配列は検索範囲のことで、行番号と列番号は数値の入力されたセルを指定するか、直接関数の中に数値を指定します。
実際に下の商品リスト表でINDEX関数を使ってみます。
F11セルに下のINDEX関数が挿入されています。
=INDEX(B4:D11,F6,G6)
表(B4:D11)の中から、５行目(F6セル)の３列目(G6セル)の値(3,500円）を抽出することが出来ます。
MATCH 関数(マッチ)は、検索範囲の中から検索値を検索し、その範囲内での検索値の相対的な位置を返します。
＝MATCH(検索値, 検索範囲, [照合の型])
検索値： 検索範囲の中で照合する値を指定します。
検索範囲： 検索するセルの範囲を指定します。
照合の型 ： ”-1”、”0”、”1 ”の数値のいずれかを指定しますが、”1 ”の場合は省略可能です。今回は、「検索値に一致する値のみが検索の対象」とする”0”を利用します。
実際に、下の図でMATCH関数の使用例を説明します。
B5セルに下のMATCH関数が挿入されています。
=MATCH($C$2,B2:E2,0)
検索値のC2セルは、検索範囲のB2:E2の左から２番目にあるので、関数の結果は「２」と表示されます。
MATCH関数の詳しい使い方は以下の記事を参考にしてみてください。
INDEX関数とMATCH関数を組み合わせた抽出方法
それでは、下の商品リストを例に、「B店」の「ブラシ」の値段を抽出するためにINDEX関数とMATCH関数を使って、複数条件で値を抽出する方法を解説します。
INDEX関数が『XLOOKUP関数の代わりに使う関数』で、MATCH関数が『検索範囲から抽出する位置を指定する』役割を果たします。
手順１．表の枠外に検索条件を書き出す
表の枠外のセルに複数の検索条件を入力します。今回は、条件の１つ目が店舗名が「B店」で、条件の２つ目が「ブラシ」となります。
手順２．INDEX関数を挿入する
INDEX関数を挿入し、一つ目の引数「配列」を指定します。「配列」は表全体のことを指しますので、列項目を除くB34:D9を指定します。
=INDEX(B3:D9,
手順３．INDEX関数の中にMACH関数を挿入する
INDEX関数の２つ目の引数「行番号」を指定するために、以下のMATCH関数を挿入します。
=INDEX(B3:D9,MATCH(F3&G3,B3:B9&C3:C9,0),
１つ目の引数「検索値」は手順１で作成した複数条件のセルを指定します。
２つ目の引数「検索範囲」は、B列の範囲(B3:B9)とC列の範囲(C3:C9)を「＆」演算子で結合して指定します。
３つ目の引数「検索の型」は「0」を指定してください。
手順４．INDEX関数の３つ目の引数「列番号」を指定
INDEX関数の３つ目の引数「列番号」を指定します。値段は表の３列目に存在するため、”３”と指定します。
※「Enter」キーは押さず、次の手順５を操作してください。Office365のバージョンを利用している方は、「Enter」キーを押しても大丈夫です。
=INDEX(B3:D9,MATCH(F3&G3,B3:B9&C3:C9,0),3)
手順５．「Ctrl＋Shift＋Enter」キーで確定
関数を確定させるため、「Ctrl」キー＋「Shift」を押しながら「Enter」キーを押してください。
下の図のように「B店」の「ブラシ」の値段「600」円が抽出することが出来ます。
また、数式バーを見た時に、{ }で関数全体が囲まれていれば関数が正常に完成しています。
