ExcelのVLOOKUP関数で複数条件を指定する方法をお探しですね。
広告
ExcelのVLOOKUPで2つの条件を使って検索する方法【作業列で簡単に実現】
Excelで表からデータを探すとき、VLOOKUP関数はとても便利ですよね。
でも実際の仕事では、「商品コードだけじゃなくて、店舗名も一致する行を見つけたい」とか「社員番号と年月の両方が合うデータがほしい」みたいに、1つの条件だけでは足りない場面がよくあります。
VLOOKUP関数は基本的に、検索する値を1つしか指定できません。
でも、ちょっとした工夫で2つの条件を使った検索ができるんです。
その方法が「作業列」を使うやり方。
2つの条件を組み合わせて1つの検索キーにしてしまえば、VLOOKUPでもちゃんと複数条件の検索ができます。
この記事では、VLOOKUPで「2つの条件を両方満たす」検索をする裏技を、初心者の方にも分かりやすく説明していきます。
VLOOKUPで2つの条件を使った検索ってどうやるの?
まず、VLOOKUP関数のおさらいから。
この関数は、指定した値を表の一番左の列から探して、同じ行にある別の列の値を取り出してくれる関数です。
書き方は「=VLOOKUP(検索値, 範囲, 列番号, 検索の型)」で、最後の「検索の型」には完全一致を意味する「FALSE」を指定するのが実務では基本です。
とても便利な関数なんですが、検索値として指定できるのは1つだけ。
だから「商品コードがA001で、しかも店舗が東京」みたいな2つの条件を、そのまま指定することはできないんです。
そこで登場するのが「作業列」です。
作業列っていうのは、最終的に見せたい表とは別に、計算や検索をやりやすくするために追加する補助的な列のこと。
たとえば、商品コードが「A001」で店舗名が「東京」なら、作業列に「A001|東京」みたいな文字列を作っておくんです。
検索するときも同じように「A001|東京」という値を作れば、VLOOKUPから見ると条件は1つになります。
つまり、VLOOKUP関数そのものを変えるんじゃなくて、検索する前のデータを工夫して複数条件に対応させる、という考え方なんですね。
この方法のいいところは、古いバージョンのExcelでも使えること。
Microsoft 365やExcel 2021以降ならXLOOKUP関数やFILTER関数という便利な関数もあるんですが、会社の共有ファイルでは古いExcelを使っていることも多いですよね。
作業列を使ったVLOOKUPなら、どのバージョンでも使えるし、数式の中身も目で確認しやすいんです。
まずは「複数の条件をくっつけて、1つの検索キーにする」っていう基本を押さえておくと、このあとの説明もスムーズに理解できると思います。
作業列を使った複数条件検索の具体的な手順
では、実際にどうやるのか見ていきましょう。
例として、売上一覧から「商品コード」と「店舗名」の2つが一致する行の「売上金額」を取り出すケースで考えます。
元のデータには、商品コード、店舗名、商品名、売上金額の列があるとします。
VLOOKUPで検索できるようにするには、表の一番左に作業列を追加して、商品コードと店舗名をくっつけた検索キーを作ります。
VLOOKUP関数は検索範囲の左端の列を検索するので、作業列はできるだけ一番左に置くのがポイントです。
たとえば、こんな感じで列を配置します。
– A列:作業列
– B列:商品コード
– C列:店舗名
– D列:商品名
– E列:売上金額
A2セルには、次のように入力します。
“`
=B2&”|”&C2
“`
この数式は、B2セルの商品コードとC2セルの店舗名を「|」(縦棒)でつないでいます。
なぜ「|」を入れるかというと、条件同士の境目をはっきりさせるため。
たとえば「AB」と「C」をつなげた値と、「A」と「BC」をつなげた値は、単純につなげるとどちらも「ABC」になっちゃいますよね。
でも「AB|C」「A|BC」みたいに区切り文字を入れれば、別の検索キーとして区別できるんです。
この数式を下の行にコピーすれば、各行に複数条件用の検索キーができあがります。
次に、検索条件を入力するセルを用意します。
たとえば、
– H2セル:商品コード
– I2セル:店舗名
– J2セル:売上金額を表示
という形にしたい場合、J2セルには次のように入力します。
“`
=VLOOKUP(H2&”|”&I2,$A$2:$E$100,5,FALSE)
“`
この数式では、検索値として「H2&”|”&I2」を指定しています。
元データ側の作業列と同じルールで検索キーを作っているので、商品コードと店舗名の両方が一致する行だけを探せるわけです。
範囲はA列の作業列からE列の売上金額までを指定。
売上金額は範囲内の5列目にあるので、列番号は「5」になります。
最後に「FALSE」を指定することで完全一致検索になって、似た値じゃなく完全に一致した検索キーだけを対象にできます。
作業列方式でミスを防ぐためのチェックポイント
作業列を使う方法はシンプルなんですが、いくつかのポイントを押さえないと、思った値が返ってこなかったり、違う行のデータを拾ってきちゃったりすることがあります。
特に大事なのは、**元データ側と検索側でまったく同じルールの検索キーを作ること**。
元データでは「商品コード|店舗名」としているのに、検索側で「店舗名|商品コード」としちゃうと一致しません。
また、区切り文字の有無や、全角半角の違い、余分なスペースも検索結果に影響します。
実務でよくあるミスを減らすには、次の点をチェックしましょう。
– **作業列はVLOOKUPの検索範囲の左端に置く**
– **元データ側と検索側で、条件をつなげる順番と区切り文字をそろえる**
– **検索の型には「FALSE」を指定して、完全一致で検索する**
– **条件の列に余分なスペースや表記のゆれがないか確認する**
特にスペースの混入は、見た目で気づきにくいエラーの原因です。
「東京」と「東京 」(最後にスペース)は、人の目にはほとんど同じに見えても、Excelでは別の文字列として扱われます。
必要に応じてTRIM関数を使って、余分なスペースを取り除いてから検索キーを作ると精度が上がります。
たとえば、作業列を次のようにしておくと、前後の不要なスペースによる不一致を減らせます。
“`
=TRIM(B2)&”|”&TRIM(C2)
“`
ただし、商品コードなどで意図的にスペースを含む運用をしている場合は、データのルールを確認してから使うことが大切です。
もう1つ知っておきたいのは、**2つの条件を満たす行が複数ある場合、VLOOKUPは最初に見つかった行の値だけを返す**ということ。
たとえば「商品コードA001、店舗名東京」のデータが複数行ある場合、2行目以降の一致データは無視されます。
これはVLOOKUP関数の仕様なので、複数件を一覧で抽出したい場合は、FILTER関数やピボットテーブル、Power Queryなど別の方法を検討したほうがいいです。
作業列方式は、「2つの条件で1件の値を特定して返す」用途に向いていると考えると、使いどころを間違えにくくなります。
エラー表示が気になる場合は、IFERROR関数を組み合わせると見やすくなります。
たとえば、検索条件に一致するデータがないときに空白を表示したい場合は、次のように書けます。
“`
=IFERROR(VLOOKUP(H2&”|”&I2,$A$2:$E$100,5,FALSE),””)
“`
この数式では、VLOOKUPでエラーが出た場合に空白を返します。
空白じゃなく「該当なし」と表示したい場合は、最後の部分を「”該当なし”」に変更すればOKです。
ただ、エラーを隠すだけでは原因が分からなくなることもあるので、完成前の確認段階ではあえてエラーを表示させて、数式や検索キーが正しいかを確認してからIFERRORを加えるといいですよ。
作業列方式が向いているケースと他の方法との使い分け
作業列を使ったVLOOKUPの複数条件検索は、社内で共有するExcelファイルや、いろんなバージョンのExcelで使う資料に向いています。
数式が比較的短くて、作業列に作られた検索キーを見れば、どの条件で検索しているのかが分かりやすいからです。
関数に慣れていない人がファイルを引き継ぐ場合でも、非表示にした複雑な配列数式より、作業列で段階的に処理しているほうが理解しやすいことがあります。
特に、毎月同じ形式の表で売上、在庫、勤怠、請求データなどを照合する業務では、安定して使いやすい方法です。
一方で、作業列を増やしたくない場合や、検索条件が3つ以上に増える場合、左側に列を追加できない場合には、別の方法も検討できます。
たとえば、Excelの新しい環境ならXLOOKUP関数を使って複数条件検索を行う方法があります。
また、INDEX関数とMATCH関数を組み合わせれば、VLOOKUPのように検索列が左端にある必要はありません。
ただし、これらの方法は数式がやや複雑になりやすいので、ファイルを使う人のExcelスキルや利用環境に合わせて選ぶことが大切です。
作業列方式の最大の強みは、**仕組みが分かりやすくて、トラブル時に原因を追いやすい**こと。
検索結果がおかしいときも、まず作業列の検索キーと検索側のキーを見比べれば、条件の順番、区切り文字、スペース、表記のゆれなどをすぐに確認できます。
すべてを1つの複雑な数式にまとめるより、途中経過が見えるぶん、実務では早く問題解決できることが多いんです。
作業列は見た目が気になる場合、列を非表示にしたり、表の右端に置いて管理したりすれば、資料としての見やすさも保てます。
まとめ
ExcelのVLOOKUPで「2つの条件を満たす」複数条件検索を行う裏技は、難しい関数を覚えることじゃなくて、検索前のデータ設計をちょっと工夫することなんです。
作業列で複数の条件を1つの検索キーにまとめて、VLOOKUPではそのキーを完全一致で探すだけ。
ポイントは次の3つです。
1. **作業列を検索範囲の左端に置く**
2. **連結ルールをそろえる**(元データと検索側で同じ順番、同じ区切り文字)
3. **区切り文字を入れて誤一致を防ぐ**
この基本を押さえておけば、商品コードと店舗、社員番号と年月、顧客名と案件名など、いろんな2条件検索に応用できます。
ぜひ試してみてください!
広告
