doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

社員番号から欲しい列だけ一発取得｜VLOOKUP卒業後に使うExcel関数5選【コピペで使える】

Excel 関数 業務効率化 データ活用 スプレッドシート

スポンサーリンク

社員番号から欲しい列だけ一発取得｜VLOOKUP卒業後に使うExcel関数5選【コピペで使える】

「氏名を引くVLOOKUP、部署を引くVLOOKUP、役職を引くVLOOKUP……」列の数だけ数式を増殖させた経験はありませんか？さらに列を1つ追加した瞬間、全数式の列番号を修正する羽目になった、という失敗も。

この記事を読めば、社員番号を入力するだけで、欲しい列だけをまとめて・一発で・自動整形して取得できるようになります。VLOOKUPの基本から始め、XLOOKUP・CHOOSECOLS・TAKE・DROPまでを実務直結のサンプルで解説します。

サンプルデータの構成

Sheet1に以下の従業員マスタがあると仮定します。範囲全体を EmpTable という名前付き範囲として定義しておきましょう。

A（社員番号） B（氏名） C（部署） D（役職） E（入社年） F（給与）
E001 山田 太郎 営業部 主任 2018 420,000
E002 鈴木 花子 人事部 係長 2015 480,000
E003 佐藤 次郎 開発部 部長 2010 650,000

① VLOOKUP｜まずは基本をおさらい

=VLOOKUP(検索値, 範囲, 列番号, FALSE)
=VLOOKUP("E002", EmpTable, 2, FALSE)   ' → 鈴木 花子
=VLOOKUP("E002", EmpTable, 3, FALSE)   ' → 人事部

シンプルで全バージョン対応という強みがある一方、実務では3つの壁にぶつかります。

  • 検索列は必ず左端：左側の列は返せない
  • 列番号のハードコード：列の追加・削除で数式が壊れる
  • 1列ずつしか返せない：3列欲しければ3つの数式が必要

エラーハンドリング

入力ミスや半角全角のゆらぎで #N/A が返ることは日常的です。IFERROR でラップしておくのが実務の基本です。

=IFERROR(VLOOKUP("E002", EmpTable, 2, FALSE), "社員番号を確認してください")

② XLOOKUP｜VLOOKUPの弱点をすべて克服する

対応バージョン：Excel 365 / Excel 2021以降

VLOOKUPで「3列欲しいのに3つ数式を書いた」という経験があるなら、XLOOKUPに乗り換えるだけで工数が一気に減ります。

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

複数列をまとめて返す

=XLOOKUP("E002", A2:A100, B2:C100, "該当なし")
' → 鈴木 花子 ／ 人事部（スピルで2列自動展開）

左方向への逆引きも可能

=XLOOKUP("人事部", C2:C100, A2:A100, "該当なし")
' 部署名 → 社員番号の逆引き

VLOOKUPでは不可能だった操作が1行で書けます。IFERROR を使わず第4引数でエラー文言を指定できる点も実務向きです。

③ CHOOSECOLS｜「この列とこの列だけ」を柔軟に選ぶ

対応バージョン：Excel 365以降

XLOOKUPで複数列を返せるようになっても、「部署列は不要、氏名・役職・給与だけ欲しい」という場面があります。列が飛び飛びの場合、XLOOKUPの戻り範囲では対応しきれません。そこで使うのがCHOOSECOLSです。

=CHOOSECOLS(配列, 列番号1, [列番号2], ...)
=CHOOSECOLS(
  XLOOKUP("E003", A2:A100, B2:F100, "該当なし"),
  1, 3, 5
)
' B列(氏名)=1番目, D列(役職)=3番目, F列(給与)=5番目
' → 佐藤 次郎 ／ 部長 ／ 650,000

「XLOOKUPで全列取得 → CHOOSECOLSで必要列だけ選ぶ」という2段構えが、実務でもっとも使いやすいパターンです。

④ TAKE vs DROP｜「切り出し」か「除外」かで使い分ける

対応バージョン：Excel 365以降

TAKEとDROPはどちらも配列を整形する関数ですが、発想の方向が逆です。「欲しいものの個数が決まっている」ならTAKE、「除きたいものの個数が決まっている」ならDROPと覚えてください。

TAKE｜先頭N列を切り出す

=TAKE(配列, 行数, [列数])
=TAKE(
  XLOOKUP("E001", A2:A100, B2:F100, "該当なし"),
  1, 3
)
' 先頭3列（氏名・部署・役職）だけ取得
' → 山田 太郎 ／ 営業部 ／ 主任

TAKEが向くケース：「常に先頭〇列だけ使う」と決まっているとき。列が増えても取得数が変わらない帳票向き。

DROP｜先頭N列を除外する

=DROP(配列, 行数, [列数])
=DROP(
  XLOOKUP("E002", A2:A100, A2:F100, "該当なし"),
  0, 1
)
' 先頭1列（社員番号）を除外して残り全列を返す
' → 鈴木 花子 ／ 人事部 ／ 係長 ／ 2015 ／ 480,000

DROPが向くケース：「給与など機密列を含む社員番号列だけ見せたくない」「除外したいものは決まっているが残りは全部欲しい」といった場面。列が増えても除外対象だけ指定すればよいため、テーブル拡張に強い。

実践｜4関数を組み合わせた総仕上げ数式

ここまで学んだ内容を1本の数式にまとめます。

「社員番号から、氏名・役職・給与の3項目だけを表示し、社員番号と入社年は非表示にする」

=CHOOSECOLS(
  DROP(
    XLOOKUP(A1, EmpTable[社員番号], EmpTable, "該当なし"),
    0, 1
  ),
  1, 3, 5
)

処理の流れは以下の通りです。

  1. XLOOKUP で社員番号を検索し、テーブル全体を返す
  2. DROP で先頭1列（社員番号列）を除外する
  3. CHOOSECOLS で残った列から氏名(1)・役職(3)・給与(5)だけを選ぶ

エラー対策はXLOOKUPの第4引数で一元管理しているため、IFERRORを重ねる必要がありません。

関数の使い分けまとめ

関数 主な用途 向くケース 対応バージョン
VLOOKUP 単一値の取得 レガシー環境・シンプルな検索 全バージョン
XLOOKUP 柔軟な検索・複数列返却 左引き・エラー文言指定 365 / 2021以降
CHOOSECOLS 飛び飛びの列を選択 列が連続していない場合 365以降
TAKE 先頭N列/行の切り出し 取得数が固定の帳票 365以降
DROP 先頭N列/行の除外 機密列除外・テーブル拡張対応 365以降

まとめ

  • VLOOKUPは今すぐIFERRORと組み合わせるだけでエラー表示が格段に改善する
  • XLOOKUPに切り替えれば、複数列取得・逆引き・エラー処理がすべて1関数で完結する
  • CHOOSECOLS / TAKE / DROPは「XLOOKUPで取得した後の整形」に使うと威力を発揮する
  • 組み合わせるときは XLOOKUP → DROP → CHOOSECOLS の順でネストするのが最もシンプル

次回のLookupシリーズでは、複数条件での検索とINDEX・MATCHとの使い分けを取り上げます。ぜひブックマークして続きをお楽しみに！