［Excel 2021以降］
Excelの XLOOKUP（エックス・ルックアップ）関数の使い方を紹介します。
XLOOKUP関数はExcel 2021の新関数で、表引き用の万能関数です。これまで VLOOKUP関数、HLOOKUP関数、INDEX ＋ MATCH関数などで行ってきたさまざまな表引きを、XLOOKUP関数 1 つで行えます。
ここでは、XLOOKUP関数による表引きのうち、完全一致検索の表引きを紹介します。近似一致検索の表引きやスピルの利用については、下記のページを参照してください。
目次
書式
さまざまな条件を指定してデータを取り出す
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
［検索範囲］から［検索値］を探し、見つかった場合は［戻り値範囲］の該当する位置から値を返します。見つからなかった場合は、［見つからない場合］を返します。［一致モード］で一致の判断基準、［検索モード］で検索の方向を指定できます。
■引数
| 引数 | 説明 |
|---|---|
| 検索値 | 検索する値を指定する。全角文字と半角文字、アルファベットの大文字と小文字は区別されず同じ文字と見なされる。 |
| 検索範囲 | ［検索値］を検索する。1列または 1行の範囲を指定する。 |
| 戻り値範囲 | 値を取り出す範囲を指定する。［検索範囲］が 1列の場合は［検索範囲］と同じ行数、1行の場合は同じ列数の範囲を指定すること。 |
| 見つからない場合 | ［検索範囲］の中に［検索値］が見つからない場合に返す値を指定する。省略した場合は、エラー値「#N/A」が返される。 |
| 一致モード | 検索の際に「一致」と判断する基準を 表A の数値で指定する。省略した場合は、完全一致で検索される。 |
| 検索モード | 検索する方向を 表B の数値で指定する。省略した場合は、先頭から末尾に向かって検索される。 |
■表A 引数［一致モード］の設定値
| 設定値 | 説明 |
|---|---|
| 0 または省略 | 完全一致 |
| -1 | 完全一致または次に小さい項目 |
| 1 | 完全一致または次に大きい項目 |
| 2 | ワイルドカード文字との一致 |
■表B 引数［検索モード］の設定値
| 設定値 | 説明 |
|---|---|
| 1 または省略 | 先頭から末尾へ検索 |
| -1 | 末尾から先頭へ検索 |
| 2 | バイナリ検索（［検索範囲］が昇順の場合） |
| -2 | バイナリ検索（［検索範囲］が降順の場合） |
XLOOKUP関数は、引数の指定次第で次のようなさまざまな表引きが行えます。
- 縦方向に検索して表引き … VLOOKUP関数の処理に相当
- 横方向に検索して表引き … HLOOKUP関数の処理に相当
- 表引き時のエラー処理 … IFERROR ＋ VLOOKUP / HLOOKUP関数の処理に相当
- 表の逆引き … INDEX ＋ MATCH関数の処理に相当
使用例1…商品ID から商品名を表引き（VLOOKUP関数に相当）
XLOOKUP関数の引数［検索範囲］に1列の範囲を指定すると、VLOOKUP関数と同様の縦方向の検索を行えます。
図1のセルB3では、商品ID の「G-102」を［検索値］として商品リストから商品名を表引きしています。［検索範囲］に商品リストの「商品ID」欄、［戻り値範囲］に「商品名」欄を指定します。［一致モード］の指定を省略したので、完全一致検索が行われます。
「G-102」は［検索範囲］の 4番目にあるので、［戻り値範囲］の4番目の値である「ジェルG詰替」が取り出されます。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
セルB3 | =XLOOKUP(B2,D3:D8,E3:E8)
図1 セルB3にXLOOKUP関数を入力して、商品IDが「G-102」の商品名を商品リストから取り出す。
メモ
メモ
メモ
使用例2…商品名から商品IDを逆引き（INDEX＋MATCH関数に相当）
XLOOKUP関数では逆引きも簡単に行えます。
図2のセルB3では、商品名の「ジェルG詰替」を［検索値］として商品リストから商品ID を逆引きしています。図1とは逆に、［検索範囲］に商品リストの「商品名」欄、［戻り値範囲］に「商品ID」欄を指定します。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
セルB3 | =XLOOKUP(B2,E3:E8,D3:D8)
図2 セルB3にXLOOKUP関数を入力して、商品名が「ジェルG詰替」の商品ID を商品リストから取り出す。
メモ
使用例3…横方向に検索して表引き（HLOOKUP関数に相当）
XLOOKUP関数の引数［検索範囲］に1行の範囲を指定すると、HLOOKUP関数と同様の横方向の検索を行えます。
図3のセルB3では、「7月」を［検索値］として月別売上表から売上高を表引きしています。「7月」は［検索範囲］の 4番目にあるので、［戻り値範囲］の4番目の値である「3826」が取り出されます。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
セルB3 | =XLOOKUP(B2,B6:G6,B7:G7)
図3 セルB3にXLOOKUP関数を入力して、「7月」の売上高を月別売上表から取り出す。
メモ
使用例4…見つからない場合の値を指定（IFERROR＋VLOOKUP関数に相当）
図4のセルB3では、図1と同様に商品ID から商品名を取り出しています。［検索値］に指定した商品ID が見つからない場合、XLOOKUP関数はエラー値「#N/A」を返します。ここでは引数［見つからない場合］に「"該当なし"」を指定して、「#N/A」エラーが表示されないようにしました。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
セルB3 | =XLOOKUP(B2,D3:D8,E3:E8,"該当なし")
図4 XLOOKUP関数の引数［見つからない場合］を使用して、［検索値］が見つからない場合に「該当なし」と表示されるようにした。
メモ
そのほかの使用例
XLOOKUP関数の使用例は、下記でも紹介しています。
各引数について
検索値、検索範囲、戻り値範囲
［検索範囲］には、1列または 1行の範囲を指定します。複数行×複数列を指定すると「#VALUE!」エラーになるので注意してください。
［検索値］に複数のセルを指定したり、［戻り値範囲］に複数行×複数列の範囲を指定すると、スピルにより複数の表引きを一気に行えます。詳しくは下記を参照してください。
XLOOKUP関数 ● さまざまな条件を指定してデータを取り出す (3)スピルの利用
一致モード
第5引数の［一致モード］では、検索時に一致と判断する基準を指定します。使用例1～使用例4 で見てきたとおり、完全一致検索をしたい場合は省略できます。近似一致検索をしたい場合は、下記を参照してください。
XLOOKUP関数 ● さまざまな条件を指定してデータを取り出す (2)近似一致検索の場合
検索モード
第6引数の［検索モード］では、検索の方向を指定します。
| 設定値 | 説明 |
|---|---|
| 1 または省略 | 先頭から末尾へ検索 |
| -1 | 末尾から先頭へ検索 |
| 2 | バイナリ検索（［検索範囲］が昇順の場合） |
| -2 | バイナリ検索（［検索範囲］が降順の場合） |
省略した場合は先頭から末尾に向かって検索が行われますが、「-1」を指定した場合は逆方向になります。［検索範囲］に該当データが1つしかない場合、どの方向で検索しても同じ結果が得られます。しかし、該当データが複数ある場合、得られる結果が変わります。
例えば、日付順に並んだ売上表から顧客名を［検索値］として検索する場合、［検索モード］に「1（または省略）」を指定すると、図5のように初回の取引データがヒットします。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
セルB3 | =XLOOKUP(B2,E3:E9,D3:D9,"該当なし",0,1)
図5 ［検索モード］に「1」を指定すると、セルE3 → セルE9 の方向に検索が行われ、最初に見つかったデータの「2021/4/12」が返される。
［検索モード］に「-1」を指定した場合は、図6のように直近の取引データがヒットします。
セルB3 | =XLOOKUP(B2,E3:E9,D3:D9,"該当なし",0,-1)
図6 ［検索モード］に「-1」を指定すると、セルE9 → セルE3 の方向に検索が行われ、最初に見つかったデータの「2021/6/4」が返される。
［検索範囲］を昇順か降順に並べ替えたうえで［検索モード］に「2」か「-2」を指定すると、「バイナリ検索」という高速検索が行われます。検索対象の表が大規模の場合、表引きに時間がかかります。そんなときに便利なオプションです。なお、このページで紹介した小規模な表引きではバイナリ検索の効果を体感できません。
メモ