エクセルで商品名とサイズ、担当者と年月、部門と品目など、複数の条件がすべて一致するデータを検索したい場面は少なくありません。
VLOOKUP関数は本来ひとつの検索値で使う関数ですが、検索用の列を工夫して作成すれば、複数条件に対応できます。
条件のつなぎ方、検索範囲の指定、同じデータがある場合の注意点を理解しておくと、売上表や在庫表、名簿の管理がぐっと楽になります。
複数条件のVLOOKUPでは、検索条件を結合したキーを作る方法が基本です。
検索値と検索範囲の先頭列で、同じ順番で条件を結合することが重要です。
この記事では、VLOOKUP関数で複数条件に一致する値を検索する方法を、実務で使いやすいサンプルデータと数式で詳しく確認していきます。
VLOOKUP関数で複数条件を検索する基本式
それではまず、複数条件を結合してVLOOKUP関数で一致する値を取得する基本式について解説していきます。
| A列 商品名 | B列 サイズ | C列 単価 |
|---|---|---|
| ノート | A4 | 280 |
| ノート | B5 | 220 |
| ペン | 黒 | 150 |
上の表では、商品名だけで検索するとノートが複数行あるため、どのサイズの単価を返すべきか判断できません。
商品名とサイズをひとまとまりの検索キーとして扱うことで、該当する行を一意に特定できます。
検索キーを結合する考え方
VLOOKUP関数は、指定した検索範囲のいちばん左にある列から値を探します。
そのため、複数条件で検索したいときは、検索範囲の左端に商品名とサイズをつないだ補助列を置く方法が分かりやすい選択です。
たとえばA列の商品名とB列のサイズを結合すると、ノートA4、ノートB5、ペン黒という識別しやすい値になります。
条件の間にハイフンや縦棒などの区切り文字を入れると、元の値の境目が見やすくなります。
補助列の例としてD2に入力する数式です。
=A2&”|”&B2
この式では、A2の値、区切り記号、B2の値を順番に連結します。
オートフィルで下方向へコピーすると、各行に対応した検索キーが完成します。
この補助列は計算用の列なので、必要に応じて列幅を狭くしたり非表示にしたりしても問題ありません。
補助列を使うVLOOKUP関数の入力
検索したい商品名をF2、サイズをG2に入力し、H2に単価を表示するケースを考えます。
D列に検索キーがあり、E列に単価を配置した場合、H2には次の数式を入力します。
=VLOOKUP(F2&”|”&G2,$D$2:$E$100,2,FALSE)
F2&”|”&G2の部分は、入力された二つの条件を補助列と同じ形に結合した検索値です。
$D$2:$E$100は検索対象の範囲であり、先頭のD列には結合済みの検索キーが必要です。
3番目の引数である2は、検索範囲の左から2列目にある単価を返す指定になります。
最後のFALSEは完全一致検索を指定する重要な引数です。
商品名や担当者名などの文字列検索では、原則としてFALSEを設定すると誤った結果を防ぎやすくなります。
最初の一致データが返る仕組み
VLOOKUP関数は、検索キーが一致する行を上から順に探し、最初に見つかった値を返します。
したがって、同じ商品名とサイズの組み合わせが複数ある場合は、一覧の先頭にあるデータだけが表示されます。
売上履歴のように同じ組み合わせが繰り返される表では、日付や伝票番号も条件に追加する必要があるかもしれません。
一方で商品マスタのように、商品名とサイズの組み合わせが一行だけになる表なら、この方法は非常に扱いやすいでしょう。
【操作のポイント】補助列と検索値では、条件の並び順と区切り文字を完全にそろえます。
補助列なしで複数条件を指定する数式
続いては、元の表に補助列を追加せず、数式の中で複数条件を結合して検索する方法を確認していきます。
| A列 担当者 | B列 月 | C列 売上 |
|---|---|---|
| 田中 | 4月 | 125000 |
| 田中 | 5月 | 148000 |
| 佐藤 | 4月 | 98000 |
補助列を使わない方法では、CHOOSE関数を使って、数式内で一時的な二列の検索表を作成します。
元データの列構成を変えずに複数条件検索を実現できる点が、この方法の魅力です。
CHOOSE関数で仮想の検索範囲を作る方法
検索条件をF2の担当者、G2の月に入力し、H2に売上を表示する場合を例にします。
次の数式では、A列とB列を連結した値を仮想表の1列目にし、C列の売上を2列目にしています。
=VLOOKUP(F2&”|”&G2,CHOOSE({1,2},$A$2:$A$100&”|”&$B$2:$B$100,$C$2:$C$100),2,FALSE)
CHOOSE({1,2},値1,値2)は、値1と値2を横に並べた配列を作る書き方です。
ここでは値1が担当者と月を結合した検索キー、値2が返したい売上の列です。
見た目は長い数式ですが、補助列を置く方法と同じ検索の仕組みを数式の内部で組み立てています。
Microsoft 365や新しいExcelでは、通常どおりEnterキーで確定できます。
数式を入力したExcel画面のイメージ
数式バーにVLOOKUP関数を入力し、結果を表示するセルを選択する流れをイメージで確認していきます。
赤枠で示したF2とG2が検索条件、H2が結果を表示するセルです。
検索結果が数値の場合でも、元データが文字列として保存されていると一致しないことがあります。
数値と文字列の形式をそろえることも、複数条件検索では大切な確認項目です。
配列数式で起こりやすいエラー
古いExcelでは、CHOOSE関数を含む式を入力した後にCtrlキーとShiftキーとEnterキーを同時に押す必要がある場合があります。
数式バーの前後に波かっこが付く場合は、配列数式として確定されている状態です。
ただし、Microsoft 365では波かっこを自分で入力する必要はありません。
波かっこを直接書き込むと数式が正しく動かないため、Excelの確定操作に任せましょう。
【操作のポイント】補助列なしの式は便利ですが、共有するブックでは数式の意味をメモしておくと保守しやすくなります。
複数条件で一致しない原因と確認項目
続いては、VLOOKUP関数で複数条件を指定しても検索結果が表示されない原因を確認していきます。
| 検索条件 | 元データ | 確認する内容 |
|---|---|---|
| 東京 支店A | 東京 支店A | 余分な空白がないか |
| 0012 | 12 | 文字列と数値の違い |
| 2026年4月 | 2026/4/1 | 日付の実体値 |
エラーの多くはVLOOKUP関数そのものではなく、見た目では分かりにくいデータの違いによって起こります。
完全一致検索では、一文字の空白や表示形式の違いも別の値として扱われるため注意が必要です。
区切り文字と条件の順番
補助列でA列&”|”&B列と設定したなら、検索値側も必ずF2&”|”&G2と同じ順にします。
片方だけ商品名|サイズ、もう片方がサイズ|商品名になっていると、見た目に同じ情報を入力していても一致しません。
また、補助列でハイフンを使い、検索式では縦棒を使うと検索キーが異なる文字列になります。
補助列が=A2&”-“&B2なら、検索値も=F2&”-“&G2にそろえます。
結合の順番、区切り文字、参照セルの三つを確認しましょう。
数式をコピーして使うときは、検索範囲に絶対参照のドル記号を付けることも忘れないでください。
余分な空白と文字種の違い
コピーしたデータには、セルの末尾に半角スペースや全角スペースが含まれていることがあります。
画面上では気付きにくいものの、VLOOKUP関数では別の値として判定されます。
不要な空白を除去するには、TRIM関数を使う方法が有効です。
=TRIM(A2)&”|”&TRIM(B2)
TRIM関数は連続した半角スペースを一つにし、文字列の前後にある半角スペースを削除します。
全角スペースが混ざるデータでは、SUBSTITUTE関数で全角スペースを半角スペースへ置き換えてからTRIM関数を使う方法もあります。
英数字の半角と全角、ハイフンの種類、カタカナの表記揺れも一致しない原因になりがちです。
数値と日付のデータ形式
社員番号の0012のように先頭ゼロが意味を持つコードは、文字列として扱う必要があります。
片方が数値の12、もう片方が文字列の0012では、結合した検索キーが異なります。
日付も画面に表示される形式ではなく、Excel内部の連続番号で保存されているケースがあります。
日付を検索条件に使う場合は、TEXT関数で同じ表示形式の文字列に統一すると安定します。
=TEXT(A2,”yyyy/m/d”)&”|”&B2
検索値側にもTEXT関数を使い、同じ書式文字列を指定してください。
【操作のポイント】検索できないときは、結合後のキーを別セルに表示し、両者が本当に同じ文字列か目で比較します。
IFERROR関数を組み合わせた検索結果の表示
続いては、該当データがない場合にも見やすい表示に整えるIFERROR関数の使い方を確認していきます。
| F列 商品名 | G列 サイズ | H列 結果 |
|---|---|---|
| ノート | A4 | 280 |
| ノート | A5 | 該当データなし |
検索値が見つからないと、VLOOKUP関数は通常、#N/Aエラーを返します。
管理用の表や印刷する帳票では、エラーをそのまま表示するより、意味の分かるメッセージに変換すると親切です。
IFERROR関数の基本構文
IFERROR関数は、数式の結果がエラーになった場合だけ、指定した別の値を表示します。
VLOOKUP関数をIFERROR関数で囲むと、検索失敗時の#N/Aを任意の文字列に置き換えられます。
=IFERROR(VLOOKUP(F2&”|”&G2,$D$2:$E$100,2,FALSE),”該当データなし”)
検索に成功したときは単価が表示され、失敗したときだけ該当データなしと表示されます。
IFERROR関数は検索結果を見やすくするために便利ですが、数式の入力ミスまで隠してしまう可能性があります。
最初に数式が正しく動くことを確認してから使うと安心です。
空白を返す場合の使い方
検索欄を未入力のままにしたとき、該当データなしを表示したくない場合もあるでしょう。
この場合は、IF関数で入力セルが空白かどうかを先に判定します。
=IF(OR(F2=””,G2=””),””,IFERROR(VLOOKUP(F2&”|”&G2,$D$2:$E$100,2,FALSE),”該当データなし”))
F2またはG2が空欄なら空白を返し、両方が入力されている場合だけ検索を実行する数式です。
入力フォームのような表では、不要なメッセージを減らせるため見栄えが整います。
ただし、空欄そのものを検索条件として扱う必要がある表では、この式が適さないこともあります。
エラーの種類を見分ける視点
#N/Aは、検索値が見つからないときに起こる代表的なエラーです。
一方で#REF!は参照範囲が壊れている場合、#VALUE!は引数の形式に問題がある場合に表示されることがあります。
すべてのエラーを一律に隠すのではなく、作成途中はエラー内容を確認する姿勢も必要です。
完成した入力シートではIFERROR関数、検証中の数式では元のエラー表示という使い分けも実践的です。
【操作のポイント】検索結果が空白なのか、検索対象が存在しないのかを区別したい場合は、表示する文字列を目的に合わせて決めます。
VLOOKUP関数とXLOOKUP関数の使い分け
続いては、複数条件検索でVLOOKUP関数を使う場合とXLOOKUP関数を使う場合の違いを確認していきます。
| 関数 | 検索列の位置 | 複数条件への対応 |
|---|---|---|
| VLOOKUP | 検索範囲の左端 | 補助列またはCHOOSE関数 |
| XLOOKUP | 任意の列 | 条件式の掛け算も利用可能 |
VLOOKUP関数は多くのExcelで使える定番関数であり、既存の帳票や共有ブックとの互換性を重視する場面で役立ちます。
一方、新しいExcelを利用できるなら、XLOOKUP関数はより柔軟な検索方法を選べます。
VLOOKUP関数を選ぶ場面
社内で古いExcelを使う人がいる場合や、取引先へファイルを渡す場合は、VLOOKUP関数が無難なことがあります。
補助列を使う方法は、数式の仕組みを目で追いやすく、引き継ぎ時にも説明しやすい点がメリットです。
検索範囲の列構成が固定されている商品マスタや顧客マスタでは、VLOOKUP関数で十分に対応できるでしょう。
複数条件を一つのキーへ整理する発想は、関数が変わっても活用できる基本技術です。
XLOOKUP関数で結合キーを検索する式
XLOOKUP関数でも、条件を結合した検索キーを使えます。
補助列を使わずに担当者と月から売上を検索する式は、次のように書けます。
=XLOOKUP(F2&”|”&G2,$A$2:$A$100&”|”&$B$2:$B$100,$C$2:$C$100,”該当データなし”)
XLOOKUP関数では、検索する配列と返す配列を別々に指定します。
そのため、検索列が返したい列より右側にあっても対応しやすい構造です。
見つからない場合の表示も4番目の引数で直接指定できるため、IFERROR関数を重ねずに済むケースがあります。
条件式を使うXLOOKUP関数
Microsoft 365では、複数の条件式を掛け合わせて検索する書き方もできます。
=XLOOKUP(1,($A$2:$A$100=F2)*($B$2:$B$100=G2),$C$2:$C$100,”該当データなし”)
各条件が一致するとTRUEが1として扱われ、二つの条件がともに一致した行だけが1になります。
この式は補助列が不要で、条件を追加する場合も式の構造を保ちやすい方法です。
ただし、XLOOKUP関数が使えないExcelでは動作しないため、ファイルの利用環境を事前に確認しましょう。
【操作のポイント】共有先のExcelバージョンが不明なときは、補助列を使うVLOOKUP関数が安定した選択です。
複数条件VLOOKUP関数の活用場面
続いては、複数条件を指定するVLOOKUP関数が役立つ具体的な活用場面を確認していきます。
| 条件1 | 条件2 | 取得する値 |
|---|---|---|
| 商品コード | カラー | 在庫数 |
| 社員番号 | 対象月 | 勤怠時間 |
| 取引先 | 商品区分 | 掛率 |
二つの項目だけではデータを特定できない表で、複数条件検索は特に効果を発揮します。
検索キーを設計する段階で、何を一意に決めるための条件なのか考えることが重要です。
商品マスタと在庫管理
商品コードが同じでも、色やサイズごとに単価や在庫数が異なる商品は多くあります。
商品コードとバリエーションを結合したキーを作れば、受注入力時に該当商品の単価を自動表示できます。
入力ミスを減らしながら、商品ごとの細かな違いを反映できることが大きな利点です。
商品名をキーにする場合は表記揺れが起こりやすいため、可能なら商品コードのような固定コードを条件に使いましょう。
担当者別の月次集計
営業担当者と対象月を条件にして、売上目標、実績、経費などを参照する使い方もあります。
担当者名だけで検索すると月が違う実績を返してしまうため、月の条件を追加する必要があります。
日付を月単位で扱う場合は、元データと検索欄で年月の表現を統一することが欠かせません。
たとえば2026年4月を文字列として使うのか、月初日の日付として使うのかを、ブック全体で決めておくとトラブルを防げます。
検索キーの重複を調べる方法
複数条件検索の前に、結合したキーが本当に一意かどうかを確認すると安心です。
補助列のキーを対象にCOUNTIF関数を使うと、同じキーが何件あるかを調べられます。
=COUNTIF($D$2:$D$100,D2)
結果が2以上になる行は、同じ複数条件のデータが重複している可能性があります。
VLOOKUP関数は重複をエラーにせず最初の一件を返すため、集計前に重複の有無を確認する習慣が役立ちます。
【操作のポイント】複数条件は多いほどよいわけではなく、対象行を一意に特定できる必要最小限の組み合わせにします。
まとめ エクセルでVLOOKUP関数に複数条件を指定して一致する値を検索する方法
エクセルでVLOOKUP関数に複数条件を指定するには、各条件を結合した検索キーを作る方法が基本です。
もっとも分かりやすい方法は、元データに補助列を追加し、商品名とサイズ、担当者と月などを同じ順序で連結する方法です。
検索式では、検索欄の条件も補助列と同じ区切り文字で結合し、FALSEを指定して完全一致検索を行います。
補助列を増やせない場合は、CHOOSE関数で仮想的な検索範囲を作る方法も利用できます。
検索できないときは、条件の順番、区切り文字、余分な空白、数値と文字列、日付形式を確認しましょう。
IFERROR関数を組み合わせれば、#N/Aではなく該当データなしなどの分かりやすい表示に整えられます。
新しいExcelではXLOOKUP関数も選択肢になりますが、幅広い環境で使うブックならVLOOKUP関数と補助列の組み合わせが実用的です。
検索キーが一意であることを確認してから数式を作ると、複数条件検索を正確で扱いやすい仕組みにできます。
