XLOOKUP関数で複数条件（AND条件）を指定して検索する方法！

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

受注データから「商品コード」と「色」の2つの条件を指定して、対応する単価を自動で表示できるようにします。

今回のゴールは
・補助列に連結キー（=A2&B2）を作り、=XLOOKUP(G2&H2, $F$2:$F$11, $D$2:$D$11, “該当なし”) で検索する方法
です。
同じ商品コードが色違いで複数あっても、2つの条件を組み合わせれば1件に絞り込めます。

上の画像が完成形です。商品コードと色の2つを入力すると該当する単価が自動で表示され、見つからないときは「該当なし」と表示されます。

なお、match_modeを使ったワイルドカード（部分一致）や、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。

STEP1. 受注データを用意する

まずは検索のもとになる受注データを用意します。
1行目が見出しで、A列が商品コード、B列が色、C列がサイズ、D列が単価、E列が在庫です。
データは10件あります。
ここで注目してほしいのが、同じ商品コードが色違いで何度も出てくる点です。
たとえば PC-001 は「ブラック」「ホワイト」「ネイビー」「レッド」の4件あります。
このため、商品コードだけでは1件に特定できません。
そこで今回は「商品コード」と「色」の2つを組み合わせて検索していきます。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. 検索用の「連結キー」を補助列に作る

XLOOKUP関数は「1つの検索値」を「1つの検索範囲」から探す関数です。
そのため、2つの条件で検索するには、あらかじめ2つの条件を1つにまとめた列を用意します。
これを「連結キー」と呼びます。
まず F1セルに「連結キー」と入力します。
次に F2セルに =A2&B2 と入力します。
&（アンパサンド）は、文字列をつなげる連結演算子です。
すると F2セルは「PC-001ブラック」となります。
あとはフィルハンドルで F11セルまでコピーすれば、全行の連結キーが完成です。
この補助列があることで、複数条件の検索がぐっとラクになります。

連結キーの数式を入力する

実際に F2セルへ数式を入力している画面がこちらです。
数式バーには =A2&B2 と表示されています。
& は足し算ではなく「文字の連結」なので、数字と文字が混ざっていても問題ありません。

STEP3. XLOOKUP関数で複数条件を検索する

次に、検索エリアを作ります。
G1セルに「商品コード」、H1セルに「色」、I1セルに「単価」と入力します。
G2セルに PC-001、H2セルに レッド と入力します。
そして I2セルから I5セルまで、同じ数式を次のように入力します。

=XLOOKUP(G2&H2, $F$2:$F$11, $D$2:$D$11, "該当なし")

検索値のところで G2&H2 としているのがポイントです。
2つのセルを & で連結し、それを補助列の連結キーと照合しています。
実際の入力画面がこちらです。

検索結果を確認する

I2セルに 145,000 が表示されました。
PC-001 の「レッド」は受注データの11行目にある1件だけです。
同じ PC-001 でも色が違えば単価も変わるので、2つの条件で絞り込めたことがわかります。
カンタンですね。

「1つの条件だけ」のXLOOKUPと比べる

参考までに、商品コードだけで検索した結果も J列に並べてみました。
J2セルには =XLOOKUP(G2, $A$2:$A$11, $D$2:$D$11, “該当なし”) と入力しています。
すると J2セルは 128,000 になりました。
これは PC-001 の最初に見つかった行（ブラック）の単価です。
「レッド」を探しているのに、色を無視して上から最初の行を拾ってしまうためです。
たとえば PC-003 の「ホワイト」は正しくは 198,000 ですが、商品コードだけで引くと最初の PC-003（ブラック）の 156,000 になってしまいます。
2つの条件で検索すれば、このように意図した1件だけを正しく取得できます。

複数条件で検索する方法の比較

複数条件で値を引く方法は、ほかにもいくつかあります。
特徴を比べてみましょう。

STEP4. match_modeでワイルドカード（部分一致）にする

