【Excel】XLOOKUP関数で近似一致・完全一致を使い分ける方法

Excelでデータ検索をする際、条件に完全に一致するデータだけでなく、それに近いデータも見つけたい場面があります。例えば、点数に応じて評価を割り振ったり、金額の範囲で手数料を決定したりする場合です。しかし、従来のVLOOKUP関数では、近似一致の指定が少し扱いにくいという側面がありました。ExcelのXLOOKUP関数を使えば、この近似一致と完全一致の使い分けがより直感的かつ柔軟に行えます。この記事では、XLOOKUP関数で近似一致と完全一致を使い分ける具体的な方法を解説します。これにより、複雑な条件でのデータ検索や集計作業を効率化できるでしょう。

XLOOKUP関数における検索モードの仕組み

XLOOKUP関数は、検索対象の配列や範囲の中から、指定した条件に合う値を見つけ出す強力な関数です。その検索能力をさらに高めているのが、「検索モード(match_mode)」を指定できる点です。この引数を使うことで、検索対象のデータと検索値が完全に一致する必要があるのか、それとも近似値で良いのかを細かく制御できます。従来のVLOOKUP関数では、近似一致は検索範囲を昇順に並べ替える必要がありましたが、XLOOKUP関数ではその制約が緩和され、より柔軟な設定が可能になりました。

XLOOKUP関数には、主に4つの検索モードが用意されています。これらを理解し、状況に応じて使い分けることが、関数を最大限に活用する鍵となります。各モードは、関数の4番目の引数である「match_mode」に数値を指定することで切り替わります。デフォルトでは「0」が設定されており、これは「完全一致」を意味します。つまり、何も指定しない場合は、完全に一致する値のみが検索されます。

XLOOKUP関数で完全一致検索を行う手順

完全一致検索は、最も一般的で基本的な検索方法です。例えば、商品コードを指定して、その商品の名前を検索したい場合などに利用します。XLOOKUP関数で完全一致検索を行うには、4番目の引数「match_mode」に「0」を指定するか、この引数を省略します。省略した場合、デフォルトで「0」(完全一致)が適用されるため、多くの場合、引数を明示的に指定する必要はありません。

1. 検索対象のデータ範囲と検索値の準備
   検索したい値(検索値)と、その値が存在する可能性のあるデータ範囲を準備します。検索値は単一のセルでも、複数のセル範囲でも構いません。
2. XLOOKUP関数の入力
   結果を表示したいセルに、以下の構文でXLOOKUP関数を入力します。
   =XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [検索モード], [検索方法])
3. 検索値と検索範囲の指定
   関数の最初の2つの引数に、検索したい値(例: A2セル)と、その値が含まれる可能性のある範囲(例: D2:D10セル)を指定します。
4. 戻り範囲の指定
   検索値が見つかった場合に、その行(または列)から取得したい値が含まれる範囲(例: E2:E10セル)を指定します。
5. match_mode引数を0に設定
   4番目の引数「match_mode」に「0」を指定します。これは完全一致を意味します。省略した場合はデフォルトで「0」が適用されます。
6. 数式の確定
   Enterキーを押して数式を確定します。指定した検索値と完全に一致するデータがあれば、対応する戻り範囲の値が表示されます。一致するデータがない場合は、「見つからない場合」引数で指定した値が表示されるか、エラー値が表示されます。

例えば、商品リストから商品コード「ABC123」に対応する商品名を取得したい場合、以下のような数式になります。

=XLOOKUP("ABC123", A2:A10, B2:B10, "該当なし", 0)

この数式では、A2からA10の範囲に「ABC123」という文字列が完全に一致するものがあれば、B2からB10の範囲から対応する商品名を取得します。見つからない場合は「該当なし」と表示されます。

XLOOKUP関数で近似一致検索を行う手順

近似一致検索は、検索値が完全に一致しなくても、最も近い値を見つけたい場合に役立ちます。例えば、点数に基づいて成績(優、良、可など)を判定したり、売上金額の範囲に応じて手数料率を決定したりする際に有効です。XLOOKUP関数では、4番目の引数「match_mode」に「-1」または「1」を指定することで近似一致検索が可能です。

近似一致検索(match_mode = -1): 検索範囲が降順の場合

