【Excel】エクセルで一致したら別のセルの値を返す方法（データを抽出）

Excelで商品コードや社員番号などが一致したとき、対応する商品名、単価、担当者名などを別の表から返したい場面は多くあります。

手入力で転記すると時間がかかるうえ、入力ミスや参照漏れも起こりやすくなります。

そこで役立つのが、検索用の関数を使って一致するデータを抽出する方法です。

この記事では、エクセルで一致したら別のセルの値を返す基本式から、複数条件での抽出、エラーを防ぐ考え方まで解説していきます。

 

一致したら別セルの値を返すXLOOKUP関数

それではまず、現在のExcelで使いやすいXLOOKUP関数によるデータ抽出について解説していきます。

入力表の商品コード	返したい商品名
A-101	検索結果を表示

 

検索値と検索範囲の指定

たとえば、入力用シートのA2セルに商品コードを入力し、商品マスターシートのA列に商品コード、B列に商品名があるケースを考えます。

商品名を表示する入力用シートのB2セルには、検索する値、検索する列、返す列の順番でXLOOKUP関数を設定します。

最初のA2は探したい商品コードです。

商品マスター!A:Aは、商品コードが並ぶ検索範囲です。

商品マスター!B:Bは、一致した行から商品名を取り出す戻り範囲となります。

最後の該当なしは、商品コードが見つからないときに表示する文字列です。

この指定により、A2にA-101と入力すると、商品マスターから対応する商品名がB2へ返ります。

列全体を範囲にしても動作しますが、大量データではA2:A1000のように必要な範囲へ絞ると計算負荷を抑えやすくなります。

 

完全一致による安全な抽出

XLOOKUP関数は、特別な指定をしなければ基本的に完全一致で検索します。

つまり、A-101とA-101は一致しますが、A-10やA-101Aは別の値として扱われます。

商品コードや社員番号の検索では完全一致が基本です。

似た番号を誤って取得すると、金額や在庫数などの重要なデータがずれるおそれがあります。

数値を検索するときは、入力表と検索表の両方で数値として保存されているかも確認しましょう。

片方が文字列、もう片方が数値になっていると、画面上では同じ123に見えても一致しない場合があります。

セル左上の緑色の三角形や、表示形式の違いがあるときは注意が必要です。

 

オートフィルによる数式のコピー

B2セルに式を入力できたら、セル右下の小さな四角形であるフィルハンドルを下へドラッグします。

これにより、B3ではA3、B4ではA4を検索する式へ自動調整されます。

検索表の参照範囲はシート名を含めて指定するため、コピー後も同じマスターを参照できます。

入力表をテーブル化している場合は、数式を1行入れるだけで列全体へ自動反映されることもあります。

検索結果が正しいかどうかは、最初に数件だけ元のマスター表と照合すると安心です。

 

VLOOKUP関数によるデータ抽出

続いては、古いExcel環境でも利用されやすいVLOOKUP関数による検索方法を確認していきます。

A列の商品コード	B列の商品名	C列の単価
A-101	ノート	120

 

基本構文と列番号の考え方

VLOOKUP関数は、指定した表の左端列で値を探し、右側にある列の値を返す関数です。

入力用シートのA2に商品コードがあり、商品マスターのA列からC列に情報がある場合、単価を返す式は次のようになります。

A2は検索値です。

商品マスター!$A$2:$C$100は検索に使う表全体です。

3は、指定した表の左から数えて3列目の値を返す指定です。

FALSEは完全一致を意味します。

VLOOKUP関数でコード検索を行うときは、最後の引数をFALSEにすることが重要です。

FALSEを省略すると近似一致になる場合があり、並び順や値によっては意図しないデータを返すかもしれません。

 

絶対参照による検索表の固定

VLOOKUP関数を下方向へコピーする場合、検索表の範囲がずれないように絶対参照を使います。

数式中の$A$2:$C$100のように、列記号と行番号の前へ$を付ける書き方が絶対参照です。

数式を入力中に範囲を選択してF4キーを押すと、参照形式を切り替えられます。

検索値のA2は行ごとに変え、検索表だけを固定すると覚えると分かりやすいでしょう。

