VLOOKUP関数は、1つの検索値でしか探せません。そのため「支店と商品名の両方が一致する行の単価を出したい」のような複数条件の検索は、そのままでは書けません。
結論から言うと、条件をつなげた「作業列」を作り、つなげた文字で検索するのが、どのバージョンでも使える確実な方法です。この記事では、手順を図解で説明し、うまくいかないときの原因と、XLOOKUPやINDEX+MATCHを使う方法までまとめます。
=VLOOKUP(F2&G2,A2:D7,4,FALSE)VLOOKUPで複数条件を検索する考え方
VLOOKUPは、検索値を範囲の「一番左の列」から探します。つまり、探せるのは1列だけです。そこで、複数の列の内容を「&」でつないだ列を左端に作り、検索する側も同じようにつなげて探します。
たとえば、支店が「大阪」で商品が「みかん」の単価を探したいなら、「大阪みかん」という1つの文字にして探します。
- 作業列：B列（支店）とC列（商品）をつないだ列を、表の一番左に作る
- 検索値：探したい支店と商品を「&」でつなぐ
- 範囲：作業列を一番左に含めた表全体を指定する
手順1：作業列を作る
表の左端に列を挿入し、支店と商品名をつなぐ式を入れます。ここでは、A列を作業列にします。
- 表の一番左の列（A列）を右クリックして「挿入」を選び、空の列を作る
- A2に「=B2&C2」と入力して Enter を押す
- A2を選び、右下のハンドルをA7までドラッグしてコピーする
手順2：VLOOKUPで検索する
作業列ができたら、検索用のセルを用意します。ここでは、F2に支店名、G2に商品名を入力し、H2に単価を表示します。
- H2を選び「=VLOOKUP(」と入力する
- 検索値として「F2&G2」を入力し、カンマを入れる
- 範囲として作業列を含む「A2:D7」を選び、カンマを入れる
- 列番号「4」、検索方法「FALSE」を入力して「)」で閉じ、Enter を押す
うまくいかないときのチェックポイント
- 作業列が一番左にあるか：範囲の左端が作業列でないと、正しく検索できません
- つなぎ方がそろっているか：作業列が「支店＋商品」なら、検索値も同じ順番（F2&G2）でつなぎます
- 余分なスペースがないか：セルの前後にスペースが入っていると一致しません。TRIM関数で取り除いてから検索します
- 範囲が固定されているか：下の行へコピーするときは、範囲を「$A$2:$D$7」のように固定します
- 数値と文字列の違い：見た目が同じでも、数値と文字列では一致しないことがあります
数式をコピーして使うなら、範囲を絶対参照にしておくのが安全です。
=VLOOKUP(F2&G2,$A$2:$D$7,4,FALSE)作業列を使わない方法とその使い分け
作業列を作りたくないときは、XLOOKUP関数やINDEX関数とMATCH関数の組み合わせでも、複数条件の検索ができます。
XLOOKUP関数を使う方法
Microsoft 365やExcel 2021以降なら、XLOOKUP関数で作業列なしに検索できます。検索値と検索範囲を、それぞれ「&」でつなぐ書き方です。
=XLOOKUP(F2&G2,B2:B7&C2:C7,D2:D7,"該当なし")見つからなかったときは「該当なし」と表示されます。XLOOKUPが使えないバージョンでは使えないので注意してください。
INDEX関数とMATCH関数を使う方法
古いバージョンでも使いたい場合は、INDEXとMATCHの組み合わせがあります。条件の一致を掛け算で判定する書き方です。
=INDEX(D2:D7,MATCH(1,(B2:B7=F2)*(C2:C7=G2),0))Excel 2019以前では、式を入力したあと Ctrl+Shift+Enter で確定する必要があります。Microsoft 365やExcel 2021以降なら、通常の Enter で使えます。
| 方法 | 作業列 | 対応バージョン | 向いている場面 |
|---|---|---|---|
| 作業列＋VLOOKUP | 必要 | すべてのバージョン | 確実に動かしたい・表を共有する |
| XLOOKUP | 不要 | Microsoft 365・Excel 2021以降 | 新しい環境で式を短く書きたい |
| INDEX＋MATCH | 不要 | すべて（古い版は配列確定が必要） | 作業列を作れない・条件が3つ以上 |
まとめ
- VLOOKUPは1列しか検索できないため、複数条件は「作業列」で条件をつなげて検索する
- 作業列は範囲の一番左に作り、検索値も同じ順番で「&」でつなぐ
- 検索方法は完全一致のFALSEにする
- うまくいかないときは、作業列の位置・つなぎ順・余分なスペースを確認する
- 新しい環境ならXLOOKUP、作業列を作れないならINDEX+MATCHも使える
作業列は少し手間に見えますが、どのバージョンでも動いて、他の人に表を渡しても壊れにくい方法です。まずはこの方法から試してみてください。
コメント