データ検索ならXLOOKUP関数で縦横に検索（IFERROR関数は不要）

Excel2021とMicrosoft 365 のExcelでは、［XLOOKUP（エックスルックアップ）関数］が使用できます。

これは、VLOOKUP（ブイルックアップ）関数の進化版です。VLOOKUP関数では、検索値が見つからない場合のエラー対策として、IFERROR関数と組み合わせていましたが、XLOOKUP関数では、その必要はありません。

IFERROR関数と組み合わせなくても、検索値が見つからない場合に戻す値は引数［見つからない場合］で指定できます。

そして、VLOOKUP関数では、検索値は表の左（1列目）でなければいけませんが、XLOOKUP関数ではその必要はありません。

XLOOKUP関数が使用できるかどうかを確認

XLOOKUP関数は、Excel2021とMicrosoft 365 のExcelで使用できます。

使用できるかを確認するには、［数式］タブの［関数ライブラリ］グループにある［検索/行列］をクリックして、［XLOOKUP］ が表示されるかを確認します。

または、数式バーに［=x］と入力して、関数候補の一覧に［XLOOKUP］と表示されれば使用できる環境です。

Microsoft 365のExcelで表示されなければ、アップデートしてみてください。

XLOOKUP関数の構文

［関数の引数］ダイアログボックスを表示してXLOOKUP関数について確認してみます。

数式バーに［=XLOOKUP］まで入力した後、キーボードから［Ctrl］+［A］、または［Shift］+［F3］を押すと表示できます。

XLOOKUPの［関数の引数］ダイアログボックスが表示されます。

引数が多くて面倒くさそうに見えますが、必須項目は上から［検索値］［検索範囲］［戻り範囲］の3つです。

下へスクロールすると、もう1個［検索モード］というのが現れます。

［キャンセル］ボタンをクリックすると、［関数の引数］ダイアログボックスも数式バーに入力した［=XLOOKUP］も消えます。

必須項目は上から3つです。

［検索値］を［検索範囲］から探して［戻り範囲］の値を返します。

• 検索値：検索する値を指定します。省略すると検索範囲に空白のセルを返します。
• 検索範囲：検索する1列、または1行の範囲を指定します。
• 戻り範囲：［検索値］で見つかった場合に戻す値の範囲を指定します。［検索範囲］に1列を指定した場合は［検索範囲］と同じ行数を指定します。［検索範囲］に1行を指定した場合は［検索範囲］と同じ列数を指定します。
• 見つからない場合：［検索値］が見つからない場合に返す値を指定します。省略すると、エラー［#N/A］が返されます。
• 一致モード：一致の種類を指定します。
• 検索モード：検索の向きを指定します。

一致モード
0	完全一致（既定です。）
-1	完全一致 見つからない場合は次の小さい項目
1	完全一致 見つからない場合は次の大きい項目
2	ワイルドカード文字との一致

検索モード
1	先頭から検索（既定です。）
-1	末尾から検索
2	昇順で並べ替えられた検索範囲をバイナリ検索（並べ替えられていない場合は無効な結果が返されます。）
-2	降順で並べ替えられた検索範囲をバイナリ検索（並べ替えられていない場合は無効な結果が返されます。）

以下は、Microsoftの解説ページです。

業務効率を大幅アップ! Excel XLOOKUP 関数の使い方早わかり解説 - Microsoft for business（Microsoft）

XLOOKUP 関数（Microsoft）

XLOOKUP関数でVLOOKUP関数の近似値検索結果を得るには

VLOOKUP関数の近似値検索に該当する検索をXLOOKUP関数で行います。

以下の記事では、VLOOKUP関数で近似値検索を行っています。

ここでは、上記記事で使用した同じ表を使って解説します。

通し番号を印刷した展示会の案内状があります。

その案内状をどの企業へ配布したか、また誰が招待したかが分かるように作成した管理表があります。

配布した案内状が少なければ、案内状の番号を目で追って探せますが、管理表が膨大になると探すのも一苦労です。

管理表の横に以下のような検索ボックスと戻り値を表示する枠を準備します。

［番号入力］のセル［F3］に［111］と入力しておきます。セル［G3］にXLOOKUP関数を入力していきます。

［数式］タブの［関数ライブラリ］グループから［検索/行列］-［XLOOKUP］をクリックして［関数の引数］ダイアログボックスを表示します。

または、数式バーに［=XLOOKUP］まで入力した後、キーボードから［Ctrl］+［A］を押して［関数の引数］ダイアログボックスを表示します。