検索表を固定しないままコピーすると、2行目ではA2:C100だった範囲が、3行目ではA3:C101へずれてしまいます。

その結果、表の先頭データを参照できず、抽出漏れにつながります。

 

VLOOKUP関数の制約と使い分け

VLOOKUP関数は便利ですが、検索値が検索表の最も左側に置かれている必要があります。

たとえばB列の商品コードからA列の分類を返すような、左方向への検索はVLOOKUP関数だけではできません。

また、列を途中へ挿入すると列番号が変わり、返す値がずれる可能性もあります。

新しいExcelを使用できるなら、列番号を数えなくてよいXLOOKUP関数のほうが管理しやすい場面もあります。

一方で、取引先や共有先が旧バージョンのExcelを使っている場合には、VLOOKUP関数の知識が役立ちます。

 

INDEX関数とMATCH関数による柔軟な検索

続いては、列の並び順に左右されにくいINDEX関数とMATCH関数を組み合わせた抽出方法を確認していきます。

A列の分類	B列の商品コード	C列の商品名
文具	A-101	ノート

 

MATCH関数による一致位置の取得

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

商品コードA-101が商品マスターのB2:B100の中で何番目かを調べる式は次のとおりです。

最後の0は完全一致を表します。

検索値がB2:B100の先頭にあれば1、2番目にあれば2が返ります。

この位置番号だけでは商品名は表示されませんが、次のINDEX関数と組み合わせる土台になります。

 

INDEX関数による別列の値の返却

INDEX関数は、指定範囲の中から行番号や列番号に対応する値を取り出す関数です。

商品コードがB列にあり、左側のA列にある分類を返したい場合は、次の数式を使います。

MATCH関数が見つけた行位置を、INDEX関数が利用して分類を返す仕組みです。

検索列より左にある値も取得できるため、VLOOKUP関数では対応しにくい表でも利用できます。

数式を入力したセルのフィルハンドルを下へコピーすれば、各行の商品コードに対応した分類を返せます。

 

表の変更に強い参照設定

INDEX関数とMATCH関数の組み合わせは、途中に列を挿入しても返す列番号を数え直す必要が少ない方法です。

返したい列そのものをINDEX関数の範囲として明示するため、数式の意味を確認しやすい特徴があります。

検索表の列構成が変わる可能性がある業務ファイルでは、柔軟な参照方法として有効です。

ただし、関数を初めて使う場合はXLOOKUP関数より式が長く感じるかもしれません。

まずMATCH関数で位置を探し、その位置をINDEX関数へ渡す二段階の考え方を押さえましょう。

 

複数条件による一致データの抽出

続いては、商品コードと店舗名など、複数の条件がそろったときだけ別セルの値を返す方法を確認していきます。

店舗	商品コード	在庫数
東京店	A-101	35

 

検索用キーの作成

同じ商品コードが複数店舗に存在する場合、商品コードだけでは正しい在庫数を特定できません。

このようなときは、店舗名と商品コードをつないだ検索用キーを作成する方法が分かりやすいでしょう。

たとえば検索表でD2セルに、次の式を入力します。

東京店とA-101なら、東京店_A-101という一意の値になります。

入力側にも同じ形式のキーを作り、XLOOKUP関数で検索します。

複数条件をひとつの検索値にまとめると、通常の検索関数で扱いやすくなります。

 

複数条件でのXLOOKUP関数

補助列を作らずに、複数条件を直接指定してXLOOKUP関数で検索することも可能です。

入力側で店舗名がA2、商品コードがB2にあり、検索表のA列が店舗、B列が商品コード、C列が在庫数である場合の式は次のようになります。

それぞれの条件が一致するとTRUEが1として扱われ、両方が一致する行だけが1になります。

その1を検索値として指定することで、該当行の在庫数を返す仕組みです。

数式が長くなるため、シート名や範囲を間違えないように確認しましょう。

 

重複データへの対応

検索表に同じ条件のデータが複数ある場合、XLOOKUP関数は原則として最初に見つかった値を返します。

複数の結果をすべて表示したい場合は、FILTER関数を使う方法が適しています。

