Excel - 検索関数

概要

Excelの検索/行列関数は、データの検索、参照、抽出を行うための重要な機能である。
大量のデータから特定の値を検索したり、条件に一致するデータを取り出したり、動的にセル参照を変更したりすることができる。

主な機能として、垂直検索 (VLOOKUP、XLOOKUP)、水平検索 (HLOOKUP)、行列参照 (INDEX、MATCH)、位置検索 (MATCH、XMATCH)、動的参照 (OFFSET、INDIRECT)、選択 (CHOOSE)等がある。

これらの関数は、マスタデータとの照合、商品情報の自動取得、動的なレポート作成、データベース的な操作等、様々な業務シーンで活用される。

垂直検索関数

VLOOKUP関数

縦方向 (垂直) に範囲を検索し、指定した列の値を取得する最も基本的な検索関数である。

構文:

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

引数の説明:

• 検索値

  検索する値

• 範囲

  検索範囲 (左端の列が検索対象)

• 列番号

  取得する列の番号 (範囲の左端を1とする)

• 検索方法

  TRUE (近似一致) または FALSE (完全一致)

  省略時は TRUE

使用例:

• A2の値をC列から検索し、D列(2列目)の値を返す

  =VLOOKUP(A2, C:E, 2, FALSE)

• 絶対参照で範囲を固定

  =VLOOKUP(A2, $C$2:$E$100, 3, FALSE)

• 近似一致で検索 (数値の範囲検索に使用)

  =VLOOKUP(A2, C:E, 2, TRUE)

※注意
検索は範囲の左端の列でのみ行われる。
FALSE (完全一致) が最も一般的に使用される。
検索値が見つからない場合は #N/A エラーを返す。
TRUE (近似一致) を使用する場合、検索列は昇順に並んでいる必要がある。

VLOOKUP のエラー処理

VLOOKUPは見つからない場合に #N/A エラーを返すため、エラー処理が必要になることが多い。

• IFERROR関数との組み合わせ

  =IFERROR(VLOOKUP(A2, C:E, 2, FALSE), "該当なし")

• IFNA関数との組み合わせ

  =IFNA(VLOOKUP(A2, C:E, 2, FALSE), "未登録")

• IF関数で事前チェック

  =IF(A2="", "", VLOOKUP(A2, C:E, 2, FALSE))

VLOOKUP の制限事項と回避方法

VLOOKUPには以下に示す制限がある。

制限1: 検索列より左側の列を取得できない。

• 問題

  範囲の左端列でしか検索できない

• 回避策

  INDEX関数 + MATCH 関数を使用する

制限2: 列番号の指定が固定になる。

• 問題

  列の挿入・削除で列番号がずれる

• 回避策

  MATCH関数で列番号を動的に取得する

制限3: 複数条件での検索ができない。

• 問題

  1つの列でしか検索できない

• 回避策

  作業列で条件を結合するか、INDEX + MATCH + MATCH を使用する

水平検索関数

HLOOKUP関数

横方向 (水平) に範囲を検索して、指定した行の値を取得する関数である。

構文:

=HLOOKUP(検索値, 範囲, 行番号, [検索方法])

引数の説明:

• 検索値

  検索する値

• 範囲

  検索範囲 (上端の行が検索対象)

• 行番号

  取得する行の番号 (範囲の上端を1とする)

• 検索方法

  TRUE (近似一致) または FALSE (完全一致)

使用例:

• 1行目から「売上」を検索し、3行目の値を返す

  =HLOOKUP("売上", A1:M5, 3, FALSE)

• A1の値を検索し、2行目の値を返す

  =HLOOKUP(A1, B1:Z10, 2, FALSE)

※注意
検索は範囲の上端の行でのみ行われる。
データが横方向に並んでいる場合に使用する。
VLOOKUP関数の横方向版と考えることができる。

INDEX関数とMATCH関数

INDEX関数

指定した行と列の交差位置にあるセルの値を返す関数である。

構文:

