VLOOKUP関数を使えるようになりましたが、XLOOKUP関数は何が違うのでしょうか？新しく覚えた方がよいですか？
XLOOKUP関数は、表から検索値と一致するデータを探し、同じ行にある別の値を取り出す関数です。
VLOOKUP関数と同じように、商品番号から商品名を取り出したり、社員番号から氏名を取り出したりできます。
XLOOKUP関数では、検索する範囲と結果を取り出す範囲を別々に指定します。
取り出す列番号を数える必要がなく、検索列より左側にあるデータも取り出せます。
ただし、VLOOKUP関数で目的のデータを検索できている場合は、すべての数式をXLOOKUP関数へ変更する必要はありません。
このブログでは、古いExcelでも利用でき、既存の業務ファイルを理解するためにも役立つVLOOKUP関数を検索の基本とします。
XLOOKUP関数は、使用しているExcelのバージョンによっては利用できません。
会社や取引先とファイルを共有する場合は、自分の環境だけでなく、相手のExcelでもXLOOKUP関数を使用できるか確認する必要があります。
この記事では、XLOOKUP関数を使って、表から検索値と一致するデータを取り出す方法を解説します。
VLOOKUP関数との違い、左方向への検索、データが見つからない場合の表示方法、XLOOKUP関数を使用できないExcelも確認しましょう。
XLOOKUP関数で表から一致するデータを検索し、Excelのバージョンや検索内容に合わせてVLOOKUP関数と使い分けられるようになる
数式を入力してもXLOOKUP関数が候補に表示されない場合は、使用しているExcelのバージョンを確認してください。
XLOOKUP関数を利用できない環境では、VLOOKUP関数を使用して基本的な検索を行えます。
XLOOKUP関数とは一致するデータを検索する関数
XLOOKUP関数は、検索範囲から検索値と一致するデータを探し、戻り範囲の同じ行にある値を取り出す関数です。
基本的な検索では、検索値・検索範囲・戻り範囲の3つを指定します。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合])
引数
検索値：表から探したい値
検索範囲：検索値を探すセル範囲
戻り範囲：見つかった行から取り出すセル範囲
見つからない場合：一致する値がない場合に表示する内容
「見つからない場合」は省略可能な引数です。
省略して一致する値が見つからなかった場合は、#N/Aエラーが表示されます。
機能
検索範囲から検索値を探し、同じ行にある戻り範囲の値を取り出す関数
主な結果
商品番号を検索 → 同じ行の商品名を表示
XLOOKUP関数の引数
「検索値」には、表から探したい値または、その値が入力されたセルを指定します。
商品番号から商品名を検索する場合は、調べたい商品番号が検索値です。
「検索範囲」には、検索値を探すセル範囲を指定します。
商品番号がセルA2からA6に入力されている場合は、A2:A6を指定します。
「戻り範囲」には、一致するデータが見つかった行から、結果として取り出すセル範囲を指定します。
商品名がセルB2からB6に入力されている場合は、B2:B6を指定します。
「見つからない場合」には、検索値と一致するデータが見つからなかったときに表示する文字や値を指定します。
この引数は省略できますが、「該当なし」などを指定すると、#N/Aエラーの代わりに分かりやすい文字を表示できます。
検索値： E2
検索範囲： A2:A6
戻り範囲： B2:B6
見つからない場合： ”該当なし”
XLOOKUP関数で一致するデータを検索する
商品番号から対応する商品名を検索してみましょう。
セルE2に入力された商品番号をA列から探し、同じ行にあるB列の商品名をセルF2へ表示します。
検索値・検索範囲・戻り範囲を指定する
セルE2に商品番号「P003」が入力されている場合は、商品名を表示するセルF2へ次の数式を入力します。
=XLOOKUP(E2,A2:A6,B2:B6)
検索値 E2
検索範囲 A2:A6
戻り範囲 B2:B6
結果：ぶどうXLOOKUP関数は、検索範囲A2:A6からセルE2の「P003」を探します。
「P003」はセルA4にあるため、戻り範囲の同じ行にあるセルB4の「ぶどう」が表示されます。
XLOOKUP関数は、検索値と完全に一致するデータを探す設定が基本です。
VLOOKUP関数のように、基本的な完全一致検索で「0」や「FALSE」を追加する必要はありません。
VLOOKUP関数の基本的な使い方や引数は、「VLOOKUP関数の使い方」で詳しく解説しています。
XLOOKUP関数は、検索値・検索範囲・戻り範囲の3つを指定すれば、完全一致するデータを検索できます。
次は、一致するデータが見つからない場合に、エラーではなく任意の文字を表示する方法を確認しましょう。
見つからない場合の表示を指定する
検索範囲に検索値と一致するデータがない場合、基本のXLOOKUP関数では「#N/A」エラーが表示されます。
4つ目の引数「見つからない場合」を指定すると、エラーの代わりに「該当なし」などの文字を表示できます。
「該当なし」と表示する
セルE2の商品番号が商品一覧に見つからない場合に「該当なし」と表示するには、次の数式を入力します。
=XLOOKUP(E2,A2:A6,B2:B6,"該当なし")
検索値 E2
検索範囲 A2:A6
戻り範囲 B2:B6
見つからない場合 "該当なし"
結果 検索値「P010」→該当なしセルE2の商品番号が検索範囲A2:A6にあれば、同じ行の商品名が表示されます。
一致する商品番号がなければ、「#N/A」エラーの代わりに「該当なし」が表示されます。
「該当なし」などの文字を数式へ直接入力する場合は、半角のダブルクォーテーションで囲みます。
ダブルクォーテーションを付け忘れると、「#NAME?」エラーなどが表示される場合があります。
見つからない場合を空白にする
一致するデータがない場合に何も表示したくないときは、4つ目の引数へ空の文字列を指定します。
=XLOOKUP(E2,A2:A6,B2:B6,"")半角のダブルクォーテーションを2つ続けた「””」は、空の文字列を表します。
検索値が見つからない場合は、数式を入力したセルに何も表示されません。
VLOOKUP関数では、見つからない場合の表示を引数へ直接指定できないため、IFERROR関数などを組み合わせます。
VLOOKUP関数のエラーを空白や任意の文字へ変更する方法は、「VLOOKUPとIFERRORでエラーを空白にする方法｜#N/Aを非表示にしよう」で解説しています。
「””」を指定したセルは、見た目では何も表示されませんが、数式が入力されています。
COUNTA関数などで入力済みのセルを数える場合は、空白セルとの違いに注意してください。
「#N/A」や「#NAME?」などの原因と直し方は、「Excelのエラー表示一覧」で確認できます。
VLOOKUP関数とXLOOKUP関数の違い
VLOOKUP関数とXLOOKUP関数は、どちらも表から検索値と一致するデータを取り出せます。
主な違いは、検索範囲の指定方法、取り出す列の指定方法、検索できる方向、完全一致の指定方法です。
VLOOKUP関数
検索範囲を含む表全体を指定する
結果を取り出す列を列番号で指定する
検索値より右側の列から結果を取り出す
完全一致では0またはFALSEを指定する
XLOOKUP関数
検索範囲と戻り範囲を別々に指定する
結果を取り出す範囲を直接指定する
検索値の左右どちらからでも結果を取り出せる
完全一致が基本のため追加指定が不要
同じ検索を2つの関数で比較する
VLOOKUP関数では
セルE2の商品番号をA2:B6の左端列から探し、範囲の左から2列目にある商品名を取り出す。
=VLOOKUP(E2,A2:B6,2,0)XLOOKUP関数では
セルE2の商品番号をA2:A6から探し、同じ行にあるB2:B6の商品名を取り出す
=XLOOKUP(E2,A2:A6,B2:B6)2つの数式は、どちらも同じ商品名を取り出せます。
VLOOKUP関数は表全体と列番号を指定し、XLOOKUP関数は検索する列と結果を取り出す列を直接指定する点が異なります。
VLOOKUP関数で正しく検索できている数式を、すべてXLOOKUP関数へ変更する必要はありません。
利用するExcelのバージョンや、ファイルを共有する相手の環境を確認して使い分けましょう。
XLOOKUP関数では、検索範囲と戻り範囲を直接指定するため、列番号を数えずに検索できます。
次は、VLOOKUP関数では対応しにくい左方向の検索を確認しましょう。
XLOOKUP関数は列番号を数えない
VLOOKUP関数では、指定した表の左端列を「1」として、結果を取り出す列の番号を指定します。
セルE2の値をA2:C6の左端列から検索し、指定範囲の左から3列目にある値を取り出します。
=VLOOKUP(E2,A2:C6,3,0)XLOOKUP関数では、列番号を指定せず、結果を取り出すセル範囲を直接指定します。
一方XLOOKUP関数では、セルE2の値をA2:A6から検索し、同じ行にあるC2:C6の値を取り出します。
=XLOOKUP(E2,A2:A6,C2:C6)VLOOKUP関数：表の左端から列番号を数える
XLOOKUP関数：結果を取り出すセル範囲を直接指定する
VLOOKUP関数では、指定範囲の途中へ列を追加すると、取り出したい列と列番号の対応が変わる場合があります。
XLOOKUP関数は戻り範囲を直接指定するため、どの列から値を取り出す数式なのか確認しやすくなります。
XLOOKUP関数は左方向にも検索できる
VLOOKUP関数では、指定した範囲の左端列で検索し、その右側にある列から結果を取り出します。
XLOOKUP関数では検索範囲と戻り範囲を別々に指定するため、検索列より左側にあるデータも取り出せます。
=XLOOKUP(E2,B2:B6,A2:A6)
検索値 E2
検索範囲 B2:B6
戻り範囲 A2:A6検索範囲には商品番号が入力されたB2:B6、戻り範囲には左側の商品名が入力されたA2:A6を指定しています。
XLOOKUP関数では検索範囲と戻り範囲の左右関係を問わないため、商品番号から左側の商品名を取り出せます。
XLOOKUP関数を使用できる環境では、検索列を表の左端へ移動しなくても、検索範囲と戻り範囲を直接指定できます。
ただし、ファイルを共有する場合は、相手のExcelでXLOOKUP関数を利用できるか確認してください。
XLOOKUP関数を使用できないExcelでも、VLOOKUP関数の数式や表の作り方を工夫すれば、左側のデータを取り出せます。
詳しい方法は、「VLOOKUP関数で左側のデータを抽出する方法」で解説しています。
列番号を数えず、検索する列と結果を取り出す列を直接選べるので、数式の意味も分かりやすいですね！
XLOOKUP関数は、列番号を使わず、検索範囲と戻り範囲を直接指定できます。
次は、XLOOKUP関数を使用できないExcelと、VLOOKUP関数との使い分けを確認しましょう。
XLOOKUP関数を使えない場合
数式を入力してもXLOOKUP関数が候補に表示されない場合は、Excelのバージョンが対応していない可能性があります。
XLOOKUP関数を使用できない場合は、VLOOKUP関数で検索する方法を検討してください。
Excelのバージョンを確認する
XLOOKUP関数を使用する前に、自分が使用しているExcelのバージョンを確認しましょう。
会社のパソコンでは、部署や端末によって異なるバージョンのExcelが使用されている場合もあります。
XLOOKUP関数を使用できる主なExcel
Microsoft 365
Excel 2021
Excel 2024
XLOOKUP関数を使用できないExcel
Excel 2016
Excel 2019
新しいExcelで作成されたファイルを古いExcelで開くと、対応していない関数名の前に「_xlfn.」が表示される場合があります。
XLOOKUP関数の数式が正しく入力されていても、使用しているExcelが対応していなければ計算できません。
共有する相手のExcelも確認する
自分のExcelでXLOOKUP関数を使用できても、ファイルを共有する相手が古いExcelを使用している場合があります。
会社や取引先と共有するファイルでは、相手のExcelでも数式を使用できることを確認してください。
共有先の環境が分からない場合は、VLOOKUP関数を使用した方が対応しやすいことがあります。
まとめ｜対応環境ではXLOOKUP関数も活用しよう
XLOOKUP関数は、検索範囲から検索値と一致するデータを探し、戻り範囲の同じ行にある値を取り出す関数です。
検索範囲と戻り範囲を直接指定できるため、列番号を数えず、検索列の左右どちらにあるデータも取り出せます。
基本的な検索
=XLOOKUP(検索値,検索範囲,戻り範囲)
見つからない場合の表示を指定する
=XLOOKUP(検索値,検索範囲,戻り範囲,”該当なし”)
列番号
数えずに戻り範囲を直接指定する
検索する方向
検索列の左右どちらからでも結果を取り出せる
対応バージョン
Excel 2016・Excel 2019では使用できない
VLOOKUP関数とXLOOKUP関数は、どちらか一方だけが正しいわけではありません。
使用するExcelのバージョン、検索する方向、ファイルを共有する相手の環境に合わせて選びましょう。
VLOOKUP関数で目的のデータを正しく検索できている場合は、無理に数式をXLOOKUP関数へ変更する必要はありません。
新しく数式を作るときに、使用環境や検索内容を確認し、使いやすい関数を選んでください。
基本はVLOOKUP関数を使い、対応しているExcelで便利な検索が必要になったらXLOOKUP関数を選べばよいんですね！
検索関数を正しく使うには、検索値・検索範囲・戻り値の関係を理解することが大切です。
関数を基本から順番に学びたい場合は、Level3攻略フローから進めてください。
コメント