Excel約5分で読めます

ExcelのVLOOKUPで左側の列を取りたい|XLOOKUPとINDEX・MATCHの比較

ExcelのVLOOKUPで左側の列を取りたいを表したイメージ
記事の内容を表したイメージ(AI生成)

VLOOKUPは検索列より「右側」の値しか返せません。たとえば社員番号が列Bにあり、名前が列Aにある表から名前を引きたい場合、VLOOKUPではそのままでは対応できません。この記事では、この「左側の列を取りたい」という状況を解決する方法として、XLOOKUPとINDEX・MATCHの二つを取り上げ、どちらをどの場面で選ぶかを整理します。

解決策を先に書きます。Excelのバージョンが2021以降またはMicrosoft 365であればXLOOKUPが使えます。それより古いバージョンではINDEX・MATCHの組み合わせで同じことができます。どちらも「左側の列を返す」用途には十分対応できますが、書き方と覚えやすさが異なります。画面の表示やメニュー名はバージョンや設定によって異なる場合があります。


なぜVLOOKUPでは左側が取れないのか

VLOOKUPの構文は =VLOOKUP(検索値, 範囲, 列番号, 照合の型) です。「列番号」は範囲の左端を1とした相対的な番号で、必ず1以上の整数を指定します。そのため、検索に使う列(範囲の左端)より左にある列を指定する方法がなく、構造上、左方向への参照はできません。

回避策として「列全体を並べ替えて検索列を左端にする」方法もありますが、元の表の並び順を変えると他の数式や印刷レイアウトに影響が出ることがあります。並べ替えずに解決したい場合に、XLOOKUPかINDEX・MATCHを使います。


二つの方法の構文と例

XLOOKUPで左側の列を取る

XLOOKUPの基本構文は次のとおりです。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [照合モード], [検索モード])

「検索範囲」と「戻り範囲」を別々に指定できるので、戻り範囲を検索範囲より左の列に設定するだけで済みます。

架空の例です。

表は横にスクロールできます

列A(名前)列B(社員番号)列C(部署)
山田 太郎S001営業
鈴木 花子S002総務
佐藤 一郎S003経理

社員番号「S002」から名前(列A)を取りたい場合:

=XLOOKUP("S002", B2:B4, A2:A4, "該当なし")

結果:鈴木 花子

「S002」を検索範囲のB2:B4で探し、対応する戻り範囲A2:A4の値を返します。検索列Bより左の列Aを指定しているにもかかわらず、エラーにはなりません。第4引数の「該当なし」は、一致する値がなかったときに返す文字列です。省略すると見つからない場合に #N/A が出ます。

迷いやすい例外:戻り範囲に複数列を指定したとき

戻り範囲を A2:C4 のように複数列にすると、一致した行の複数列の値がまとめて返されます(スピル機能)。意図していない場合は戻り範囲を一列に絞ってください。スピル機能はExcel 2021以降で動作します。

INDEX・MATCHで左側の列を取る

INDEX・MATCHは二つの関数を組み合わせます。

  • MATCH:検索値が範囲の何番目にあるかを返します。
  • INDEX:範囲の指定した行番号(または列番号)にある値を返します。

構文の組み合わせ:

=INDEX(戻り範囲, MATCH(検索値, 検索範囲, 照合の型))

同じ架空の表で社員番号「S002」から名前を取る場合:

=INDEX(A2:A4, MATCH("S002", B2:B4, 0))

MATCHが「S002」をB2:B4の2行目に見つけ、INDEXがA2:A4の2行目の値を返します。結果は 鈴木 花子 です。

照合の型「0」は完全一致を意味します。並べ替えが不要なときは必ず0を指定してください。

迷いやすい例外:見つからないときのエラー処理

MATCHが一致を見つけられないと #N/A が返り、INDEXもエラーになります。エラーを別の文字に変えたい場合はIFERRORで囲みます。

=IFERROR(INDEX(A2:A4, MATCH("S999", B2:B4, 0)), "該当なし")

XLOOKUPとINDEX・MATCHの選び方

どちらを使うかは主に「Excelのバージョン」と「ファイルを使い回す環境」で決まります。

表は横にスクロールできます

観点XLOOKUPINDEX・MATCH
使えるバージョン2021以降、Microsoft 3652007以降(広い範囲)
数式の長さ短くなりやすい関数を入れ子にするため長め
エラー処理の組み込み第4引数で対応可IFERRORを別途追加
古いバージョンとの互換性開けないか #NAME? が出る問題なし

ファイルを共有する相手が古いExcelを使っている可能性がある場合は、INDEX・MATCHを選ぶほうが安全です。自分の環境がMicrosoft 365か2021以降であることが確認できていて、ファイルの共有先も同等であれば、XLOOKUPのほうが数式の見通しがよくなります。

使っているExcelのバージョンは、「ファイル」→「アカウント」または「ファイル」→「Excelのバージョン情報」から確認できます(画面の表示はバージョンや設定により異なります)。


作業前の確認と元に戻す手順

数式を入力する前に、ファイルを別名でコピーしておきます。「ファイルをコピー」または「名前を付けて保存」で保存したうえで作業することで、数式の入力ミスや範囲の指定誤りがあっても元の状態に戻せます。

数式を入力したら、正しい値が返っているかを手動で確かめてください。架空の例で言えば、「S001」を検索したときに「山田 太郎」が返るか、「S003」のとき「佐藤 一郎」が返るかを確認します。一致していない場合は、検索範囲と戻り範囲が同じ行数を参照しているかを最初に確認してください。ずれていると、意図しない行の値が返ります。

名簿など個人情報を含む表を使う場合、外部サービスや生成AIに数式や表のデータを入力する前に、情報の取り扱いについて確認してください。


XLOOKUPがバージョン不一致で動かないとき

古いExcelで作成したファイルをXLOOKUPを使って更新し、他の人が古いバージョンで開くと、#NAME? エラーが表示されます。この場合は次のどちらかで対応します。

  1. INDEX・MATCHに書き直す:上記の構文をそのまま使えます。
  2. 相手のExcelバージョンを確認して対処する:Microsoft 365への移行など、組織の環境に応じた判断が必要です。個別の判断は管理者や公式情報で確認してください。

#NAME? が出たときは、まず関数名のスペルを確認し、それでも解消しない場合はバージョン不一致を疑います。


次の行動

まず、自分が使うExcelのバージョンを確認してください。2021以降またはMicrosoft 365であればXLOOKUPを試し、それ以前であればINDEX・MATCHで数式を組んでください。作業前には必ずファイルをコピーしておき、入力後に一件ずつ検索結果を手で確かめるようにしてください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