=INDEX(配列, 行番号, [列番号])

引数の説明:

• 配列

  参照する範囲

• 行番号

  取得する行の番号 (範囲の上端を1とする)

• 列番号

  取得する列の番号 (範囲の左端を1とする)。省略時は1列目

使用例:

• C列の5行目の値を返す。

  =INDEX(C2:C100, 5)

• C2:E100の範囲の5行目2列目 (D列) の値を返す。

  =INDEX(C2:E100, 5, 2)

• 10行目のC列の値を返す。

  =INDEX(A:C, 10, 3)

※注意
行番号や列番号に0を指定すると、その行全体または列全体が配列として返される。
範囲外の番号を指定すると #REF! エラーになる。

MATCH関数

指定した値が範囲内の何番目にあるかを返す関数である。

構文:

=MATCH(検索値, 検索範囲, [照合の種類])

引数の説明:

• 検索値

  検索する値

• 検索範囲

  検索する範囲 (1行または1列)

• 照合の種類

  1 (以下の最大値)、0 (完全一致)、-1 (以上の最小値)

  省略時は、1

照合の種類の詳細:

• 完全一致を検索する。(最も一般的)

  0

• 検索値以下の最大値を検索する。(昇順に並んでいる必要あり)

  1 または省略

• 検索値以上の最小値を検索する。(降順に並んでいる必要あり)

  -1

使用例:

• =MATCH("東京", A2:A100, 0)

  A列で「東京」が何行目にあるかを返す。

• =MATCH(A2, C:C, 0)

  C列でA2の値が何行目にあるかを返す。

• =MATCH(100, B2:B100, 1)

  B列で100以下の最大値が何行目にあるかを返す。

※注意
検索値が見つからない場合は #N/A エラーを返す。
照合の種類0 (完全一致) が最も一般的に使用される。
行番号そのものではなく、検索範囲内での位置を返す。

INDEX関数 + MATCH関数の組み合わせ

INDEX関数とMATCH関数を組み合わせることにより、VLOOKUP関数より柔軟な検索が可能になる。

基本的な使い方:

=INDEX(取得列, MATCH(検索値, 検索列, 0))

具体例:

• C列からA2を検索し、同じ行のD列の値を返す。

  =INDEX(D:D, MATCH(A2, C:C, 0))

• C列からA2を検索し、同じ行のE列の値を返す。(検索列より左側も可能)
• :=INDEX(E:E, MATCH(A2, C:C, 0))

2次元の検索 (行と列の両方):

=INDEX(範囲, MATCH(行検索値, 行検索範囲, 0), MATCH(列検索値, 列検索範囲, 0))

具体例:

• A列とB行の両方で検索し、交差位置の値を返す。

  =INDEX(B2:E10, MATCH(A12, A2:A10, 0), MATCH(B1, B1:E1, 0))

INDEX関数 + MATCH関数のメリット:

• 検索列より左側の列も取得可能。
• 列の挿入・削除の影響を受けにくい。
• 複数条件での検索が可能。 (工夫が必要)
• 行と列の両方向で検索可能。

XLOOKUP関数 (新規関数)

XLOOKUP関数

VLOOKUP関数やHLOOKUP関数の後継となる強力な検索関数である。
Microsoft 365およびExcel 2021以降で使用可能である。

構文:

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

引数の説明:

• 検索値

  検索する値

• 検索範囲

  検索を行う範囲

• 戻り範囲

  取得する値が含まれる範囲