FILTER関数は条件に一致する複数行をまとめて抽出できる関数です。

ただし、結果が下方向へ広がるため、数式の下に値が入力されていないか確認する必要があります。

 

エラーと空白を防ぐ数式設定

続いては、検索値が見つからないときのエラー表示や、空白セルへの対応を確認していきます。

入力コード	検索結果
未入力	空白表示

 

見つからない値への表示設定

VLOOKUP関数やMATCH関数で検索値が見つからない場合、通常は#N/Aエラーが表示されます。

エラーを利用者に見せたくない場合は、IFERROR関数で式全体を囲みます。

この式では、検索に失敗したときだけ該当なしを表示します。

エラーを単に空白へ隠すより、該当なしと表示したほうが入力漏れに気付きやすい場面もあります。

業務の目的に合わせて表示文字を決めましょう。

 

入力前の空白セルへの対応

入力セルが空白なのに検索式だけが入っていると、不要な該当なしや0が表示される場合があります。

入力セルが空白なら結果も空白にするには、IF関数を先頭に追加します。

A2が空白なら空白文字を返し、値が入ったときだけXLOOKUP関数を実行します。

入力フォームや見積書のように、未入力行が多いシートで見やすくなる設定です。

 

文字列と数値の不一致確認

検索式が正しく見えるのに該当なしとなる場合、文字列と数値の形式が食い違っていることがあります。

たとえば、検索表の00123が文字列で、入力側の123が数値なら一致しません。

先頭の0を残すコードは、両方のセルを文字列形式にそろえる必要があります。

見た目ではなく、セルに保存されている値とデータ型をそろえることが検索成功の条件です。

余分な空白が混じる場合はTRIM関数、印刷できない文字が混じる場合はCLEAN関数も役立ちます。

 

検索表を管理しやすくするテーブル設定

続いては、データ量が増えても数式を保守しやすい検索表の作り方を確認していきます。

商品コード	商品名	単価
A-101	ノート	120

 

Excelテーブルへの変換

商品マスターの範囲を選択してCtrlキーとTキーを押すと、Excelテーブルへ変換できます。

先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認して作成します。

テーブル化すると、新しい商品を末尾に追加したとき、検索範囲も自動的に広がります。

固定のA2:A100のような範囲指定を更新し続ける手間を減らせるのが大きな利点です。

 

構造化参照による数式

商品マスターという名前のテーブルを作成した場合、XLOOKUP関数は列名を使って記述できます。

数式を見ただけで、商品コードから商品名を返す処理だと分かりやすくなります。

列の追加や行の追加にも対応しやすいため、継続的に利用する管理表に向いています。

 

検索キーの重複チェック

商品コード、社員番号、顧客IDなどの検索キーは、原則として重複しない状態で管理します。

重複があると、検索関数はどの行を返すべきか判断できず、先頭のデータだけを返すことがあります。

条件付き書式の重複する値を使えば、重複したコードを目立たせることができます。

検索式の正確さは、数式だけでなく元データの整備によって支えられます。

更新担当者が複数いる表では、入力規則でコードの形式を統一することも有効です。

 

まとめ エクセルで一致したら別セルの値を返す方法

エクセルで一致したら別のセルの値を返すには、検索値と検索表の対応関係を整え、目的に合う関数を選ぶことが基本です。

新しいExcelでは、XLOOKUP関数を使うと検索列と返す列を明確に指定できるため、最初の選択肢としておすすめです。

旧バージョンとの互換性が必要な場合は、VLOOKUP関数で左端列から右側の値を取得できます。

左方向への検索や列挿入に強い式が必要なら、INDEX関数とMATCH関数の組み合わせが役立ちます。

店舗名と商品コードのように条件が複数あるときは、検索用キーを作る方法、または複数条件を掛け合わせるXLOOKUP関数を使いましょう。

検索で該当なしが出る場合は、数式だけでなく、文字列と数値の違い、先頭の0、余分な空白、検索キーの重複を確認することが大切です。

検索表をテーブル化しておけば、データ追加後も数式を修正する手間が減り、日々の転記作業をより正確かつ効率的に進められます。