Excel で情報を検索する際、よく使われる関数に VLOOKUP があります。
近年では新しい検索関数として XLOOKUP が登場し、Microsoft も XLOOKUP の利用を推奨しているようです。
しかし、
- VLOOKUP と XLOOKUP は何が違うの？
- 今まで通り VLOOKUP を使っていて問題ない？
- どちらを覚えるべき？
と疑問に思う方も多いのではないでしょうか。
この記事では、VLOOKUP と XLOOKUP の違いを実際の使用例を交えながら分かりやすく解説します。
VLOOKUP とは
VLOOKUP は、一覧表の中から指定した値を検索して検索結果と同じ行にある値を取得する関数です。
一覧表を作成することの多い Excel では覚えておいて損のない関数と言えます。
使用用途としては、社員表の中から社員番号を使用して社員名を取得したり、一覧表に指定の値が存在するかを確認するときに使用したりします。
構文
VLOOKUP ( 検索値, 範囲, 列番号, [検索方法])
※ 検索方法は省略可能
例)
= VLOOKUP ( "1", A1:B10, 2 )
= VLOOKUP ( A1, B1:D20, 3, FALSE )
= VLOOKUP ( A1, 社員表!C1:D9, 2, TRUE )
使用例
社員表から社員番号を指定して社員名を取得するサンプルを作ります。
意味としては、『「B3」の検索値を「社員表の A2 から B11 の範囲 (の一番左の列)」から検索し、一致した行の 2列目の値を取得する。(検索方法は完全一致) 』となります。
式
= VLOOKUP ( B3, 社員表!$A$2:$B$11, 2, FALSE )
XLOOKUP とは
XLOOKUP は Microsoft 365 や Excel 2021 以降で利用できる新しい検索関数です。
VLOOKUP の欠点を改善するために作られただけあって、VLOOKUP の使いにくいところが解消されています。
解消されている部分は後ほど説明します。
構文
XLOOKUP ( 検索値, 検索範囲, 戻り範囲, [一致しなかった場合], [一致モード], [検索方法])
※ [ ] は省略可能
例)
= XLOOKUP ( "1", A1:A10, B1:B10 )
= XLOOKUP ( A1, B1:B100, D1:D100, "一致なし" )
= XLOOKUP ( A1, 社員表!C:C, 社員表!E:E, , -1 )
使用例
VLOOKUP の時と同じ社員表から社員名を取得するサンプルを作ります。
意味としては、『「B3」の値を「社員表の A2 から A11 の検索範囲」から検索し、「社員表の B2 から B11 の戻り範囲」から値を取得する。』となります。
検索方法は完全一致が基本なので、特に指定する必要はありません。
式
= XLOOKUP ( B3, 社員表!$A$2:$A$11, 社員表!$B$2:$B$11 )
VLOOKUP と XLOOKUP の違い
XLOOKUP の説明で記載しましたが、XLOOKUP は VLOOKUP の欠点を改善するために作られました。
VLOOKUP を使用したことがある方であれば、一度は「これができれば・・・」などと思ったことがあるかもしれません。
Microsoft 365、Excel 2021 以降という条件を除けばかなり使いやすい関数ですので、使えるのであれば一度使ってみることをお勧めします。
左端を検索条件にしなくてよい
VLOOKUP は比較する値が検索範囲の左端列でなければなりません。
例えば「社員名 – 社員番号」の順番で作成されている社員表があった場合、「社員名」で「社員番号」を検索することはできますが、「社員番号」で「社員名」を検索することができず、表の並び替えが必要になります。
一方、XLOOKUP の場合は検索範囲と戻り範囲を自由に指定できるため、「社員番号」を検索範囲、「社員名」を戻り範囲に指定することが可能です。
検索範囲に列追加しても取得結果が変わらない
VLOOKUP は取得結果の列番号を指定します。
検索範囲の途中に列を追加した場合、数値で入力した列番号が自動調整されないため取得結果が変わってしまうことがあります。
一方、XLOOKUP は列番号を指定せずセルの範囲を指定するため、列を追加しても Excel が自動調整されます。
= VLOOKUP ( B3, 社員表!$A$2:$C$11, 2, FALSE )
= XLOOKUP ( B3, 社員表!$A$2:$A$11, 社員表!$B$2:$B$11 )
↓ 社員表を変えた時に自動調整される
= XLOOKUP ( B3, 社員表!$A$2:$A$11, 社員表!$C$2:$C$11 )
一致なしのエラー処理が簡単
VLOOKUP では検索値が見つからない場合、#N/A が表示されます。
エラーを表示させたくない場合は IFERROR などでエラー表示させない工夫が必要になります。
XLOOKUP では標準機能としてエラー時の表示を指定できます。
= IFERROR ( VLOOKUP ( B3, 社員表!$A$2:$B$11, 2, FALSE ), "該当なし" )
= XLOOKUP ( B3, 社員表!A2:A11, 社員表!B2:B11, "該当なし" )
数式が分かりやすい
VLOOKUP は取得したい列を検索範囲内の列番号で指定します。
例えば、VLOOKUP ( A2, A:C, 3, FALSE ) の場合は C 列となり、VLOOKUP ( A2, D:F, 3, FALSE ) の場合は F 列になります。同じ 3 を指定しても検索範囲により取得する結果が異なるため、ぱっと見ではどこを参照しているかがわかりにくい場合があります。
一方、XLOOKUP は検索範囲と戻り範囲を直接指定するため、取得した結果がどこの列を参照しているか理解しやすくなります。
戻り値の範囲指定については、複数列を指定する便利な方法もあるので近いうちに XLOOKUP の小技も公開する予定です。
= VLOOKUP ( A2, D:F, 3, FALSE )
= XLOOKUP ( A2, D:D, F:F )実務ではどちらを使うべき？
私は長年、業務で VLOOKUP を使用してきました。
既存の Excel ファイルでは今でも VLOOKUP が使われているケースが多く、保守や改修を行う際には VLOOKUP の知識が必要です。
しかし、Microsoft 365 や Excel 2021 以降を使用しているのであれば、新しく作成するファイルは XLOOKUP で作成することをおすすめします。
理由はここまで記載した通りですが以下となります。
- 左端を検索条件にしなくてよい
- 検索範囲に列追加しても取得結果が変わらない
- 一致なしのエラー処理が簡単
- 数式が分かりやすい
特に複数人で管理する Excel ファイルでは、後から修正しやすい XLOOKUP のメリットが大きいと感じています。
VLOOKUP と XLOOKUP の比較表
| 項目 | VLOOKUP | XLOOKUP |
| 利用開始 | Excel 2003以前から | Excel 2021・365 |
| 左方向検索 | × | ○ |
| 列追加への強さ | × | ○ |
| エラー処理 | IFERRORが必要 | 標準対応 |
| 数式の見やすさ | △ | ○ |
| 既存ファイルとの互換性 | ○ | △ |
※ ChatGPT 調べ
まとめ
VLOOKUP と XLOOKUP はどちらも Excel でデータ検索を行うための関数です。
現在でも VLOOKUP は広く利用されていますが、新規作成する場合は XLOOKUP の方が使いやすくおすすめです。
まずは VLOOKUP を理解したうえで、今後は XLOOKUP も活用できるようになると Excel 業務の効率化につながります。
特に Microsoft 365 を利用している方は、これからは XLOOKUP を積極的に活用していきましょう。