ExcelのXLOOKUP関数とは？どうやって使うの？

Excelをよくお使いの方は、LOOKUP関数をよくお使いだと思います。LOOKUP関数は、1つの行や列を検索して、別の行や列にある特定の値を見つけるために使用します。

この関数は、Excelシートで多くのデータを扱うときに便利です。Excelでは、LOOKUP関数にはいくつかの種類があります。

この記事では、Microsoft ExcelのXLOOKUP関数について知っておくべきことを、役立つ例を交えてお伝えします。

 

XLOOKUP関数とは何ですか？

XLOOKUPは、HLOOKUP、VLOOKUP、LOOKUPなどの古い関数を置き換えるものです。垂直および水平方向のルックアップをサポートします。

つまり、XLOOKUP関数を使えば、与えられたデータセットの中から縦横にある値を素早く見つけ出し、別の行や列にある対応する値を返すことができるのです。

XLOOKUP関数を最大限に活用するには、さまざまな方法があります。XLOOKUPの理解が深まるような例題を見てみましょう。

 

XLOOKUP関数へのアクセス方法

残念ながら、XLOOKUPはOffice 2010、2013、2016、2019の各バージョンをお使いのユーザーにはご利用いただけません。Microsoft Office 365、Office 2021スイートでのみご利用いただけます。

お使いのコンピュータでMicrosoft 365またはOffice 2021スイートを使用していない場合、XLOOKUP機能にアクセスするにはMicrosoft 365またはOffice 2021にアップグレードする必要があるかもしれません。すでにMicrosoft 365をお使いの方は、Excelですでにこのオプションが有効になっています。XLOOKUPは、Office 365 Onlineでもご利用いただけます。

 

XLOOKUP関数の構文

XLOOKUP関数の構文は、VLOOKUPやHLOOKUPの構文と似ています。これらを使ったことがある方は、XLOOKUPを使うと便利だと思います。以下は、ExcelでのXLOOKUP関数の構文です。

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP関数は最大6つの引数をサポートしており、その値は以下の通りです。

lookup_value（必須）：探したい値。

lookup_array (必須): ルックアップ値を探すための配列。

return_array (必須): ルックアップの値が見つかったときに、値を取得して返したい配列です。

[if_not_found］（オプション）。マッチしたものが見つからなかった場合、返される値です。

[match_mode] (オプション)。この引数では、探しているマッチのタイプを指定できます。指定できる値はいくつかあります。

• 0 - 完全に一致するものを探し、値はlookup_arrayの値と完全に一致する必要があります。また、言及されていない場合は、デフォルトとして設定されます。
• -1 - 完全に一致するものを探し、見つかったら次の小さい値に戻ります。
• 1 - 完全に一致するものを探し、見つかったら次の大きな値に戻ります。
• 2 - ワイルドカードを使用して部分的なマッチングを行います。

[search_mode] (オプション)。lookup_arrayでのXLOOKUPの検索モードを指定するために使用します。同じ指定でも異なる値があります。

• 1 - 最初の項目から検索を行います。何も指定されていない場合はデフォルトで設定されています。
• -1 - 最後の項目から始まる逆引き検索を行います。
• 2 - データが昇順にソートされる必要がある場合、lookup_arrayでバイナリ検索を実行します。データがソートされていないと、エラーや間違った結果が出る可能性があります。
• -2 - データが降順でソートされる必要がある場合に、lookup_arrayでバイナリ検索を実行します。データがソートされていないと、エラーや間違った結果が出る可能性があります。

 

エクセルのXLOOKUP関数のメリット・デメリット

XLOOKUPは今でもVLOOKUPやINDEX/MATCHに比べてメリットがある関数です。しかし、デメリットもあります。

XLOOKUP関数の利点

• 縦にも横にも使えます。
• VLOOKUP関数やINDEX MATCH関数の4つの引数ではなく、3つの引数が必要です。
• 常にデフォルトでは完全に一致しています。
• ワイルドカードを使った部分一致検索が可能。
• 降順でのルックアップが可能
• INDEX MATCHで2つの関数を使用せず、1つの関数を使用します。

XLOOKUP関数のデメリット

• 任意の引数は、初心者には複雑に見えるかもしれません。
• 2つの範囲を選択する場合や、スプレッドシート内のセル数が多すぎる場合に時間がかかることがあります。
• ルックアップと配列の戻り値が同じ長さでない場合、エラーを返します。
• ルックアップとリターンの両方の範囲を覚えておく必要があります。

 

ExcelでXLOOKUP関数を使用する方法

XLOOKUP関数は、ExcelのLOOKUP関数と同様に動作します。XLOOKUPは、関数が動作するセル参照を選択するだけで使用できます。

また、上部にある「数式バー」ボックスを利用して、そこにXLOOKUP関数の構文を挿入することもできます。

 

XLOOKUP関数の使用例

混乱している方のために、以下の例で説明します。これらの例題を見れば、HLOOKUP関数がどのように機能するのかがよくわかるでしょう。

例1

このようなデータセット（下の画像）があり、FセルとGセルでマシューのサイエンスマークを取得するとします。

そして、そのデータを取得するために、F2セルで以下の「XLOOKUP関数」を使用します。

=XLOOKUP(F2,A2:A15,B2:B15)

そして、この式では、次のセルにMatthewのScienceスコアが表示されます。これは、ルックアップの値をF2としたためで、この場合はMatthewとなります。

例2

同じデータセットを使って、今度は名前を右側に表示してみましょう。ここでも、MatthewのScienceのスコアを探しています。

この場合に使用するXLOOKUP関数は次のとおりです。 

=XLOOKUP(F2,D2:D15,A2:A15)

この関数の結果のデータも同じになります。同じ結果であっても、ここではlookup_arrayとreturn_arrayの値が変わります。

XLOOKUP関数は、VLOOKUP関数の主要な問題点を解決します。行や列をどのようにでも移動させることができ、XLOOKUPは機能します。しかし、VLOOKUPはエラーを返してしまいます。

例3

また、縦横に複数の結果を同時に取得することもできます。つまり、Matthewの全科目のスコアを取得したい場合も可能です。今回のケースでのXLOOKUP関数は、次のようになります。

=XLOOKUP(F2,D2:D15,A2:C15)

この関数の結果は、同じになります。

この場合、XLOOKUP関数は水平方向と垂直方向の検索を行い、その結果は完全に正確なものでした。ここでは、引数の値を適宜修正しています。

 

VLOOKUPの代わりにXLOOKUPを使う

XLOOKUPは、ルックアップ関数に関しては大きな進歩を遂げています。XLOOKUP関数の唯一の欠点は、下位互換性がないことです。古いバージョンのMicrosoft Office Suiteでスプレッドシートを共有している場合、エラーが発生する可能性があります。

しかし、Office 365 Online から XLOOKUP にアクセスすることができます。XLOOKUP機能はまだ初期段階なので、Excelユーザーが導入するには時間がかかります。