VLOOKUPで複数条件を検索する方法|作業列を使った手順と、XLOOKUP・INDEX+MATCHの使い分け

教員向け 同じ氏名を組で区別するVLOOKUP複数条件 検索・参照関数

学年全体の表に、同じ氏名の子が、別の組にいる。氏名だけで探すと、違う子の点数が出てしまう。教員の仕事では、複数の項目で、1人を特定したい場面があります。

VLOOKUP関数は、1つの列でしか探せません。そこで、組と氏名を結合した列を作って、その列で探します。この記事では、教員の仕事でよく出る例を使って、方法を説明します。

教員の仕事でVLOOKUPの複数条件を使う場面|同じ氏名の子を組で区別する

表の上のアルファベット(A、B、C…)が列、左の数字(1、2、3…)が行です。「A2」は、A列の2行目のセルのことです。

ABCD
1検索用組氏名算数
21組青木1組青木72
32組青木2組青木88

検索用の列(A列)を、探す範囲の左端に置きます。A2に、組と氏名を結合する式を入れて、下にコピーします。

=B2&C2

別の場所で「2組青木」の算数の点数を探すときは、検索用の列を使います。範囲はA列からD列まで、点数はその4列目です。

=VLOOKUP("2組青木",A2:D31,4,FALSE)

上の表では、88が出ます。

ここからは、複数条件で検索する方法を順に見ていきます。

VLOOKUPで複数条件を検索する考え方

VLOOKUPは、検索値を範囲の「一番左の列」から探します。つまり、探せるのは1列だけです。そこで、複数の列の内容を「&」でつないだ列を左端に作り、検索する側も同じようにつなげて探します。

たとえば、支店が「大阪」で商品が「みかん」の単価を探したいなら、「大阪みかん」という1つの文字にして探します。

  • 作業列:B列(支店)とC列(商品)をつないだ列を、表の一番左に作る
  • 検索値:探したい支店と商品を「&」でつなぐ
  • 範囲:作業列を一番左に含めた表全体を指定する

手順1:作業列を作る

表の左端に列を挿入し、支店と商品名をつなぐ式を入れます。ここでは、A列を作業列にします。

作業列A2に式=B2&C2を入れて、支店と商品名をつなぐ手順を示した図
  1. 表の一番左の列(A列)を右クリックして「挿入」を選び、空の列を作る
  2. A2に「=B2&C2」と入力して Enter を押す
  3. A2を選び、右下のハンドルをA7までドラッグしてコピーする

手順2:VLOOKUPで検索する

作業列ができたら、検索用のセルを用意します。ここでは、F2に支店名、G2に商品名を入力し、H2に単価を表示します。

H2に式=VLOOKUP(F2&G2,A2:D7,4,FALSE)を入れて、複数条件で単価を検索する図
  1. H2を選び「=VLOOKUP(」と入力する
  2. 検索値として「F2&G2」を入力し、カンマを入れる
  3. 範囲として作業列を含む「A2:D7」を選び、カンマを入れる
  4. 列番号「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つ以上

複数条件で検索するときの注意

  • 結合した文字の、スペースや全角・半角を、そろえておきます。
  • 同姓同名で、組も同じ場合は、出席番号など、別の項目も足します。

あわせて読みたい

コメント

タイトルとURLをコピーしました