Excelで、2つの検索値から表の行と列を特定し、行と列が交差するセルのデータを取得する、いわゆるクロス抽出の方法です。
- XLOOKUP関数を使ってクロス表からデータを抽出することができます。1種類の関数で済み、関数のネストを用いない抽出も可能です。
- オーソドックスな方法としてINDEX関数とXMATCH関数を組み合わせる方法もあります。
手順
クロス抽出とは
そもそもの検索の対象と抽出の考え方についてです。
次の画像のB2:F7の範囲にある「売上集計」と名付けられた表がクロス表です。B列（B3:B7）には店舗の名称が並んでおり、2行目（C2:F2）には4月~7月の月名が並んでいます。
店舗と月を特定してその行と列が交差するセルを参照することで、その店舗のその月の売上高がわかります。例えば「池袋店」の「6月」の売り上げは「9031」であることがわかります。これがクロス表からのデータ検索の考え方です。
そこで以下では、H3セルに入力された店舗と、I3セルに入力された月に基づき、J3セルに式を入力して売上高を検索する例を試してみます。
XLOOKUP関数を使う方法（その1）
XLOOKUP関数を用いる、比較的新しい方法です。
関数のネスト（入れ子）は必要ですがXLOOKUP関数だけで済みます。
画像ではJ3セルに次のように入力しています。
=XLOOKUP(I3,C2:F2,XLOOKUP(H3,B3:B7,C3:F7))
まず内側の「XLOOKUP(H3,B3:B7,C3:F7)」により、「C3:F7の中で『B3:B7内での池袋店の位置』に対応する行（つまり2行目）」を得ることができます。
これによりC3:C7の範囲のうち2行目の内容、つまり「9440」「10423」「9031」「12510」という4つの数字の配列が得られます。
さらにこの結果に対してXLOOKUP関数を適用することにより、「『9440』『10423』『9031』『12510』の中で『C2:F2内での6月の位置』に対応する列（つまり3列目）」を得ることができます。
4つの数字のうち3列目（3番目）の値は「9031」ですので、最終的にこの値が得られることとなります。
確定すると「9031」が得られました。
XLOOKUP関数を使う方法（その2）
同じくXLOOKUP関数を使う方法です。
少し変わった式になり、関数の動作解釈も上記の方法とは異なるものになりますが、関数のネスト（入れ子）を要しないのがポイントです。
画像ではJ3セルに次のような式を入力しています。
=XLOOKUP(H3,B3:B7,C3:F7) XLOOKUP(I3,C2:F2,C3:F7)
珍しい感じの式ですが、XLOOKUP関数の戻り値がセル範囲参照（$C$3のような）と同様に扱えることと、半角スペースが「2つの範囲の共通部分を抽出する演算子」として使えることを利用しています。
最初の「XLOOKUP(H3,B3:B7,C3:F7)」により、「C3:F7の中で『B3:B7内での池袋店の位置』に対応する行（=2行目）」への参照を得ることができます。これによりC3:C7の範囲のうち2行目、つまりC4:F4の範囲が得られます。
次の「XLOOKUP(I3,C2:F2,C3:F7)」により、「C3:F7の中で『C2:F2内での6月の位置』に対応する列（=3列目）」への参照を得ることができます。これによりC3:C7の範囲のうち3列目、つまりE3:E7の範囲が得られます。
これらの2つの式を半角スペース（共通部分演算子）でつなぐことにより、「C4:F4」と「E3:E7」の共通部分である「E4」が得られます。
確定すると、E4セルに記録されている数値「9031」が得られました。
なお、XLOOKUP関数と参照演算子（コロンやスペース）の組み合わせや、それを可能にするXLOOKUP関数の動作について次の記事でも紹介しています。
INDEX関数とXMATCH関数を使う方法
従来多用されてきた方法です。
本来はXMATCH関数でなくMATCH関数でしたが、比較的新しい関数であるXMATCH関数を使えば（完全一致検索をするための）引数を省略できるのでXMATCHを用いることとします。
画像ではJ3セルに次のような式を入力しています。
J3セル
=INDEX(C3:F7,XMATCH(H3,B3:B7),XMATCH(I3,C2:F2))
INDEX関数は「=INDEX(表の範囲,表内行番号,表内列番号)」とすることで、表内のその位置にある値を抽出する関数です。まさにこの手の検索のためにあるような関数です。
ただし、この例では行番号や列番号を直接指定するのではなく、店名と月から番号を特定する必要があります。そこで配列内の位置を返す関数、XMATCH関数を用います。「XMATCH(H3,B3:B7)」は「B3:B7の範囲で『池袋店』が何番目にあるか」を返します。また、「XMATCH(I3,C2:F2)」は「C2:F2の範囲で『6月』が何番目にあるか」を返します。
2つのXMATCH関数はそれぞれ「2」と「3」という結果を返しますので、上記の式は次の式と同じ意味となります。ここまで来れば結果が見えてくると思います。
=INDEX(C3:F7,2,3)
そして結果は次の画像のようになります。
「池袋店」の行にあり、かつ「6月」の列にある数値、つまり「9031」が取得できているのがわかります。
逆の抽出について
表内の検索値から見出し（行・列）を抽出する方法について、次の記事で紹介しています。