エクセルのデータ検索を極める！VLOOKUP・MATCH+OFFSET・XLOOKUPの使い方

Excel小技part3
2025.02.01

こんにちは！今回はエクセルのデータ検索関数「VLOOKUP」「MATCH+OFFSET」「XLOOKUP」の使い方を解説します。 大量のデータから特定の値を検索し、関連する情報を自動取得できるこれらの関数は、データ管理やレポート作成に欠かせません。

例えば、「社員番号を入力したら、氏名や部署が自動で表示される」や「商品コードから価格を取得する」といった場面で活躍します。 それぞれの関数の特徴を学び、適切なシチュエーションで使い分けられるようになりましょう！

VLOOKUP関数の使い方

VLOOKUPダウンロード

**VLOOKUP（垂直検索）** は、指定した値を縦方向（列）に検索し、対応する列のデータを取得する関数です。

✅ VLOOKUPの構文

=VLOOKUP(検索値, 検索範囲, 列番号, [検索方法])

  • 検索値：検索する値（例：商品コードや社員番号）
  • 検索範囲：データが含まれる範囲（検索値は一番左の列にある必要がある）
  • 列番号：取得したいデータがある列の番号（1から始まる）
  • 検索方法：FALSE（完全一致）またはTRUE（近似一致）を指定（通常はFALSE）

✅ VLOOKUPの活用例

以下のような社員リストから「社員番号」をもとに「氏名」を取得する例を考えます。

社員番号氏名部署
1001田中営業
1002佐藤総務
1003鈴木開発

**例：A1セルに社員番号を入力すると、B1セルに氏名を表示**

=VLOOKUP(A1, A2:C4, 2, FALSE)

この式では、A1に入力された社員番号をA2:A4で検索し、2列目（氏名）を取得します。

MATCH + OFFSET を使った検索（VLOOKUPの代替）

VLOOKUP は便利ですが、「検索する値の列が左端でなければならない」という制限があります。 MATCH 関数と OFFSET 関数を組み合わせることで、この制限を克服し、より柔軟な検索を実現できます。

MATCH + OFFSETダウンロード

✅ MATCH + OFFSET の構文

=OFFSET(開始セル, MATCH(検索値, 検索範囲, 0) - 1, 列オフセット)

  • 開始セル：検索結果を取得するデータの最初のセル（通常は一番上）
  • 検索値：検索したい値（例：社員番号）
  • 検索範囲：検索値が含まれる列（例：社員番号リスト）
  • 列オフセット：検索範囲から取得したいデータの列番号（例：氏名や部署）

✅ MATCH + OFFSET の活用例

以下のような社員リストがあるとします。

社員番号氏名部署
1001田中営業
1002佐藤総務
1003鈴木開発

**例：A1セルに社員番号を入力すると、B1セルに氏名を表示**

=OFFSET(B2, MATCH(A1, A2:A4, 0) - 1, 0)

この式の動作：

  1. MATCH(A1, A2:A4, 0) で検索値（A1セルに入力した社員番号）が A2:A4 の何行目にあるかを取得。
  2. OFFSET 関数で、B2（氏名の最初のセル）を基準に、取得した行番号に応じて移動。

例えば、A1に「1002」を入力すると、MATCH は「2」（A3が2行目）を返し、B2から1行下（B3）に移動して「佐藤」を取得します。

XLOOKUP関数の使い方（新しいVLOOKUP）

**XLOOKUP** は、VLOOKUPの進化版で、以下のような特徴があります。

  • VLOOKUPのように「検索範囲の左端」に限定されない（逆方向検索OK）。
  • 「列番号」の指定が不要で、直接検索範囲と返す値の範囲を指定。
  • 検索値がない場合のデフォルト値を設定可能。
XLOOKUPダウンロード

✅ XLOOKUPの構文

=XLOOKUP(検索値, 検索範囲, 返す範囲, [検索方法], [一致モード])

✅ XLOOKUPの活用例

VLOOKUPと同じように、社員リストを検索するとします。

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

この式では、A1の社員番号をA2:A4で検索し、B2:B4の氏名を取得します。 **検索値が見つからない場合は「該当なし」を表示**するため、エラー処理が不要になります！

3つの関数の違いと使い分け

関数用途特徴
VLOOKUP左から右への検索検索範囲の左端に検索値が必要
MATCH + OFFSET自由な位置検索左端制限なし、柔軟な検索が可能
XLOOKUP最新の検索左右どちらも検索OK、エラー対応可

まとめ

今回は、エクセルでよく使われる検索関数「VLOOKUP」「MATCH + OFFSET」「XLOOKUP」の違いと使い方を紹介しました！ 次回は「実は奥の深いSUM関数いろいろ」をご紹介します。お楽しみに！

コメント