• 見つからない場合

  検索値が見つからないときに返す値 (省略時は #N/A)

• 一致モード

  0 (完全一致)

  -1 (完全一致または次に小さい値)

  1 (完全一致または次に大きい値)

  2 (ワイルドカード一致)

• 検索モード

  1 (先頭から検索)

  -1 (末尾から検索)

  2 (昇順の二分検索)

  -2 (降順の二分検索)

使用例:

• C列からA2セルを検索し、D列の値を返す。(基本的な使い方)

  =XLOOKUP(A2, C:C, D:D)

• 見つからない場合は「該当なし」を返す。

  =XLOOKUP(A2, C:C, D:D, "該当なし")

• 複数列を一度に取得する。 (D列とE列を返す)

  =XLOOKUP(A2, C:C, D:E)

• ワイルドカード検索 (部分一致)

  =XLOOKUP(A2, C:C, D:D, , 2)

※注意
検索範囲と戻り範囲のサイズは一致している必要がある。
VLOOKUP関数と異なり、検索列より左側の列も取得可能。 デフォルトで完全一致検索を行う。
エラー処理が組み込まれている。(見つからない場合の引数)

XLOOKUP関数のメリット

XLOOKUP関数は従来の検索関数の問題点を解決している。

VLOOKUPとの比較:

• 検索列の位置

  VLOOKUP: 左端のみ

  XLOOKUP: 任意の列

• 取得列の位置

  VLOOKUP: 検索列より右側のみ

  XLOOKUP: 任意の列

• エラー処理

  VLOOKUP: IFERRORが必要

  XLOOKUP: 引数で指定可能

• 複数列の取得

  VLOOKUP: 1列ずつ

  XLOOKUP: 一度に複数列可能

• 検索方向

  VLOOKUP: 先頭から

  XLOOKUP: 先頭・末尾から選択可能

INDEX関数 + MATCH関数との比較:

• 数式の簡潔さ

  INDEX + MATCH: 複雑

  XLOOKUP: シンプル

• 複数列の取得

  INDEX + MATCH: 繰り返しが必要

  XLOOKUP: 1度に可能

• エラー処理

  INDEX + MATCH: IFERRORが必要

  XLOOKUP: 引数で指定可能

XMATCH関数 (新規関数)

XMATCH関数

MATCH関数の後継となる検索位置を返す関数である。
Microsoft 365およびExcel 2021以降で使用可能である。

構文:

=XMATCH(検索値, 検索範囲, [一致モード], [検索モード])

引数の説明:

• 検索値

  検索する値

• 検索範囲

  検索を行う範囲

• 一致モード

  0 (完全一致)

  -1 (完全一致または次に小さい値)

  1 (完全一致または次に大きい値)

  2 (ワイルドカード一致)

• 検索モード

  1 (先頭から検索)

  -1 (末尾から検索)

  2 (昇順の二分検索)

  -2 (降順の二分検索)

使用例:

• C列でA2セルの位置を返す。

  =XMATCH(A2, C:C)

• 末尾から検索する。(最後に一致した位置を返す)

  =XMATCH(A2, C:C, 0, -1)

• INDEX関数と組み合わせて使用する。

  =INDEX(D:D, XMATCH(A2, C:C))

※注意
MATCH関数よりも機能が拡張されている。
ワイルドカード検索や検索方向の指定が可能。

LOOKUP関数

LOOKUP関数 (ベクトル形式)

1行または1列の範囲を検索し、対応する値を返す関数である。

構文:

=LOOKUP(検索値, 検索範囲, [対応範囲])

使用例:

• C列でA2を検索し、D列の対応する値を返す。

  =LOOKUP(A2, C2:C100, D2:D100)

※注意
検索範囲は昇順に並んでいる必要がある。
検索値以下の最大値を検索する。(完全一致ではない)
一般的には、VLOOKUP関数やINDEX関数 + MATCH関数の方が推奨される。

LOOKUP関数 (配列形式)

2次元配列から検索する形式である。

構文:

=LOOKUP(検索値, 配列)

※注意
この形式は使用頻度が低く、一般的には、VLOOKUP関数やINDEX関数を使用する。

選択関数

CHOOSE関数

インデックス番号に基づいて、値のリストから値を選択する関数である。

構文:

=CHOOSE(インデックス番号, 値1, [値2], ...)

引数の説明:

• インデックス番号

  1から始まる番号で、選択する値を指定

• 値1, 値2, ...

  選択肢のリスト

使用例:

• 2番目の値「火」を返す。

  =CHOOSE(2, "月", "火", "水", "木", "金")

• A1セルの値が3なら30を返す。

  =CHOOSE(A1, 10, 20, 30, 40, 50)

• 日付から曜日名を返す。

  =CHOOSE(WEEKDAY(A1), "日", "月", "火", "水", "木", "金", "土")

• A1の値に応じて参照する列を切り替える。

  =CHOOSE(A1, B:B, C:C, D:D)

※注意
インデックス番号は1から始まる。
インデックス番号が範囲外の場合は #VALUE! エラーになる。
値には、数値、文字列、セル参照、範囲、数式を指定できる。

CHOOSE関数の応用

CHOOSE関数は他の関数と組み合わせて柔軟な処理が可能である。

動的な列参照:

# A1セルの値に応じて、合計する列を切り替える
=SUM(CHOOSE(A1, B:B, C:C, D:D))

# 条件による計算式の切り替える (A1セルの値に応じて、異なる税率を適用する)
=CHOOSE(A1, B2*1.05, B2*1.08, B2*1.1)

動的参照関数

OFFSET関数

基準セルから指定した行数・列数だけ移動したセルまたは範囲を参照する関数である。

構文:

=OFFSET(基準, 行数, 列数, [高さ], [幅])

引数の説明:

• 基準

  基準となるセルまたは範囲

• 行数

  基準から移動する行数 (正: 下、負: 上)

• 列数

  基準から移動する列数 (正: 右、負: 左)

• 高さ

  参照する範囲の行数

• 幅

  参照する範囲の列数

使用例:

• A1から2行下、1列右のセル (B3) を参照

  =OFFSET(A1, 2, 1)

• A1からA5の範囲を参照

  =OFFSET(A1, 0, 0, 5, 1)

• A1から10行分の合計を計算

  =SUM(OFFSET(A1, 0, 0, 10, 1))

• 動的にセルを参照

  =OFFSET(A1, ROW()-1, 0)

※注意
動的な範囲参照に便利だが、計算が重くなる場合がある。
範囲が基準セルの外に出る場合は、#REF! エラーになる。
名前定義と組み合わせて動的な範囲を作成できる。

OFFSET関数の応用

OFFSET関数は動的な範囲作成や可変長の集計に使用される。

直近N件のデータを集計:

# 最新のデータから10件分の平均を計算する。
=AVERAGE(OFFSET(A1, COUNTA(A:A)-10, 0, 10, 1))

動的な範囲の名前定義を以下に示す。

• 名前

  データ範囲

• 参照範囲

  =OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)