「match_mode」に「-1」を指定すると、検索範囲が降順(大きい値から小さい値へ)に並んでいる場合に、検索値以下の最も大きい値(つまり、検索値に最も近い、または検索値そのもの)を返します。これは、例えば、売上金額と手数料率の対応表で、売上金額が降順で並んでいる場合に利用できます。

1. 降順に並んだ検索範囲の準備
   検索範囲のデータが、大きい値から小さい値へと降順に並んでいることを確認します。例えば、売上金額が D2:D10 の範囲に降順で入力されているとします。
2. XLOOKUP関数の入力
   結果を表示したいセルに、以下の構文でXLOOKUP関数を入力します。
3. 検索値と検索範囲の指定
   検索したい値(例: F2セルに入力された売上金額)と、降順に並んだ検索範囲(例: D2:D10セル)を指定します。
4. 戻り範囲の指定
   検索値に対応する手数料率などが含まれる範囲(例: E2:E10セル)を指定します。
5. match_mode引数を-1に設定
   4番目の引数「match_mode」に「-1」を指定します。これにより、検索値以下の最も大きい値(近似値)が検索されます。
6. 数式の確定
   Enterキーを押して数式を確定します。指定した売上金額に対して、それ以下の最も近い金額に対応する手数料率が表示されます。

例:売上金額F2セルがあり、D列に売上金額(降順)、E列に手数料率がある場合。

=XLOOKUP(F2, D2:D10, E2:E10, "該当なし", -1)

近似一致検索(match_mode = 1): 検索範囲が昇順の場合

「match_mode」に「1」を指定すると、検索範囲が昇順(小さい値から大きい値へ)に並んでいる場合に、検索値以上の最も小さい値(つまり、検索値に最も近い、または検索値そのもの)を返します。これは、例えば、点数と成績の対応表で、点数が昇順で並んでいる場合に利用できます。

1. 昇順に並んだ検索範囲の準備
   検索範囲のデータが、小さい値から大きい値へと昇順に並んでいることを確認します。例えば、点数が G2:G10 の範囲に昇順で入力されているとします。
2. XLOOKUP関数の入力
   結果を表示したいセルに、以下の構文でXLOOKUP関数を入力します。
3. 検索値と検索範囲の指定
   検索したい値(例: H2セルに入力された点数)と、昇順に並んだ検索範囲(例: G2:G10セル)を指定します。
4. 戻り範囲の指定
   検索値に対応する成績などが含まれる範囲(例: I2:I10セル)を指定します。
5. match_mode引数を1に設定
   4番目の引数「match_mode」に「1」を指定します。これにより、検索値以上の最も小さい値(近似値)が検索されます。
6. 数式の確定
   Enterキーを押して数式を確定します。指定した点数に対して、それ以上の最も小さい点数に対応する成績が表示されます。

例:点数H2セルがあり、G列に点数(昇順)、I列に成績があると場合。

=XLOOKUP(H2, G2:G10, I2:I10, "該当なし", 1)

近似一致検索(match_mode = 2): ワイルドカード検索

「match_mode」に「2」を指定すると、ワイルドカード文字(アスタリスク * や疑問符 ?)を使った検索が可能です。これは、文字列の一部が一致するデータを検索したい場合に利用できます。例えば、名前に「郎」が付く人を全員検索したい場合などに使えます。

1. 検索対象の文字列データ準備
   検索したい文字列データが A2:A10 にあるとします。
2. XLOOKUP関数の入力
   結果を表示したいセルに、以下の構文でXLOOKUP関数を入力します。
3. 検索値と検索範囲の指定
   検索したい値(例: “*郎”、つまり「郎」で終わる文字列)と、検索範囲(例: A2:A10セル)を指定します。
4. 戻り範囲の指定
   取得したい値が含まれる範囲(例: B2:B10セル)を指定します。
5. match_mode引数を2に設定
   4番目の引数「match_mode」に「2」を指定します。これにより、ワイルドカード文字を使った検索が可能になります。
6. 数式の確定
   Enterキーを押して数式を確定します。指定したワイルドカードパターンに一致するデータがあれば、対応する戻り範囲の値が表示されます。

例:A列に氏名、B列に所属部署があり、「郎」で終わる氏名に対応する部署を検索する場合。

=XLOOKUP("*郎", A2:A10, B2:B10, "該当なし", 2)