そして、以下のように入力します。

引数の［一致モード］を［-1］にすると、検索値が見つからない場合は次に小さい項目が返されるので、VLOOKUP関数の近似値検索と同じ結果を得ることができます。

引数［見つからない場合］については、指定していません。

指定すると、［番号入力］のセル［F3］が空白の場合、戻り範囲の最後のセルが表示されてしまいます。

XLOOKUP関数の引数［見つからない場合］は、［一致モード］で［完全一致：0］にはとても便利ですが、こういう場合は当てはまりません。

［一致モード］が［1］では、検索値が見つからない場合は次に大きい項目が返されますので、この表の場合は空白になります。

検索値のセル［F3］が空白の場合に対処するのであれば、IF関数を使用します。

参考VLOOKUP関数では、検索値が空白の場合は、エラー［#N/A］となるので、IFERROR関数が使用できますが、XLOOKUP関数ではエラーとはなりませんので、IFERROR関数では対応できません。

配布担当者も求める

セル［H3］に配布担当者も表示したいと思います。こういう場合、XLOOKUP関数はとても便利です。

セル［G3］の［戻り範囲］を修正するだけです。

セル［H3］には、自動的に数式が入力されます。これをスピルと呼びます。数式を入力した以外のセルを［ゴースト］といいます。

ゴーストのセルでは、数式を編集したり削除はできません。

数式を編集、または削除する場合は、スピル範囲先頭のセルで行います。ここでは、セル［G3］になります。

VLOOKUP関数の場合

VLOOKUP関数で同じようにするには、以下のようになります。VLOOKUPの場合、引数［検索方法］の既定値が［TRUE］（近似値）なので、省略できます。

VLOOKUPのほうが簡単そうに見えますが、そんなことはありません。

VLOOKUP関数で近似値検索をする場合は、検索値の列を昇順で並べる必要があります。

配布担当者のセル［H3］にも結果を表示するには、セル［G3］の数式をコピーしたりし、再度、数式を入力しなければなりません。

数式をコピーするためにセル［G3］の数式に複合参照を使用したりします。

以下の記事で記しています。

数式をコピーして列番号を修正（配布担当者）

XLOOKUP関数の完全一致検索

配布担当者名で検索して、配布先会社を求められるようにします。

XLOOKUP関数の完全一致を使用します。また、検索列は左端（1列目）にする必要はありません。

セル［K3］に以下のように入力します。引数［一致モード］は、既定が［完全一致：0］なので省略可能です。

VLOOKUP関数では、検索値が見つからない場合エラー［#N/A］が表示されますので、回避するためには、IFEEROR関数を使用しなければなりません。

XLOOKUP関数では、引数［見つからない場合］で指定できるので、IFERROR関数は必要ありません。

ただし、省略すると、エラー［#N/A］が表示されます。

検索値セル［J3］に入力した値に一致するものがない場合は、引数［見つからない場合］で指定した文字列が表示されます。

セル［J3］が空白であっても［該当者なし］と表示されます。必要であれば、IF関数を組み合わせてください。

これもスピルを利用して番号を求めることができます。範囲を変更するだけです。

セル［K2] からセル［M2］を表の並びと同じに［番号from］［番号to］［配布先会社］と修正します。

そして、セル［K3］のXLOOKUP関数の［戻り範囲］を修正します。

セル［L3］とセル［M3］は、スピル範囲のゴーストです。

検索値セル［J3］に入力した値に一致するものがない場合は、セル［K3］にのみ引数［見つからない場合］で指定した文字列が表示されます。

参考Excel2021とMicrosoft 365のExcelでは、［UNIQUE関数］も使用できます。

入力規則のドロップダウンリストで重複しない値を表示したい場合などは、とても便利です。

もし、ここで解説した記事の担当者に重複した値があれば、セル［I3］にUNIQUE関数を使用して重複しない値を求めることができます。

セル［I3］に以下のように入力すると、自動的に下のセルまでスピルによって入力されます。

次に、セル［F3］に［データの入力規則］を使って、ドロップダウンリストから選択できるようにします。

［データの入力規則］ダイアログボックスの［設定］タブの［元の値］にスピル演算子を使用して、以下のように入力します。

これでD列の配布担当者に新しい担当者名が追加されたり、削除されても自動的に一意の値が表示されるようになります。

UNIQUE関数については、以下の記事で解説しています。

SORT関数とSORTBY関数もExcel2021とMicrosoft 365のExcelで使用できる新関数です。