データが追加されても自動的に範囲が拡張される。

INDIRECT関数

文字列で指定したセル参照を実際のセル参照に変換する関数である。

構文:

=INDIRECT(参照文字列, [参照形式])

引数の説明:

• 参照文字列

  セル参照を表す文字列

• 参照形式

  TRUE または省略 (A1形式)、FALSE (R1C1形式)

使用例:

• A1セルの値を返す

  =INDIRECT("A1")

• 同じ行のA列を参照 (A1、A2、A3...)

  =INDIRECT("A" & ROW())

• A1の値が「B」なら B1 を参照

  =INDIRECT(A1 & "1")

• A2の値に応じて合計範囲が変わる

  =SUM(INDIRECT("A1:A" & A2))

※注意
参照文字列が無効な場合は、#REF! エラーになる。
他のブックを参照する場合、そのブックを開いている必要がある。
計算が重くなる可能性があるため、過度な使用は避ける。

INDIRECT関数の応用

INDIRECT関数は動的なセル参照や、シート名の動的な切り替えに使用される。

シート名を動的に参照:

# A1セルに「Sheet1」と入力する時、Sheet1のB2セルを参照する
# A1セルに「Sheet2」と入力する時、Sheet2のB2セルを参照する
=INDIRECT(A1 & "!B2")

列を動的に切り替え:

