VLOOKUP関数の後継として XLOOKUP関数が登場して 3年ほどになります。
周りを見るとそろそろ Excel2021など新しいバージョンが使える環境が整いつつあるので、改めて XLOOKUP関数の話題も取り上げていこうと思います。
XLOOKUP関数そのものについては「Excel XLOOKUP関数(1)」記事、「Excel XLOOKUP関数(2)」記事でご紹介しています。
今回は、XLOOKUP関数で、複数の条件で検索する方法をご紹介します。
(1) AND条件
下図のようにクラス、No.、氏名で構成される表データについて、クラスと No.から氏名を検索してみます。
セルE2に検索するクラスを、セルF2に検索する No.を入力し、検索結果を表示するセルG2に次の数式を入力します：
=XLOOKUP(E2&F2,A2:A61&B2:B61,C2:C61)
XLOOKUP関数の書式については「Excel XLOOKUP関数(1)」記事をご参照ください。
第1引数「検索値」は通常 1つの値またはセル参照としますが、ここでは 2つのセルE2とF2の値を使いたいので、それらを結合して「E2&F2」とします。
第2引数「検索範囲」も A列のデータと B列のデータを「&」で結合し「A2:A61&B2:B61」とします。
第3引数は結果を抽出する範囲を指定しますので「C2:C61」とします。
上の表ではこれでよろしいのですが、1つ注意があります。
第1引数で「E2&F2」というように 2つのセルの値を結合しているので、セルE2が「1」、セルF2が「5」であるときは、検索値として「15」という文字列で検索することになります。
もし、このように結合した値と同じ値が他にも出現するような表データでは単に「&」で結合するのではなく、例えば次式のように何かしらの文字を挟んで結合するとよいでしょう。
（次式では「-」(ハイフン)を間にはさみました。）
=XLOOKUP(E2&"-"&F2,A2:A61&"-"&B2:B61,C2:C61)
動的配列（スピル）としての動作が可能です。
E列「クラス」と F列「No.」に複数の検索値を入力しておき、これらを一度に検索する方法です。
セルG2に次式のように入力します：
=XLOOKUP(E2:E4&F2:F4,A2:A61&B2:B61,C2:C61)
これで、下図のように検索できました。
この例では、異なるクラスで No.が「5」の人を検索したので、第1引数は「E2:E4&F2」としても同じ動作になります。
なお、以上の例は 2つの検索値を結合して検索するものでしたが、3以上になってもそれらを結合して検索すればよろしいです。
(2) クロス抽出する
XLOOKUP関数を入れ子（ネスト）にすることでクロス抽出することができます。
同じ内容のデータですが、今度は下図のように表の構成を変えました。
つまり、A列「No.」に対し B列「クラス1」、C列「クラス2」、D列「クラス3」としてその表内に氏名が入力されています。
この表データについて、セルF2とセルG2で指定された列と行が交差するセルの値を読み出します。
セルH2には次式を入力します：
=XLOOKUP("クラス"&F2,B1:D1,XLOOKUP(G2,A2:A21,B2:D21))
まず、内側の XLOOKUP関数について見てみます。
「XLOOKUP(G2,A2:A21,B2:D21)」は、セルG2で指定された値「5」を「No.」列の中で検索し、合致する行の氏名を索出しますので、この例では「秋山 瞬」「宮里 亜希」「友坂 さんま」からなる配列を返します。
そして、外側の XLOOKUP関数について見てみます。
セルF2は数値で指定しており、これを使ってセルB1～D1の項目行のいずれかを特定したいので、第1引数「検索値」には「"クラス"&F2」とします。
この検索値がセルB1～D1の項目行の中で検索するので、第2引数「検索範囲」は「B1:D1」とします。
第3引数「戻り範囲」は、先ほどの内側の XLOOKUP関数の結果を使います。
XLOOKUP関数の利用例ですが、INDEX関数を使ったデータ検索でもよいでしょうね。