近似一致検索(match_mode = -1 または 1)の注意点

近似一致検索(-1または1)を使用する際は、検索範囲のデータの並び順が非常に重要です。Excelは、指定された並び順(昇順または降順)に基づいて近似値を探索します。もし、検索範囲のデータが指定された順序で並んでいない場合、期待通りの結果が得られない、あるいは誤った結果が返される可能性があります。そのため、近似一致検索を行う前に、必ず検索範囲のデータが正しく並び替えられているかを確認してください。

また、近似一致検索では、検索値が検索範囲の最小値より小さい場合や最大値より大きい場合、それぞれ最小値または最大値が返されます。これは仕様ですが、意図しない結果とならないように、データの範囲を把握しておくことが大切です。

XLOOKUP関数とVLOOKUP関数の検索モード比較

XLOOKUP関数が登場する前は、VLOOKUP関数がデータ検索の主要な関数でした。VLOOKUP関数でも近似一致検索は可能でしたが、いくつかの制約がありました。XLOOKUP関数と比較することで、その進化がより明確になります。

XLOOKUP関数でよくある誤解とトラブルシューティング

XLOOKUP関数は非常に強力ですが、使い方によっては意図しない結果になることもあります。ここでは、よくある誤解やトラブルシューティングについて解説します。

h3>「#N/A」エラーが表示される場合

「#N/A」エラーは、指定した検索値が見つからなかった場合に表示されます。これは、完全一致検索(match_mode=0)で、検索値と完全に一致するデータが検索範囲に存在しない場合に発生します。

対処法:

1. 検索値の確認
   検索値にスペルミスがないか、余分なスペースが入っていないかを確認します。
2. 検索範囲の確認
   検索範囲に、検索したい値が実際に存在するかどうかを確認します。
3. match_modeの指定確認
   完全一致を意図しているのに、近似一致(-1または1)やワイルドカード(2)が指定されていないか確認します。
4. 「見つからない場合」引数の活用
   エラー表示を避けたい場合は、XLOOKUP関数の4番目の引数「見つからない場合」に、表示したい文字列(例: “該当なし”)を指定します。

h3>近似一致検索で期待通りの値が返ってこない場合

近似一致検索(match_mode=-1または1)で予期しない結果が返される場合、最も可能性が高いのは検索範囲の並び順が正しくないことです。

対処法:

1. 検索範囲の並び順を確認
   match_modeに-1を指定した場合は降順(大きい→小さい)、1を指定した場合は昇順(小さい→大きい)になっているか確認します。必要であれば、Excelの並べ替え機能を使ってデータを並べ替えます。
2. 検索値と検索範囲のデータ型を確認
   数値と文字列が混在している場合、期待通りの比較ができないことがあります。データ型を統一してください。
3. 検索値が範囲外の場合
   検索値が検索範囲の最小値より小さい、または最大値より大きい場合、それぞれ最小値または最大値が返されます。これは仕様ですが、意図しない結果とならないよう、データの範囲を把握しておきましょう。

h3>XLOOKUP関数が利用できない場合

XLOOKUP関数は、Excel for Microsoft 365、Excel 2021、Excel for the webで利用可能です。これらのバージョンより古いExcel(Excel 2019以前の永続ライセンス版など)では、XLOOKUP関数は提供されていません。

対処法:

1. Excelのバージョンを確認
   ExcelのバージョンがXLOOKUP関数に対応しているか確認します。「ファイル」タブから「アカウント」を選択し、「Office の更新プログラム」で最新の状態にアップデートできるか確認してください。
2. 代替関数を使用
   古いバージョンのExcelを使用している場合は、VLOOKUP関数やINDEX/MATCH関数を組み合わせて同様の機能を実現する必要があります。

まとめ

ExcelのXLOOKUP関数は、match_mode引数を活用することで、完全一致だけでなく、近似一致やワイルドカード検索を柔軟に行える強力な関数です。検索値の条件に合わせてmatch_modeを適切に設定することで、点数による成績判定や売上に応じた手数料率の決定といった、より複雑なデータ検索・集計作業を効率化できます。近似一致検索を行う際は、検索範囲の並び順に注意し、必要に応じてデータを並べ替えることが重要です。XLOOKUP関数を使いこなして、Excelでのデータ分析業務をさらに効率化しましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】