いまさら聞けないExcelの使い方講座
【Excel新関数】面倒なVLOOKUP関数のエラー処理にサヨナラ！新搭載のXLOOKUP関数だけを使って解決するテク
2020年6月3日 06:55
VLOOKUP関数で必須のエラー処理が不要になる？！
普段、Excelを使う業務を担当している読者の中には、VLOOKUP関数は欠かせない関数の1つだと感じている人も多いのではないでしょうか。VLOOKUP関数は、指定したデータのリストから検索条件を満たすデータを探し出して表示してくれる関数で、データ入力の強い味方です。
でも、次の見積書の例のように、VLOOKUP関数を使って作成した資料に「#N/A」というエラー（①）が表示されて困った経験はありませんか。
この「#N/A」エラーは、数式などで参照しているセルに値が存在しない時に発生するエラーです。この例では、B列には、VLOOKUP関数を使った数式が入力されていますが（②）、エラーの出ているセルB9以降のセルでは、数式で参照しているセルA9（③）以降のセルが空欄になっているためエラーになっているのです（VLOOKUP関数の詳細については、以前の記事をご覧ください）。
この例では、A列にデータが入力されていなくても、B列には数式だけ入力しておきたいので、適切なエラー処理をする必要があります。次のように、IFERROR関数を使って、「参照しているA列のセルにデータが入力されていない場合は何も表示しない」という処理を記述しなければなりません（④）（IFERROR関数を使ったエラー処理の詳細は、以前の記事をご覧ください）。
関数を2つ組み合わせているので、かなり式が長く、複雑になっていますよね。そこで今回は、関数を組み合わせずに、XLOOKUP関数だけを使ってエラー処理まで行ってしまう方法を解説します。XLOOKUP関数はMicrosoft 365（旧称Office 365）に新しく登場した関数で、VLOOKUP関数で使いにくかった点が改善されています。また後半では、XLOOKUP関数の実力をより感じられるための記述方法も解説しますね。
XLOOKUP関数で「データが見つからない場合」を指定しよう
では、先ほどの見積書の例を使ってやってみましょう。XLOOKUP関数の基本的な使用方法は、前回の記事で解説しましたが、書式を復習しておきましょう。
この書式にある4つ目の引数「見つからない場合」で、検索値が見つからなかった場合に何を表示させるかを指定できます。
今回の見積書の例では、XLOOKUP関数を使って取り出したいデータは同じブック内の別のシート（「商品カタログ」シート）（①）にあります。「見積書」シートのセルに数式を記述する際、引数の「検索範囲」や「戻り範囲」を指定しやすくするために、それぞれの範囲に名前を付けておきましょう。
最初に、「商品カタログ」表の「商品番号」列（セル範囲A4:A12）に名前を付けます。セル範囲A4:A12を選択し（②）、名前ボックスに任意の名前を直接入力します。ここでは「商品番号」（③）と入力します。このようにすると、セル範囲A4:A12を参照したい時は、セル番号ではなく、「商品番号」と記述すればよくなります。わかりやすく、入力もしやすいので便利ですね。
続けて、「商品名」列（セル範囲B4:B12）に、「商品名」（④）という名前を付けます。
同様に、「単価（税抜）」列（セル範囲C4:C12）に、「単価」（⑤）という名前を付けておきます。これで準備は完了です。
では、「見積書」シートに戻り、数式を入力していきましょう。セルB8に「=XLOOKUP(」と入力したあとで、検索値となる「A8」を入力し、「,」（カンマ）を入力します（⑥）。
続けて、検索値（セルA8の値）を検索するセル範囲（A4:A12）を指定しますが、ここで、先ほど設定した名前「商品番号」を入力し、「,」（カンマ）を入力します（⑦）。
さらに続けて、取り出したい商品名が入力された範囲（B4:B12）を指定します。ここでも先ほど設定した名前「商品名」を入力し、「,」（カンマ）を入力します（⑧）。
最後に、検索値が見つからなかった場合に表示する値を指定します。ここでは、データが見つからなかった場合、何も表示されないようにしたいので、「""」（⑨）と入力します。以降の引数は省略し、「)」（カッコ）を入力し、［Enter］キーを押して、数式を確定します。
セルA8に入力された「A-002」に該当する商品名「クラフト封筒 長形4号 100枚セット」（⑩）がセルB8に表示されましたね。
この数式をB列のほかのセルにもコピーしましょう。オートフィルを使って、セルB8に入力した数式をセルB13までコピーします（⑪）。今度は、以前のような「#N/A」エラーが現れることはありませんね。
IFERROR関数と組み合わせなくてもエラー処理ができてしまうので、数式もシンプルでわかりやすいです。
XLOOKUP関数のマジックを体験しよう
実は、この「見積書」の例では、XLOOKUP関数の特徴をもっと活かせる入力方法があります。もう一度、セルB8に数式を入力し直してみましょう。XLOOKUP関数の効果をわかりやすくするために、ここでは事前に、セル範囲A8:A13に商品番号を入力しておきます（①）。
セルB8に「=XLOOKUP(」と入力したあとで、1つ目の引数を指定しますが、ここで検索値としてセル範囲A8:A13を指定します（実際には、あとでコピーする時にA列が動かないように「$A8:$A13」と入力します）。続けて「,」（カンマ）を入力します（②）。
このように、検索値に「検索するセル範囲」を指定しておくと、あとでマジックが起きるのです。
以降は、先ほどと同様に引数を入力していき（③）、最後に「)」（カッコ）を入力して［Enter］キーを押します。
すると、B8からB13までのセルに一気に商品名が表示されましたね（④）！ これがXLOOKUP関数のマジックです。
続けて、セルB8に入力した数式を左の列のセルC8にドラッグしてコピー（⑤）してみましょう。
すると、セル範囲C8:C13に一気に値が表示されます（⑥）が、ここに表示したいのは単価なので、この表示内容は正しくありませんよね。
今回、「見積書」のC列に表示したい情報は、「商品カタログ」の「単価」列（セル範囲C4:C12）のデータです。先ほど、このセル範囲には「単価」という名前を付けたので、数式の3つ目の引数を「単価」（⑦）と書き換えます。
数式を修正できたら、［Enter］キーを押します。
すると、「見積書」のセル範囲C8:C13の値が一気に修正され、単価が表示されるようになります（⑧）。
今回の例では、実際に入力した数式はたった1カ所です。それだけで、これらのセルに一気にデータを入力できてしまいました。とても素敵な関数だと思いませんか。
新関数XLOOKUP関数を使いこなそう
今回は、Microsoft 365（旧称Office 365）に新しく登場したXLOOKUP関数をより便利に活用する方法を解説しました。本記事の前半では、VLOOKUP関数で必須だったエラー処理をせずに対応する方法を紹介しました。また後半では、XLOOKUP関数の1つ目の引数に「検索するセル範囲」を指定することで、一気に複数のセルに数式の処理結果を表示する方法を解説しました。皆さん、この新しい関数の可能性にワクワクしてきませんか。ぜひ一度、試してみてくださいね！