本業をいろいろと優先していて，最近は完全に放置状態となってしまっていました。
ご心配おかけして申し訳ありません。
さて，今回はExcelを使用するうえで覚えて損のない関数，「XLOOKUP」のご紹介です。
先日紹介した「VLOOKUP」の完全上位互換関数とも言えるかと思います。
ぜひ理解して，資料作成等の効率化を図っていただけたら幸いです。
前提知識
XLOOKUP
表や指定した範囲内にある，条件と合致するデータを抽出する関数です。
本関数はExcel2021から登場しました。(Excel2021よりも前のバージョンでは使用できません。)
類似関数に以下があります。
- VLOOKUP
- HLOOKUP
- XLOOKUP ←今回の主題
検索方法にそれぞれ違いがあるものの，条件合致のデータを抽出する意味では同じ関数です。
XLOOKUPは，検索値に対して上下左右方向へデータを検索して抽出します。
イツキ「VLOOKUP」，「HLOOKUP」よりも高度な検索が可能だよ！
関数の使い方
構文
「XLOOKUP」関数の構文は以下の通りです。
=XLOOKUP([検索値],[検索範囲],[戻り範囲],([見つからない場合],[一致モード],[検索モード]))
[見つからない場合],[一致モード],[検索モード]は省略可能です。
「VLOOKUP」の時と同じ，野菜データを抽出してみます。
C12セル(「商品番号」)に商品番号を入力して，D12(「商品名」)～G12セル(「備考」)へ商品番号に応じたデータを抽出してみましょう。
入力前
入力後
検索値
VLOOKUPと同様，検索の要となる値を設定します。
今回は複合参照で「$C12」を入力します。
検索範囲
「検索値」の入力してある範囲を指定します。
今回はC列(C3～C9の範囲)に入力してあるため，絶対参照で「$C$2:$C$9」を指定します。
戻り範囲
抽出したいデータの入力してある範囲を指定します。
「商品名」を抽出するため，複合参照で「D$2:D$9」を指定します。
見つからない場合
検索した結果，データが見つからなかった場合の値を指定します。
今回は「-」を出力するよう指定します。
一致モード
「検索範囲」で検索する「検索値」の一致モードを指定します。
- 0 ：完全一致
「検索値」の値と完全一致のデータを抽出する。
- -1：完全一致または次に小さい項目
「検索値」の値と完全一致もしくは「検索値」に対して次に小さい項目を抽出する。
数値を「検索値」としてデータ抽出したい場合等で有用な場合がある。
- 1 ：完全一致または次に大きい項目
「検索値」の値と完全一致もしくは「検索値」に対して次に大きい項目を抽出する。
数値を「検索値」としてデータ抽出したい場合等で有用な場合がある。
- 2 ：ワイルドカード文字との一致
「検索値」の値と似ているデータを抽出する。
あいまい検索をする場合等で有用な場合がある。
省略した場合は「0」の「完全一致」となります。
今回は「0」の「完全一致」を指定します。
検索モード
「検索範囲」にある「検索値」の検索方向を指定します。
「検索値」が複数ある(重複している)場合に役立ちます。
- 1：先頭から末尾へ検索
「検索範囲」の先頭行もしくは先頭列から末尾方向へ検索する。
（上→下もしくは左→右方向）
- -1：末尾から先頭へ検索
「検索範囲」の最終行もしくは最終列から先頭方向へ検索する。
（下→上もしくは右→左方向）
- 2：バイナリ検索(昇順で並べ替え)
「検索範囲」をあらかじめ昇順に並べ替えたうえで，先頭から末尾方向へ検索する。
- -2：バイナリ検索(降順で並べ替え)
「検索範囲」をあらかじめ降順に並べ替えたうえで，先頭から末尾方向へ検索する。
省略した場合は「1」の「先頭から末尾へ検索」となります。
今回は「1」の「先頭から末尾へ検索」を指定します。
実証
早速「商品番号」(C12セル)へ値を入力して確認してみます。
入力前は「見つからない場合」で指定した値(「-」)が出ています。
入力前
入力後
「商品番号」(「A045」)に対する「商品名」(「キャベツ」)を抽出することができました！
では，残りの「単価(円)」列以降に，「商品名」(D12セル)で設定した関数をコピーします。
「商品番号」(「A045」)に対するデータを，すべて正しく抽出することができました！
最終状態
最終的に，B11～G12の範囲にある表には以下が入力してある状態となっています。
| # | 商品番号 | 商品名 | 単価(円) | 在庫数(個) | 備考 |
|---|---|---|---|---|---|
| =ROW()-ROW($B$11) | (関数無し) | =XLOOKUP($C12,$C$2:$C$9,D$2:D$9,”-“,0,1) | =XLOOKUP($C12,$C$2:$C$9,E$2:E$9,”-“,0,1) | =XLOOKUP($C12,$C$2:$C$9,F$2:F$9,”-“,0,1) | =XLOOKUP($C12,$C$2:$C$9,G$2:G$9,”-“,0,1) |
イツキ「戻り範囲」が違うだけで，他はすべて同じだよ！
まとめ
今回はExcelにある関数の一種，「XLOOKUP」について説明しました。
「VLOOKUP」(左→右への検索)や「HLOOKUP」(上→下への検索)ではなく，データ抽出は「XLOOKUP」の使用が当然となる日も遠くなさそうな気がします。
関数を駆使して，様々な資料作成に役立ててください！