# A1セルに「B」と入力する時、B2セル〜B100セルまでの合計を計算
# A1セルに「C」と入力する時、C2セル〜C100セルまでの合計を計算
=SUM(INDIRECT(A1 & "2:" & A1 & "100"))

行列情報関数

ROW関数 / COLUMN関数

セルの行番号または列番号を返す関数である。

構文:

=ROW([参照])
=COLUMN([参照])

引数の説明:

• 参照

  行番号または列番号を取得するセル (省略時は数式が入力されているセル)

使用例:

• =ROW()

  数式が入力されている行の番号を返す。

• =COLUMN()

  数式が入力されている列の番号を返す。

• =ROW(A10)

  10を返す。

• =COLUMN(D1)

  4 を返す (D列は4列目)

応用例:

• 連番の作成

  =ROW()-1

  2行目から入力すれば、1, 2, 3...

• 偶数行に色を付ける条件付き書式

  =MOD(ROW(), 2)=0

• 動的なセル参照

  =INDIRECT("A" & ROW())

ROWS関数 / COLUMNS関数

範囲内の行数または列数を返す関数である。

構文:

=ROWS(配列)
=COLUMNS(配列)

使用例:

• =ROWS(A1:A10)

  10を返す。(10行ある)

• =COLUMNS(A1:E1)

  5を返す。(5列ある)

• =ROWS(A:A)

  1048576を返す。(Excelの最大行数)

応用例:

• データの件数を取得

  =ROWS(A2:A100)

• 範囲のサイズを動的に計算

  =OFFSET(A1, 0, 0, ROWS(A:A), 1)

その他の検索関連関数

TRANSPOSE関数

行と列を入れ替える関数である。

構文:

=TRANSPOSE(配列)

使用例:

• 縦1列を横1行に変換

  =TRANSPOSE(A1:A5)

• 横1行を縦1列に変換

  =TRANSPOSE(A1:E1)

• 3行3列を行列入れ替え

  =TRANSPOSE(A1:C3)

※注意
配列数式として入力する必要がある。
Microsoft 365では自動的にスピルされる。

FILTER関数 (新規関数)

条件に一致する行を抽出する関数である。
Microsoft 365およびExcel 2021以降で使用可能である。

構文:

=FILTER(配列, 条件, [空の場合])

使用例:

• B列が100以上の行を抽出

  =FILTER(A2:C100, B2:B100>=100)

• A列が「東京」の行を抽出

  =FILTER(A2:C100, A2:A100="東京")

• 複数条件で抽出 (AND条件)

  =FILTER(A2:C100, (B2:B100>=100)*(C2:C100="完了"))

※注意
条件に一致する行がない場合は #CALC! エラーになる。
空の場合の引数でエラーを回避できる。
結果は動的にスピルされる。

SORT関数 / SORTBY関数 (新規関数)

データを並べ替える関数である。
Microsoft 365およびExcel 2021以降で使用可能である。

構文:

=SORT(配列, [並べ替えインデックス], [並べ替え順序], [並べ替え基準])
=SORTBY(配列, 基準配列1, [並べ替え順序1], ...)

使用例:

• 2列目で降順に並べ替え

  =SORT(A2:C100, 2, -1)

• B列を基準に降順で並べ替え

  =SORTBY(A2:C100, B2:B100, -1)

UNIQUE関数 (新関数)

重複を除いた一意の値を返す関数である。
Microsoft 365およびExcel 2021以降で使用可能である。

構文:

=UNIQUE(配列, [列の比較], [回数指定])

使用例:

• A列の重複を除いた一意の値を抽出

  =UNIQUE(A2:A100)

• 複数列で重複を確認

  =UNIQUE(A2:C100, FALSE, FALSE)

実用的な使用例

商品マスタからの情報取得

商品コードから商品名や単価を自動的に取得する例。

• VLOOKUP関数を使用
  • 商品名

    =VLOOKUP(A2, 商品マスタ!A:C, 2, FALSE)

  • 単価

    =VLOOKUP(A2, 商品マスタ!A:C, 3, FALSE)

  
  

