Excelでデータを検索する際に便利な関数として VLOOKUP関数 と XLOOKUP関数 が挙げられます。特にVLOOKUPは長年親しまれてきた定番の検索関数ですが、XLOOKUPはその進化版として登場し、さまざまな制限が改善されています。本記事では、両関数の基本的な使い方と違い、そしてどちらを選ぶべきかについて解説します。
目次
VLOOKUP関数とは？
VLOOKUP関数は、垂直方向（縦）にデータを検索し、指定した列から値を取得する関数です。基本的な書式は以下の通りです：
=VLOOKUP(検索値, 範囲, 列番号, 検索の型)
- 検索値: 検索したい値（キーとなる値）
- 範囲: 検索対象のデータ範囲
- 列番号: 取得する値がある列の番号（範囲内での位置）
- 検索の型: 「TRUE（近似値）」または「FALSE（完全一致）」を指定
基本的なVLOOKUPの使い方
以下のような「商品マスタ」シートがあるとします：
別の「販売データ」シートで商品番号ごとに販売データをまとめる際、このままでも問題ないですがやっぱり商品名が入っていた方がわかりやすいですよね。
その際に使用できるのが、VLOOKUP関数です。
B2セルにVLOOKUP関数を利用して、下記のように入力した結果、商品名が表示されました。
=VLOOKUP(A2,商品マスタ!A:B,2,FALSE)
これを日本語に訳すと、
「A2セルのデータを、『商品マスタ』シートのA～B列の一番左の列から見つけて、見つけたデータ（行）の２列目のデータで完全に一致しているものを表示してください。」ということになります。
VLOOKUP関数の注意点
VLOOKUP関数は便利ですが、いくつかの注意点があります。
- 検索列が左端に固定
検索値は常に範囲の左端列に存在する必要があります。 - 列番号の固定
列番号を変更するときは手動で入力し直す必要があります。 - エラーが発生した場合
検索のときに、データに含まれない値や文字列で検索するとエラーが表示されたり、正しい値で表示されなかったりします。その場合はIFERROR関数などを組み合わせる必要があります。詳しくはIFERROR関数の解説にてお話しします。 - 近似値検索の誤動作
「検索の型」にTRUEを指定した場合、データが昇順に並んでいないと誤った結果を返すことがあります。 - 同じデータが存在した場合
検索対象のデータ範囲（上の例では「商品マスタ」シート）で、同じデータが存在していた場合、一番上にあるデータが表示されます。同じデータがあることに気づかないと、誤ったデータを引っ張ってきてしまうことになります。
XLOOKUP関数とは？
XLOOKUP関数は、VLOOKUP関数の進化版であり、従来の制約を克服した非常に柔軟な検索関数です。基本的な書式は以下の通りです。
注：XLOOKUPはOffice 2021およびMicrosoft 365にて使用できます。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, [見つからない場合], [一致の種類], [検索の順序])
- 検索値: 検索したい値（キーとなる値）
- 検索範囲: 検索対象の列
- 戻り値範囲: 取得したい値がある列
- 見つからない場合（省略可）: 値が見つからない場合に返す値
- 一致の種類（省略可）: 完全一致（デフォルト）や近似値の検索設定
0または省略：完全一致（デフォルト）
-1：完全一致または次に小さい項目
1：完全一致または次に大きい項目
2：ワイルドカード文字を使用可能 - 検索の順序（省略可）: 順方向または逆方向に検索
1または省略：先頭から末尾へ（デフォルト）
-1：末尾から先頭へ
2：二分探索（昇順）
-2：二分探索（降順）
基本的なXLOOKUPの使い方
さて、先ほどと同じ下記の表でXLOOKUP関数を使ってみましょう。
B2セルにXLOOKUP関数を利用して、下記のように入力した結果、商品名が表示されました。
=XLOOKUP(A2,商品マスタ!A:A,商品マスタ!B:B,””,1,1)
これを日本語に訳すと、
「A2セルのデータを、『商品マスタ』シートのA列から見つけて、見つけたデータ（行）のB列のデータで完全に一致しているものを表示してください。」ということになります。
XLOOKUP関数の強み
- 柔軟な列の指定
検索範囲と戻り値範囲を自由に指定でき、検索列が左端にある必要がありません。 - エラー時の対応が簡単
値が見つからない場合に返す値を指定できるため、#N/Aエラーを回避可能です - 水平検索も可能
水平方向（横）にも対応しています。 - 複雑な検索の種類
完全一致、近似値（次小値/次大値）、検索の方向を選択できます。
VLOOKUP関数とXLOOKUP関数の比較
| 特徴 | VLOOKUP | XLOOKUP |
|---|---|---|
| 左端列の制限 | あり | なし |
| 列番号の指定 | 必要 | 不要（範囲で指定） |
| 水平方向の検索 | 不可 | 可能 |
| エラー時の代替値 | 指定不可 | 指定可能 |
| 柔軟性・応用範囲 | 制限あり | 高い |
どちらを使うべき？
- 初心者や古いExcelバージョンを使用している場合は、VLOOKUP関数を使うのが無難です。
- 最新のExcel（Office 365や2021以降）を利用している場合は、XLOOKUP関数を活用しましょう。柔軟性が高く、より効率的です。
まとめ
VLOOKUP関数は長年愛用されてきた定番の関数ですが、制約が多いため、新しいXLOOKUP関数の方が柔軟で効率的です。特にデータ分析や複雑な検索処理を行う際は、XLOOKUP関数が大いに役立ちます。ぜひ両方の関数を使い分け、Excelスキルをさらに向上させましょう！
コメント