【Excel】XLOOKUP関数は何がすごいのか！？VLOOKUP関数との違いや活用例を徹底解説

---

XLOOKUP関数は、今までのVLOOKUP関数、HLOOKUP関数よりも柔軟な検索ができる関数として追加されました。

VLOOKUP(検索値,範囲,列番号,[検索方法])

検索値で指定した値を、範囲の左端列から検索する。検索値と一致するデータの左から列番号目の値を抽出し表示する。検索方法に「FALSE」を指定すれば、完全に一致するデータを検索する。「TRUE」を指定すれば、「～以上」など数値を区切って該当する区分を特定する。

HLOOKUP(検索値,範囲,行番号,[検索方法])

検索値で指定した値を、範囲の上端行から検索する。検索値と一致するデータの上から行番号目の値を抽出し表示する。検索方法に「FALSE」を指定すれば、完全に一致するデータを検索する。「TRUE」を指定すれば、「～以上」など数値を区切って該当する区分を特定する。

XLOOKUP関数では、検索値で指定した値を検索範囲から探し、同じ位置にある戻り範囲のデータを表示させます。検索範囲と戻り範囲は縦方向・横方向どちらも指定できます。

XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])

「検索値」で指定した値を、「検索範囲」から検索。検索値と一致するデータと同じ位置（列または行）にある「戻り範囲」のデータを表示させる。 「[見つからない場合]」「[一致モード]」「[検索モード]」は省略可能。

VLOOKUP関数、HLOOKUP関数との違い

XLOOKUP関数とVLOOKUP関数、HLOOKUP関数の違いをまとめると下表のようになります。機能的な観点では、XLOOKUP関数はVLOOKUP関数、HLOOKUP関数の機能を内包しているといえます。ただし、バージョンによってはXLOOKUP関数を使用できないので、社内や協業先の作業環境を確認の上、使用することをおすすめします。

機能	VLOOKUP	HLOOKUP	XLOOKUP
検索方向	垂直	水平	垂直・水平
検索列・行	左端の列のみ	上端の行のみ	任意の列・行
戻り値の指定	列番号	行番号	任意の列・行
標準の照合	近似一致		完全一致
エラー処理	IFERROR関数、IFNA関数などによる処理が必要		見つからない場合の引数を内蔵
検索方法	上から検索	左から検索	複数のオプション
構文の直感性	検索範囲・戻り範囲がわかりにくい		検索範囲・戻り範囲がわかりやすい

以下では、特徴的なポイントについて解説していきます。

縦方向の表・横方向の表から検索できる

VLOOKUP関数は垂直方向、HLOOKUP関数は水平方向のみの検索しかできません。一方でXLOOKUP関数は垂直・水平の両方向の検索が可能になります。つまり、縦方向の表・横方向の表いずれのデータからも検索することができます。

たとえば、縦方向の商品リストから商品名に基づいて価格を検索するには以下のように数式を入力します。検索値、検索範囲、戻り範囲の3つを指定すれば、検索値に対応するデータを表示させることができます。

=XLOOKUP(F3,$B$3:$B$12,$C$3:$C$12)

たとえば、商品リストの商品Dの価格「¥2,400」が正しく表示されていることがわかります。

横方向の表からデータを検索する場合、従来はHLOOKUP関数を使用する必要がありましたが、XLOOKUP関数でも検索ができます。

=XLOOKUP(B8,$B$3:$K$3,$B$4:$K$4)

縦方向の表と同じように、商品リストの商品Dの価格「¥2,400」が正しく表示されていることがわかります。

このようにXLOOKUP関数は垂直・水平の両方向の検索ができるため、VLOOKUP関数、HLOOKUP関数を使い分ける必要がなくなります。

検索範囲・戻り範囲を柔軟に指定できる

XLOOKUP関数の大きな特徴として、検索範囲・戻り範囲を個別に指定できるという点です。

VLOOKUP関数やHLOOKUP関数では検索する列は左端・上端でないといけないという制約がありました。たとえば、VLOOKUP関数を使って以下のような商品リストで商品名から商品番号を表示させることはできません。検索対象である商品名の列が左端にないからです。

XLOOKUP関数では、検索範囲と戻り範囲を個別に指定します。そのため、商品名の列が一番左になくとも商品番号を表示させることができます。

=XLOOKUP(F3,$B$3:$B$12,$A$3:$A$12)

戻り範囲も独立して指定するため、必ずしも検索範囲と連続している必要はありません。

=XLOOKUP(F3,$A$3:$A$12,$C$3:$C$12)

仮に商品リストに列を追加したとしても常に正しい結果が表示されます。

このようにXLOOKUP関数では、検索範囲・戻り範囲を個別に指定できるため、表の項目の順番にかかわらず、正しい結果を表示させることができます。

エラー処理が簡単にできる

XLOOKUP関数には、「見つからない場合」という引数が用意されており、検索値が見つからなかった場合に表示するメッセージをカスタマイズできます。VLOOKUP関数やHLOOKUP関数で同様の処理を行うには、IFERROR関数やIFNA関数を別途使用する必要があります。

たとえば、商品リストから商品名に基づき、価格を表示させる場合を考えます。商品リストにない商品名が検索値にある場合は、「該当の商品がありません」と表示させます。

=XLOOKUP(F3,$B$3:$B$12,$C$3:$C$12,”該当の商品がありません”)

「商品K」という商品名は商品リストに存在しないため、「該当の商品がありません」というメッセージが表示されます。

このようにXLOOKUP関数では、エラー処理が標準で備わっているため、シンプルな数式でエラーメッセージを表示させることができます。

「次に小さい値」「次に大きい値」を検索できる

XLOOKUP関数の標準の照合方法は完全一致で、検索値と全く同じ値を検索します。照合方法を変えることで、数値データを検索値とする場合、完全一致するデータがないときに「次に小さい値」「次に大きい値」を検索することができます。

たとえば、商品リストから「¥2,000以下で一番高い商品」を表示させたい場合、「一致モード」に「-1」を指定します。

=XLOOKUP(F3,$C$3:$C$12,$B$3:$B$12,,-1)

これで価格「\1,900」の「商品I」が表示されます。これは、「¥2,000以下で一番高い商品」の条件に合致します。たとえば、予算が決まっている場合に、どの商品なら購入可能か調べる場合に役に立ちます。

逆に「一致モード」に「1」を指定すれば、「¥2,000以上で一番安い商品」を検索することができます。

=XLOOKUP(F3,$C$3:$C$12,$B$3:$B$12,,1)

条件に該当する価格「\2,300」の「商品B」が表示されます。

このようにXLOOKUP関数では、完全一致しない場合、近似値を「次に小さい値」と「次に大きい値」を指定できるようになりました。なお、VLOOKUP関数でも同様の検索はできますが、事前にデータが降順・昇順に並べ替える必要があるという手間があります。

検索方向を指定できる、複数の列・行を表示できる、複数の条件で検索できる…