1. よねさんのWordとExcelの小部屋 » 
2. エクセル関数一覧表 » 
3. XLOOKUP関数の使い方

XLOOKUP関数の使い方:Excel基本講座

表や範囲から行ごとに情報を検索する　　　topへ
　エックス ルックアップ
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])

• XLOOKUP関数はLOOKUP関数系の新しい関数で、VLOOKUP関数やHLOOKUP関数の後継となるものと思われます。
  スピルが使用できる関数となっています。
  XLOOKUP関数を取り急ぎ紹介していますが、今後このページで詳しい説明を追加していこうと考えています。
• Excel for Office365 (Excel2016,サブスクリプション版) でXLOOKUP関数が使えるようになりました。(このページは2020年2月3日に書いています)
  ちなみに、Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
• このページで使用している画像は Excel for the webのものを使用していますことご了解ください。
  なお、Office Insiderに参加すると、最新の機能が使用できる環境が得られます。Office Insider になる　をご覧ください。

XLOOKUP関数の構文 　　　topへ

• =XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
  引数の [　] はオプションとなり、指定しなくてもOKです。
• 検索値：参照する値、セル
• 検索範囲：検索する配列または範囲
• 戻り範囲：返す配列または範囲
• [見つからない場合]：有効な一致が見つからない場合は、[見つからない場合] に指定したテキストを返します。
• [一致モード]：一致の種類を指定します
  • 0 ：完全一致。(これが既定の設定です。) 見つからない場合は、#N/A が返されます。
  • -1 ： 完全一致。 見つからない場合は、次の小さなアイテムが返されます。
  • 1 ：完全一致。 見つからない場合は、次の大きなアイテムが返されます。
  • 2 ：＊、？、および 〜 が特別な意味を持つワイルドカードになります。
• [検索モード]：使用する検索モードを指定します。
  • 1 ：先頭の項目から検索を実行します。 これが既定の設定です。
  • -1：末尾の項目から逆方向に検索を実行します。
  • 2 ： 順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。
  • -2 ：降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。

XLOOKUP関数で1つの値を返す例 　　　topへ

• VLOOKUP関数と同じような使い方ですが、検索値が左端列に無くてもOKです。
  XLOOKUP関数の[一致モード]は既定で 0 (完全一致)となっています。

1. C4セルに　=XLOOKUP($B4,$H$4:$H$6,I$4:I$6,"")　と入力しています。
   検索値として品番(B4)を指定し、右側の商品一覧表の品番(H4:H6)から一致する同じ行の商品名(I4:I6)を検索しています。
   
   • VLOOKUP関数を使った場合は、=VLOOKUP(B4,$H$4:$J$6,2)　として求めることができます。
     引数の[検索方法]は商品一覧表の品番が昇順に並んでいるので省略しています。
     
   • XLOOKUP関数では[一致モード]を省略しても、既定の完全一致で検索されるので、商品一覧表の品番が昇順に並んでいるの必要はありません。
     
     
     VLOOKUP関数の場合、商品一覧表の品番が昇順でなかったら、=VLOOKUP(B4,$H$4:$J$6,2,FALSE)　とします。
     
2. VLOOKUP関数では指定できなかった例で、検索値が検索する表で右側にある例です。
   C4セルには　=XLOOKUP($C4,$I$4:$I$6,$H$4:$H$6,"")　と入力されています。
   

XLOOKUP関数を関数の挿入から使用する例 　　　topへ

• XLOOKUP関数を関数の挿入から行う方法です。

1. 関数(数式)を入力するセルを選択して、[関数の挿入]ボタンをクリックします。
   
2. 関数の挿入 ダイアログボックスで XLOOKUP を選択して、[OK]ボタンをクリックします。
   
3. Excel for the web では　XLOOUP関数の引数ダイアログボックスは表示されないようです。
   普通に、XLOOKUP関数が入力され、引数を入力する画面になります。
   関数のポップアップ ヒントを目安に関数を入力します。
   
4. オートフィルで数式をコピーします。
   

XLOOKUP関数で複数の値を返す例 　　　topへ

• VLOOKUP関数では1つの値を返すことができますが、XLOOKUP関数では複数の値を返すことができます。
• C4セルには　=XLOOKUP(B4,$H$4:$H$6,$I$4:$J$6,"")　と入力しています。
  D4セルは何も入力していません。(未入力です)
  注目は戻り範囲に　I4:J6　と複数列を指定しています。
  
  ↓C4セルを選択して下方向へオートフィルでコピーします。
  