• XLOOKUP関数を使用 (より簡潔)
  • 商品名

    =XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!B:B, "該当なし")

  • 単価

    =XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!C:C, 0)

  
  

• INDEX関数 + MATCH関数を使用 (最も柔軟)
  • 商品名

    =INDEX(商品マスタ!B:B, MATCH(A2, 商品マスタ!A:A, 0))

  • 単価

    =INDEX(商品マスタ!C:C, MATCH(A2, 商品マスタ!A:A, 0))

クロス集計表からのデータ取得

行と列の両方で検索してデータを取得する例。

• A15の値で行を検索し、B14の値で列を検索して、交差位置のデータを取得する。

  =INDEX(B2:E10, MATCH(A15, A2:A10, 0), MATCH(B14, B1:E1, 0))

複数条件での検索

2つ以上の条件で検索する例。

• 方法1 : 作業列を使用
  • 作業列

    =A2&B2

  • 検索式

    =VLOOKUP(X2&Y2, 作業列範囲:データ範囲, 列番号, FALSE)

  
  

• 方法2 : INDEX関数 + MATCH関数 + MATCH関数を使用

  配列数式として入力する。

  =INDEX(D2:D100, MATCH(1, (A2:A100=X2)*(B2:B100=Y2), 0))

エラー処理を含む検索

検索結果が見つからない場合のエラー処理を含む例。

• VLOOKUP関数 + IFERROR関数

  =IFERROR(VLOOKUP(A2, C:E, 2, FALSE), "該当なし")

• VLOOKUP関数 + IFNA関数 (より限定的なエラー処理)

  =IFNA(VLOOKUP(A2, C:E, 2, FALSE), "未登録")

• XLOOKUP (エラー処理が組み込まれている)

  =XLOOKUP(A2, C:C, D:D, "該当なし")

動的な範囲の集計

データの件数に応じて自動的に範囲が変わる集計の例。

• OFFSET関数を使用

  A列のデータ件数に応じて合計範囲が自動調整される。

  =SUM(OFFSET(A2, 0, 0, COUNTA(A:A)-1, 1))

最後のデータを取得

リストの最後の値を取得する例。

• A列で最後の空白でないセルの値を返す。
  • LOOKUP関数を使用

    =LOOKUP(2, 1/(A2:A100<>""), A2:A100)

  • INDEX + COUNTA を使用

    =INDEX(A:A, COUNTA(A:A))

別シートからのデータ取得

シート名を動的に切り替えてデータを取得する例。

• INDIRECT関数の使用

  =INDIRECT(A1 & "!B2")

A1に「1月」と入力すれば「1月」シートのB2を参照し、「2月」と入力すれば「2月」シートのB2を参照する。

重複チェック

データの重複をチェックする例。

• COUNTIF関数を使用

  A2の値がA2:A100の範囲内に複数ある場合「重複」と表示する。

  =IF(COUNTIF($A$2:$A$100, A2)>1, "重複", "")

• MATCH関数を使用 (初出のみを抽出)

  =IF(MATCH(A2, $A$2:A2, 0)=ROWS($A$2:A2), "初出", "重複")

順位に応じたデータの取得

TOP3やワースト5などを取得する例。

• LARGE関数 / SMALL関数 + INDEX関数 + MATCH関数の組み合わせ

  B列の値が大きい順に商品名を取得する。

  TOP1の商品名: =INDEX(A2:A100, MATCH(LARGE(B2:B100, 1), B2:B100, 0))

  TOP2の商品名: =INDEX(A2:A100, MATCH(LARGE(B2:B100, 2), B2:B100, 0))

  TOP3の商品名: =INDEX(A2:A100, MATCH(LARGE(B2:B100, 3), B2:B100, 0))

条件付きの連番

特定の条件を満たす行にのみ連番を振る例。

• A列が「対象」の行にのみ、1, 2, 3... と連番を振る。

  =IF(A2="対象", COUNTIF($A$2:A2, "対象"), "")