「PC-001 で始まる」のように、一部が一致すればよい場合は、第5引数の match_mode に 2 を指定します。
2 は「ワイルドカード文字を有効にする」という意味です。
表の下に検証エリアを作りました。

=XLOOKUP("PC-001*", $F$2:$F$11, $D$2:$D$11, "該当なし", 2)

検索値の末尾に *（アスタリスク）を付けると、「PC-001 で始まるキー」が対象になります。
上の行（match_mode なし）は、* がただの文字として扱われるため「該当なし」になります。
下の行（match_mode に 2）は、PC-001 で始まる最初の行を拾って 128,000 を返します。
部分一致にしたいときだけ 2 を使う、と覚えておきましょう。

STEP5. if_not_foundで「該当なし」を表示する

XLOOKUP関数の第4引数は、値が見つからなかったときに返す文字を指定できます。
これを if_not_found（見つからない場合）と呼びます。
今回は「該当なし」と指定しているので、#N/Aエラーではなく日本語で表示されます。
試しに G5セルに PC-999、H5セルに ブラック と入力してみましょう。
受注データに存在しない商品コードなので、I5セルは 該当なし になりました。
#N/Aよりも、何が起きているかがひと目でわかりますね。

VBAで複数条件の検索を自動化する

同じ検索を何度も行う場合は、VBAに任せるのがおすすめです。
VBAでは Application.Match を使って、連結キーの位置を調べることができます。

Sub MultiLookup()
    Dim i As Long
    Dim key As String
    Dim pos As Variant
    For i = 2 To 5
        key = Cells(i, 7).Value & Cells(i, 8).Value
        pos = Application.Match(key, Range("F2:F11"), 0)
        If IsError(pos) Then
            Cells(i, 9).Value = "該当なし"
        Else
            Cells(i, 9).Value = Cells(pos + 1, 4).Value
        End If
    Next i
End Sub

Cells(i, 7) は G列、Cells(i, 8) は H列、Cells(i, 9) は I列を表します。
Application.Match は見つかった位置を返すので、その分だけ下の行から単価を取り出しています。
Find メソッドを使う書き方もご紹介します。

Sub MultiLookupFind()
    Dim key As String
    Dim hit As Range
    key = Range("G2").Value & Range("H2").Value
    Set hit = Range("F2:F11").Find(What:=key, LookIn:=xlValues, LookAt:=xlWhole)
    If hit Is Nothing Then
        Range("I2").Value = "該当なし"
    Else
        Range("I2").Value = hit.Offset(0, -2).Value
    End If
End Sub

タカヒロ

VBAでも考え方は同じで、2つの値を & でつないだ文字列を探しているだけです。
Application.Match は見つからないとエラーを返すので、IsError で判定するのがポイントです。
Find メソッドは LookAt:=xlWhole を付けて、部分一致ではなく完全一致で探すようにしましょう。
むずかしく見えますが、やっていることは数式と同じなのでカンタンですね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったように動かないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使うXLOOKUP関数と、連結演算子、IFERROR関数の概要です。

XLOOKUP関数（エックスルックアップ）

=XLOOKUP(G2&H2, $F$2:$F$11, $D$2:$D$11, "該当なし")

連結演算子 &（アンパサンド）

=A2&B2

IFERROR関数（イフエラー）

完成

検索エリアに商品コードと色を入力すると、該当する単価が自動で表示されるようになりました。
PC-002 の「ホワイト」を入れると 89,000、PC-003 の「ホワイト」を入れると 198,000 と、条件に合った1件だけが返ります。
商品コードだけでは色違いで複数ヒットしてしまう場面でも、2つの条件で正しく特定できます。
思ったとおりに動きましたか？
毎回目で探していた作業がなくなり、検索ミスも減りますね。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

ポイントは、補助列に「連結キー」を作り、検索値側も & で連結することでした。
まずは今回のサンプル表で、ぜひ一度試してみてください。
同じ商品コードで色やサイズ違いがある台帳ほど、効果を実感できますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

ぜひ試してみてください。