いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Excel)XLOOKUP関数で複数条件の検索を行う

 ExcelのXLOOKUP関数を使って、複数の条件による検索をする方法についてです。
 ただしここで「複数の条件」とは、複数列にわたる条件のことです。1つの列に対して2つ以上の検索値を設定して検索する、というものではありません。

  • XLOOKUP関数の第2引数(検索範囲)で「B3:B8&C3:C8」といったように複数の列を&でつないだものを指定すれば、複数の列に条件を課して検索したのと同じ結果を得ることができます。

手順

f:id:accs2014:20200804233012p:plain:right:w600

 画像ではB列に建物の名称が、C列に部屋番号が、そしてD列には部屋の入居者の氏名が記録されています。
 値をみるとわかるように、建物名だけでも部屋番号だけでも入居者を特定できません。両方が揃ってはじめて入居者を特定できるようになっています。

 さて、F列からH列は検索欄になっています。F3セルには建物名が入力され、G3セルには部屋番号が入力されています。これらの値をもとにXLOOKUP関数を使って入居者の氏名を抽出できるでしょうか。
 VLOOKUP関数を使う場合は元の表に「建物名+部屋番号」という列を設けるのが常套手段ですが……

 
 XLOOKUP関数の場合は式だけで解決できます。
 H3セルに次のように入力します。

f:id:accs2014:20200804233017p:plain:w750

 H3セル

=XLOOKUP(F3&G3,B3:B8&C3:C8,D3:D8)

 第1引数(検索値)として「F3&G3」と、建物名と部屋番号の2つの値を連結した値を指定します。第2引数(検索範囲)でも「B3:B8&C3:C8」と、2つの列を連結したものを指定します。これで建物名と部屋番号がF3,F4セルの値と(両方とも)一致する入居者を抽出できます。

 

f:id:accs2014:20200804233023p:plain:right:w600

 確定すると意図どおりに氏名を抽出できました。
 VLOOKUP関数の場合と違ってXLOOKUP関数では第2引数(検索範囲)が2列以上の表でなくても検索ができるため、このようなことが可能です。ちょっとした盲点かもしれません。

 注意点として、この方法(式)だと「建物名が『A』で部屋番号が『AA』の部屋」と、「建物名が『AA』で部屋番号が『A』の部屋」を区別できません(どちらも連結すると「AAA」になるためです)。意図しない限り起こらないような事態なのであまり気にする必要はありませんが、これを防ぐ場合には次のような式にするのが1つの方法です。

=XLOOKUP(F3&"-"&G3,B3:B8&"-"&C3:C8,D3:D8)

 ただしこれでも「-」「--」の組み合わせと「--」「-」の組み合わせを区別できないため完璧ではありません。

 また、式はやや複雑になりますが、複数の列にAND条件をつけて検索・抽出ができるFILTER関数に移行するのも有力な方法です。
 FILTER関数でAND検索を行う方法については次の記事で紹介ししています。

www.officeisyours.com