Excelのオンライン版で、検索範囲から指定したデータを検索する関数「XLOOKUP」の使い方を説明します。
基本的な使い方の他に、検索で見つからなかった時のエラー「#N/A」の表示を変えたり、検索対象が空白時の「0」表示を消す方法も紹介します。
基本的な使い方
- Step 1
ここでは、2枚のワークシートを使います。
1枚目は、「No・氏名・郵便番号・都道府県名・市区町村名・町域名」が表示されています。
2枚目は、「郵便番号・都道府県名・市区町村名・町域名」が表示されています。
1枚目の「郵便番号」を元にして、XLOOKUPで2枚目の「都道府県名・市区町村名・町域名」を検索して、一致する行のデータを引っ張ってきたいと思います。
- Step 2
関数「XLOOKUP」の基本的な使い方（構文）は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 検索結果)ではまず、1枚目の「都道府県名」をXLOOKUPを使って表示してみます。
1枚目の「都道府県名」のセル領域をクリックして、以下の式を入力します。
=XLOOKUP(C2, 東京都!C:C, 東京都!G:G)
- Step 3
数式を入力してから、キーボードでenter(return)キーを押すと、以下のようにデータが検索・表示されます。
なお、以下のサンプルの画面ではExcelの「テーブル」機能を使っているため、一番下の行まで自動的に数式が入力されています。
また、2行目から5行目までは「郵便番号」が入ってないので「0」が表示されていますが、これの回避方法は以下の章で解説します。
- Step 4
同じように、1枚目の「市区町村名」をXLOOKUPを使って表示してみます。
1枚目の「市区町村名」のセル領域をクリックして、以下の式を入力します。
=XLOOKUP(C2, 東京都!C:C, 東京都!H:H)
入力したら、キーボードでenter(return)キーを押して確定させます。
- Step 5
最後も同じように、1枚目の「町域名」をXLOOKUPを使って表示してみます。
1枚目の「町域名」のセル領域をクリックして、以下の式を入力します。
=XLOOKUP(C2, 東京都!C:C, 東京都!I:I)
入力したら、キーボードでenter(return)キーを押して確定させます。
一致しないときの「#N/A」の表示を変える方法
関数「XLOOKUP」で検索対象から見つからなかった場合は、エラー「#N/A」が表示されます。
一致していないことはわかるのですが、このままでは見た目が良くないので、エラーメッセージを変更してみたいと思います。
結論から言うと、検索が一致しない時の構文は次のようになります。
=XLOOKUP(検索値, 検索範囲, 検索結果, 検索が一致しない時の表示内容)元の式
「都道府県名」のセル
=XLOOKUP(C3, 東京都!C:C, 東京都!G:G)変更後の式
「都道府県名」のセル
=XLOOKUP(C3, 東京都!C:C, 東京都!G:G, "一致せず")
上記のように、「郵便番号」で検索できなかった場合は、「一致せず」と表示されるようになりました。
同様に、「市区町村名」と「町域名」も修正した結果が、以下の図です。
空白時の「0」表示を消す方法
前章までで検索が一致しない時のエラー表示は回避できましたが、そもその検索値が何も入力されてないときは「0」が表示されてしまいます。
ここでは、関数「IF」を使って、検索値が何も入っていないときは、XLOOKUPのセルに何も表示しない方法を紹介します。
結論から言うと、次の式を入力します。
=IF(検索値="", "", XLOOKUP(検索値, 検索範囲, 検索結果, 検索が一致しない時の表示内容))
なお、2番目の””の部分を適当な表示に変えることもできます。
まとめ
Excelのオンライン版で、検索範囲から指定したデータを検索する関数「XLOOKUP」の基本的な使い方に加えて、検索で見つからなかった時のエラー「#N/A」の表示を変えたり、検索対象が空白時の「0」表示を消す方法を紹介しました。
コメント