Excel(エクセル)のXLOOKUP関数は、表または範囲を検索し、最初に見つかった行の指定した列の値を返す関数です。
同じような機能を持つ関数にVLOOKUP関数がありますが、XLOOKUPの方がより細かな指定ができるようになっています。
検索にワイルドカードを指定できるのもこのXLOOKUP関数の特徴です。
それでは、このXLOOKUP関数の内容を解説します。
XLOOKUP関数は、Excel 2016および Excel 2019では使えない可能性があります。
構文
XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
| 検索値 | 必須 | 検索する値を指定します |
| 検索範囲 | 必須 | 検索する配列または範囲を指定します |
| 戻り範囲 | 必須 | 返す配列または範囲を指定します |
| 見つからない場合 | 省略可 | 一致が見つからない場合の戻り値を指定します 指定しない場合は#N/Aが指定されます |
| 一致モード | 省略可 | 一致の種類を指定します 0：見つからない場合は#N/Aを返します -1：見つからない場合は次の小さな値を返します 1：見つからない場合は次の大きな値を返します 2：ワイルドカードの一致を行います 指定しない場合は0が指定されます |
| 検索モード | 省略可 | 1：先頭の項目から検索を実行します -1：末尾の項目から逆方向に検索を実行します 2：昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合は無効な結果を返します -2：降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合は無効な結果を返します 指定しない場合は1が指定されます |
使用例
サンプルデータ| Ａ | Ｂ | Ｃ | |
|---|---|---|---|
| １ | 商品コード | 商品名 | 金額 |
| ２ | 1001 | みかん | 100 |
| ３ | 1002 | 赤りんご | 110 |
| ４ | 1003 | 青りんご | 120 |
| ５ | 1005 | いちご | 150 |
単純な検索
=XLOOKUP( 1001, A2:A5, B2:B5)
解説
＝Ａ２～Ａ５の中から1001と一致するものを検索し、見つかった行のＢ２～Ｂ５の値を取得する
＝1001～1005の中から1001と一致するものを検索し、見つかった行の"みかん"～"いちご"の値を取得する
＝1001の行の”みかん”を取得する
＝みかん
見つからない場合の戻り値を指定
=XLOOKUP( 1004, A2:A5, B2:B5, "なし")
解説
＝Ａ２～Ａ５の中から1004と一致するものを検索し、見つかった行のＢ２～Ｂ５の値を取得する
＝1001～1005の中から1004と一致するものを検索し、見つかった行の"みかん"～"いちご"の値を取得する
＝1004と一致するものなし
＝「見つからない場合」に”なし”が指定されている
＝なし
見つからない場合は次の小さな値を指定
=XLOOKUP( 1004, A2:A5, B2:B5, "なし", -1)
解説
＝Ａ２～Ａ５の中から1004と一致するものを検索し、見つかった行のＢ２～Ｂ５の値を取得する
＝1001～1005の中から1004と一致するものを検索し、見つかった行の"みかん"～"いちご"の値を取得する
＝1004と一致するものなし
＝1004の次に小さな値の行を検索
＝1003の行の”青りんご”を取得する
＝青りんご
見つからない場合は次の大きな値を指定
=XLOOKUP( 1004, A2:A5, B2:B5, "なし", 1)
解説
＝Ａ２～Ａ５の中から1004と一致するものを検索し、見つかった行のＢ２～Ｂ５の値を取得する
＝1001～1005の中から1004と一致するものを検索し、見つかった行の"みかん"～"いちご"の値を取得する
＝1004と一致するものなし
＝1004の次に大きな値の行を検索
＝1005の行の”いちご”を取得する
＝いちご
ワイルドカード検索を指定
検索値にワイルドカードを指定することができます。
ワイルドカード文字の”？”は任意の 1 文字を、”＊”は 1 文字以上の任意の文字列を表します。
=XLOOKUP( "*りんご", B2:B5, C2:C5, "なし", 2)
解説
＝Ｂ２～Ｂ５の中から「任意の文字+"りんご"」と一致するものを検索し、見つかった行のＣ２～Ｃ５の値を取得する
＝”みかん”～"いちご"の中から「任意の文字+"りんご"」と一致するものを検索し、見つかった行の100～150の値を取得する
＝"赤りんご"と"青りんご"が条件に一致する
＝先頭の項目から検索するため、”赤りんご”が最初に一致する
＝"赤りんご"の110を取得する
＝110
逆方向からの検索を指定
=XLOOKUP( "*りんご", B2:B5, C2:C5, "なし", 2, -1)
解説
＝Ｂ２～Ｂ５の中から「任意の文字+"りんご"」と一致するものを検索し、見つかった行のＣ２～Ｃ５の値を取得する
＝”みかん”～"いちご"の中から「任意の文字+"りんご"」と一致するものを検索し、見つかった行の100～150の値を取得する
＝"赤りんご"と"青りんご"が条件に一致する
＝末尾の項目から逆方向に検索するため、”青りんご”が最初に一致する
＝"青りんご"の120を取得する
＝120
おわりに
いかがでしたでしょうか？
XLOOKUP関数は、条件に一致した行の値を取得する場合や検索にワイルドカードを指定したい場合に有効です。
Excel関数を有効に使えば、業務を効率化することができるようになるでしょう。
一度使ってみてはいかがでしょうか。
のXLOOKUP関数は、表または範囲を検索し、最初に見つかった行の指定した列の値を返す関数です。同じような機能を持つ関数にVLOOKUP関数がありますが、XLOOKUPの方がより){kind=link}