エクセルで列方向に検索する方法

この記事では、エクセルで列方向に検索する方法をご紹介します。

データ数が多い表やリストを扱う際、目視で必要なデータを探し出すのは大変です。また、ミスの原因にもなるのでおすすめしません。

以下では、列方向に検索する方法はVLOOKUP関数が一般的ですが、より便利なXLOOKUP関数の使い方と、応用の仕方をご紹介しています。

まず、「列方向」と聞くと縦列なのか横列なのか迷う場合があります。

サイトによって「列方向」の定義が異なることもあるので、欲しい情報が得られないことがあります。

この記事では、上記の画像のように縦列を「列方向」としてご説明します。

以下では、定番のVLOOKUP関数や新しく加わったXLOOKUP関数を使って列方向に検索する方法をご紹介します。

以下では、VLOOKUP関数を使って列方向（縦）に検索して対応する行のデータを取り出す方法をご紹介します。

VLOOKUP関数は検索値を選択範囲で縦方向に検索し、一致する値に対応する値を抽出します。例では「氏名」小林翔太を検索値として左の表で検索して検索値に完全に一致する場合は指定した4列目のデータである「所属チーム」からBチームを抽出します。以下では、手順をご紹介します。

上記の画像のようなデータを用意します。F3セルは検索値です。また、検索値は必ず範囲の1列目（左端）であることを確認してください。2列目以降に設定すると正しい結果が得られないのでご注意ください。

F3セルに検索値（例：小林翔太）を入力します。

G3セルに「=VLOOKUP(F3,A3:D12,4,FALSE)」を入力し、Enterを押します。VLOOKUP関数で、「検索値」小林翔太をA3セルからD12セルの範囲で検索して完全に一致した場合は、「列番号」の引数で指定した4列目の値をG3セルに返します。

G3セルにBチームが表示されました。検索値を列方向に検索して、対応するデータを抽出することができました。

HLOOKUP関数は検索値を選択範囲で横方向に検索し、一致する値に対応する値を抽出します。

例では「氏名」を検索値として下の表で検索し、検索値に完全に一致する場合は、「行番号」の引数で指定した4行目の値「所属チーム」を抽出します。

検索値が入力されている列が必ず範囲の1行目に設定します。2行目以降に設定すると正しい結果が得られないのでご注意ください。

HLOOKUP関数の引数や使い方は以下の記事でご紹介しています。

以下では、XLOOKUP関数をネストして表の列方向（縦）と行方向（横）に検索する方法をご紹介します。

ネストとは、関数の中に関数を含める数式の作成方法です。

上記の画像のようなデータを用意します。

例では「年齢区分」と「チケット区分」を入力して、「料金」がいくらになるかを求めます。

B7セルに検索値（例：大学生）を入力します。

ネストするXLOOKUP関数の引数に指定する検索値（例：土日祝）を入力します。

B9セルに「=XLOOKUP(B7,B1:F1,XLOOKUP(B8,A2:A5,B2:F5))」を入力し、Enterを押します。

B7セルの値に一致するデータをB1セルからF1セルの範囲で検索します。次に、B8セルの値に一致するデータをA2セルからA5セルの範囲で検索します。

B2セルからF5セルの範囲で2つの検索値が交差する値をB9セルに返します。

XLOOKUP関数は範囲または配列で検索値に一致するデータを探し、指定した範囲から検索値に対応する値を返します。書式は「=XLOOKUP（検索値,検索範囲,戻り配列,[一致モード],[検索モード]）」です。

基本的な使い方や引数の説明は以下の記事でご紹介しています。

B9セルに検索値に対応する「料金」が表示されました。

検索値を入れ替えると、区分に合った料金が表示されます。

以下では、検索値が存在しない場合に指定した数値や文字列を返す方法をご紹介します。

上記の画像のようなデータを用意します。

例では「商品コード」を検索値として、商品リストから一致するデータを検索します。一致する「商品コード」がある場合はB2セルに「単価」が返されますが、存在しない場合は指定した文字列の「在庫なし」が返されます。

A3セルに検索値（例：1004）を入力します。

B3セルに「=XLOOKUP(A3,A7:A12,C7:C12,"在庫なし")」を入力し、Enterを押します。

A31セルの検索値をA7セルからA12セルの範囲で検索して一致する場合は、対応するC7セルからC12セルの値をB3セルに返します。

検索値が一致しないまたは存在しない場合は指定した「在庫なし」の文字列をB3セルに返します。

XLOOKUP関数は、検索値が範囲のデータに一致しない場合のエラー処理ができます。

検索値が範囲に存在しないため、B3セルに指定した文字列が返されました。