XLOOKUP関数(エックスルックアップ)はVLOOKUP関数の利便性がUPした大変便利な関数です。
Microsoft365やExcel2021を利用しているユーザーは、このXLOOKUP関数を利用することが出来ます。
このXLOOKUP関数とIF関数を組み合わせて利用すると、さらにXLOOKUP関数の用途が広がります。
今回は、『XLOOKUP関数とIF関数を組み合わせて利用』することで『抽出値を別の値に変換して表示』する便利な利用方法を解説します。
VLOOKUP関数とIF関数の組み合わせる方法については、以下の記事を参考にしてください。
XLOOKUP関数とは
XLOOKUP関数の基礎について熟知されている方は、次の見出しまで読み飛ばしてください。
XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の両方の機能を兼ね備え、さらに大幅に機能が強化された新関数です。
XLOOKUP関数は、
指定した検索値に『当てはまる値』を、指定した検索範囲から縦方向や横方向で調べて、指定した位置から抽出することが出来る
関数です。
構文と引数
XLOOKUP関数の構文は下のとおりで、６つの引数が存在します。
＝XLOOKUP(検索値,検索範囲,戻り範囲, [見つからない場合] , [一致モード] , [検索モード] ）
一方、VLOOKUP関数の引数は４つです。
＝VLOOKUP(検索値,検索範囲,列番号,[検索方法]）
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関数の特徴は以下の記事で紹介していますので、興味のある方は覗いてみて下さい。
IF関数とは(おさらい）
IF関数についても使い方をおさらいしましょう。こちらも理解している方は読み飛ばしてください。
IF関数の構文は以下のように３つの引数で構成されています。
=IF(論理式,[値が真の場合],[値が偽の場合])
論理式に当てはまるものは、[値が真の場合]の結果を表示し、そうでない場合は[値が偽の場合]の結果を表示します。
条件フロー図で表すと以下のようになります。上の論理式の引数の文字色と下の図の色は同じ内容を表しています。
シンプルなIF関数の使い方を１つ紹介します。
下のテスト結果表のD列に以下のIF関数を使って、80点以上であれば”〇、そうでなければ”×”を表示しています。
＝IF(C3>＝80,”〇”,”×”)
IF関数を挿入することで、点数の大小で【〇】【×】を判定することが出来ます。
IF関数の詳しい使い方をさらに復習したい方は、以下の記事を参考にしてみてください。
XLOOKUP関数とIF関数を組み合わせると何が出来る？
使い方の実例を紹介する前に、『XLOOKUP関数とIF関数を組み合わせる』とはどういうことでしょうか？
組み合わせのパターンが２つあるので紹介します。
IF関数の論理式にXLOOKUP関数を利用
XLOOKUP関数は、『表を検索して情報を抽出する関数』です。
一方で、IF関数は『条件を指定して、セルの値によって他のセルの値を変換する関数』です。
IF関数の１つ目の引数「論理式」にXOOKUP関数を挿入することで、
『表を検索して情報を抽出し、その抽出値を条件によって別の値を変換する』
ことが出来ます。
下の図解で説明すると、上半分がXLOOKUP関数の領域で「表から値を抽出」します。
下半分がIF関数の領域で抽出された値に条件を設定して、条件に合うか合わないかで表示する結果を変換します。
IF関数の「真の値」と「偽の値」にXLOOKUP関数を利用
IF関数の２つ目の引数「真の値の場合」と3つ目の引数「偽の値の場合」にXLOOKUP関数を挿入することで、
『値が真の場合に表から検索値を抽出する条件と、値が偽の場合に表から検索値を抽出する条件を変える』
ことも出来ます。
下の図解で説明すると上半分がIF関数で、論理式の条件に合うかどうかで表示する値を変更します。
下半分がXLOOKUP関数の領域で「値が真の値」か「値が偽の値」かによって、XLOOKUP関数の数式を変えて抽出する値を変更します。
販売高を「〇」「×」で判定する方法(実例１)
それでは、IF関数とXLOOKUP関数を組み合わせた実例を紹介します。
下の販売高リストを使って、選択した商品番号の販売高が600(千円）以上であれば「〇」、600(千円）未満であれば「×」をG3セルに表示させます。
それでは、数式を作成する手順を紹介します。
手順１．IF関数を挿入
まずIF関数を挿入します。
＝IF(
手順２．論理式にXLOOKUP関数を挿入
商品番号の販売高をXLOOKUP関数で抽出するため、IF関数の論理式に以下のXLOOKUP関数を挿入します。
=IF(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)
手順３．論理式の条件を指定
XLOOKUP関数で抽出した値が600(千円）以上かどうかで判定するため「>=600」を入力します。
=IF(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=600,
手順４．IF関数の２つ目の引数を指定
販売高が600(千円）以上の真の値の場合（＝当てはまる場合）に「〇」と表示したいため、IF関数の２つ目の引数「値が真の場合」に「〇」を入力します。
=IF(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=600,"○",
手順５．IF関数の３つ目の引数を指定
販売高が600(千円）未満の偽の値の場合（＝当てはまらない場合）に「×」と表示したいため、IF関数の３つ目の引数「値が偽の場合」に「×」を入力します。
=IF(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=600,"○", "×" )
手順６．IF関数を確定
IF関数の第３引数を入力したら、「Enter」キーを押して関数を確定させてください。
そうすると、商品番号「35」の販売高は200(千円）と600(千円）未満のため、Ｇ４セルは「×」と表示されます。
販売高を「〇」「△」「×」で判定する方法(実例２)
IF関数とXLOOKUP関数を組み合わせた２つ目の使い方です。
今度は販売高の大小で「〇」「△」「×」を表示させます。
下の販売高リストを使って、選択した商品番号の販売高が700(千円）以上であれば「〇」、500(千円）以上700(千円)未満であれば「△」、500(千円)未満であれば「×」をG3セルに表示させます。
関数は長くなりますが、実例１で紹介したIF関数の[値が偽の場合]の中にもう一つのXLOOKUPが入ったIF関数を挿入することで「×」か「△」の分岐を設けます。
簡略化せずに数式で表すと下記のようになります。
緑色の下線部分が「×」か「△」の分岐のために設定した数式です。
=IF(XLOOKUP(F3,$B$2:$B$9,$D$2:$D$9)>=700,"○",IF(XLOOKUP(F3,$B$2:$B$9,$D$2:$D$9)<500,"×","△"))
商品番号「18」で判定した場合、販売高は500以上700千円未満の範囲なので「△」と判定されます。
商品番号「25」で判定した場合、販売高は500未満なので「×」と判定されます。
商品番号「35」で判定した場合、販売高は700以上なので「〇」と判定されます。
会員区分に合わせた値段を抽出する方法(実例３)
IF関数とXLOOKUP関数を組み合わせた３つ目の使い方を紹介します。
下の価格リストを使って、C12セルの「会員」と「非会員」に合わせて、該当する価格を表から抽出しD12セルに表示させます。
手順を紹介する前に作成する関数を紹介しておきます。
下の数式のように、IF関数の２つ目の引数「値が真の場合」にXLOOKUP関数を挿入します。(緑の下線部）
IF関数の３つ目の引数「値が偽の場合」に２つ目のIF関数を挿入し、２つ目のIF関数の「値が偽の場合」にXLOOKUP関数を挿入します。(赤の下線部）
=IF(C12="会員",XLOOKUP($B$12,$B$2:$B$8,$D$2:$D$8),IF(C12="非会員",XLOOKUP($B$12,$B$2:$B$8,$E$2:$E$8)))
手順１．IF関数の１つ目と２つ目の引数を入力
C12セルが「会員」だった場合に表内から会員価格をXLOOKUP関数で抽出するために以下の数式を作成します。
=IF(C12="会員",XLOOKUP($B$12,$B$2:$B$8,$D$2:$D$8),
手順２．IF関数の３つ目の引数を入力
次にC12セルが「非会員」だった場合に、表内から非会員価格を抽出するため２つ目のIF関数を挿入します。
=IF(C12="会員",XLOOKUP($B$12,$B$2:$B$8,$D$2:$D$8),IF(C12="非会員",XLOOKUP($B$12,$B$2:$B$8,$E$2:$E$8)))
手順３．IF関数を確定
「Enter」キーを押して関数を確定します。下図のように商品番号「25」の非会員価格1,100円が抽出することが出来ます。
商品番号「25」の会員価格1,000円も抽出することが出来ます。
ネスト（入れ子）を使うテクニック
エクセルで関数の中に関数を使うことをネスト（入れ子）と言います。
今回はIF関数の中にIF関数やXLOOKUP関数を挿入したので、このネストを使ったテクニックを紹介しました。
ネストに不慣れな方は、IF関数の中にIF関数を挿入する使い方を以下の記事で紹介していますので、ぜひ習得してみてください。