• このような使い方をするときは注意が必要です。
  • XLOOKUP関数で商品名と単価を返そうとしているのですが、D6セルには既に値が入力されていました。
    C6セルに数式　=XLOOKUP(B6,$H$4:$H$6,$I$4:$J$6,"")　を入力すると、#スピル!　とエラーが表示されました。
    
    ↓
    #スピル! とは?　エラーインジケータ(緑色の三角マーク)をポイントすると、説明が表示されます。
    「スピル範囲が空白ではありません。」データをスピルするのに必要なセルが空白ではありません。
    と書いてあります。「スピル」なる単語が何なのか理解してないのですが、データを返すところが空白でないといけないというエラーのようです。
    
    ↓
    D6セルを[Delete]キーでクリアすると、XLOOKUP関数の返り値が表示されました。
    数式バーには C6セルの数式が薄く表示されており、このセルの値はXLOOKUP関数での結果というのがわかります。
    

XLOOKUP関数の一致モードの違い 　　　topへ

• XLOOKUP関数の[一致モード]：一致の種類を指定します
  • 0 ：完全一致。(これが既定の設定です。) 見つからない場合は、#N/A が返されます。
  • -1 ： 完全一致。 見つからない場合は、次の小さなアイテムが返されます。
  • 1 ：完全一致。 見つからない場合は、次の大きなアイテムが返されます。
  • 2 ：＊、？、および 〜 が特別な意味を持つワイルドカードになります。

一致モードが 0 , -1 , 1 の時の例

• 表引きのキーが昇順の場合(評価一覧の得点が昇順になっています)

  一致モード	動作
  0	完全一致しないと[見つからない場合]の「なし」が表示されます。
  -1	完全に一致する値がないときは、下の値が選ばれます。 つまり、D7セルの　50　の場合、一致するのがG4:G6に無いので、下の値 40 を選び、その同じ行のI列の値　△　が返されます。
  1	完全に一致する値がないときは、上の値が選ばれます。 つまり、E7セルの　50　の場合、一致するのがG4:G6に無いので、上の値 70 を選び、その同じ行のI列の値　○　が返されます。

  
  
  • なお、VLOOKUP関数の場合は、XLOOKUP関数の[一致モード]が -1 と同じ結果が得られます。
    (VLOOKUP関数では表引きのキーが昇順になっている必要があります)
    
• 表引きのキーが降順の場合(評価一覧の得点が降順になっています)
  結果は上記と同じでした。
  
• ちなみに、表引きのキーがランダムの場合(評価一覧の得点がランダムになっています)
  結果は上記と同じでした。
  

一致モードが 2 の時の例です

• [一致モード]が 2 の場合は、ワイルドカード文字を使った検索が可能になります。
  ここでは、該当するデータが複数ある場合に、データの先頭から、末尾からといった[検索モード]を使った例を示しています。
• E4セルには =XLOOKUP("*田",B4:B6,B4:C6,"なし",2,F3)　と入力されています。
  一致モードは 2 で、[検索モード]を1　として、先頭の項目から検索を実行した例です。
  
• E10セルには =XLOOKUP("*田",B4:B6,B4:C6,"なし",2,F8)　と入力されています。
  一致モードは 2 で、[検索モード]を -1　として、末尾の項目から逆方向に検索を実行した例です。
  

XLOOKUP関数でテーブルを使う例　　　topへ

• B2:H11をテーブルに変換しています。テーブル名は「テーブル1」としています。
  氏名を検索して国語の得点を返す数式は　=XLOOKUP($C15,テーブル1[氏名],テーブル1[国語])　となります。
  
• 氏名から国語〜合計の得点を表示する例です。
  数式は　=XLOOKUP(C15,テーブル1[氏名],テーブル1[[国語]:[合計]])　としました。
  戻り範囲に　テーブル1[[国語]:[合計]]　と複数列を指定するのがポイントです。
  

テーブルから2つのデータを取り出したい　　　topへ

• VLOOKUP関数の時にも2つの条件でデータを取り出したいといった要望がありました。
  XLOOKUP関数でも同様な要望があると想像されますが、スピルが使えるようになり、FILTER関数でこれが実現されます。
• フィルター関数で2つの条件でデータを取り出した例です。
  氏名が「河合」または「佐々木」のデータを取り出す例です。
  ここで使ったテーブルのテーブル名は「テーブル13」です。
  数式は　=FILTER(テーブル13,(テーブル13[氏名]=A15)+(テーブル13[氏名]=A16))　としました。
  • FILTER関数は　FILTER関数の使い方 をご覧ください。
  

• 「スピル」という機能のおかげで、VLOOKUP関数も機能アップしていました。(2020/02/22 記)

  • 従来のVLOOKUP関数で複数の検索値を検索することができてしまいました。(SPILLのおかげです)
    D15セル数式は　=VLOOKUP($C$15:$C$18,$C$3:$H$11,COLUMN(B2),FALSE)　としました。
    下図では、D15セルをオートフィルで、H15セルまで数式をコピーしています。
    
  • XLOOKUP関数では、D15セルに　=XLOOKUP($C$15:$C$18,$C$3:$C$11,D3:D11)　とします。
    下図では、D15セルをオートフィルで、H15セルまで数式をコピーしています。
    

スポンサードリンク

PageViewCounter

Since2006/2/27