残業を減らす！Officeテクニック

XLOOKUP関数で複数条件の「いずれか」に合致する値を取り出せるようにする方法とは？

2025年2月3日 06:55

複数のコードから「いずれか」に一致する商品名は？

　表から特定のデータを探して対応する値を取り出す時の関数と言えば、XLOOKUP / VLOOKUP関数ですよね。しかし、これらの関数は万能ではなく、例えば条件に一致する値が複数ある場合、それらをすべて取得することはできません。FILTER関数などを活用するテクニックが便利です。

　反対に結果が複数ではなく、「AまたはB」といった複数の条件に一致する最初の値を取り出したいこともあるはずです。

　以下は人気順に並べた商品の一覧です。各カテゴリーにおける一番人気の商品は、XLOOKUP / VLOOKUP関数で取り出せますが、カテゴリーコードが「BLND」または「NATP」の中から一番人気を探すとなると困りますよね。

カテゴリーごとの一番人気は、XLOOKUP / VLOOKUP関数で取り出せるが、「BLND」または「NATP」の中から一番人気を探したい場合は？

　フィルターで絞り込んで並べ替える手もありますが、目視での確認では効率も悪く、見間違える可能性もありますよね。今回は、XLOOKUP関数の引数［一致モード］を活用して、複数の条件を探す時のひと工夫を紹介したいと思います。ただし、商品コードなどの半角英数字を条件とする場合限定です。

引数［一致モード］の正規表現を利用する

　XLOOKUP関数の引数［一致モード］を確認しておきましょう。普段は省略して「0：完全一致」で検索することが多いですが、「3」と指定することで、引数［検索値］に正規表現を利用できるようになります。

XLOOKUP関数の引数［一致モード］に「3」と指定すると、引数［検索値］に正規表現を利用できるようになる

　例えば「BLND」または「NATP」という条件を正規表現で記述すると「BLND|NATP」となります。指定する条件を「|」で区切ります。先ほどの数式を修正する場合は検索値の部分を「"」で囲みます。

セルE5に「=XLOOKUP("BLND|NATP",$C$2:$C$32,$B$2:$B$32,,3)」と入力した。全体で3番人気の「NATP」に対応する「自然の恵みナチュラル」を取り出せている

　しかし、数式中に条件式を直接入力するのはスマートではありませんね。TEXTJOIN関数を使って、正規表現の式を作成してみましょう。TEXTJOIN関数の構文は以下の通りです。

TEXTJOIN関数の構文。複数の［文字列］の間に［区切り記号］を挿入して連結する。［空の文字列を無視］は、TRUE（省略可）で無視、FALSEで空白も含めて連結する

　指定する条件が入力されているセル範囲を「|」で区切って連結するので、「=TEXTJOIN("|",TRUE,E2:E3)」のようになります。「TRUE」は省略しても構いませんが、「,」は必須です。

セルF2に「=TEXTJOIN("|",TRUE,E2:E3)」と入力した

結果はセルE2とE3の内容を「|」で挟んだ「BLND|NATP」となる

　あとはTEXTJOIN関数の結果をXLOOKUP関数で利用するだけです。以下は、XLOOKUP関数の数式にTEXTJOIN関数の数式を組み込んでいますが、TEXTJOIN関数が入力されたセルを参照しても構いません。

セルE5の数式を「=XLOOKUP(TEXTJOIN("|",TRUE,E2:E3),$C$2:$C$32,$B$2:$B$32,,3)」と修正した。結果は先ほどと同じく、全体で3番人気の「NATP」に対応する「自然の恵みナチュラル」が取り出される

セルE3を「DCFL」に書き換えた。全体で5番人気の「BLND」に対応する「星空のブレンド」が取り出された

　なお、「^(BLND|NATP)$」のような正規表現を使うと、完全一致となります。例えば、枝番を含むコードを除いて検索する時に活用してください。TEXTJOIN関数では「"^(" &TEXTJOIN("|",TRUE,E2:E3) & ")$"」と記述できます。

Amazonで購入