ここでは、クロス表の行見出しと列見出しを検索してデータを取り出す 2つの方法を紹介します。
- INDEX関数＋MATCH関数を使う方法
- XLOOKUP関数を使う方法（Excel 2021以降またはMicrosoft 365）
やりたいこと
ここでは、図1 の価格表から、セルB2～B3で指定した商品名「ホットティー」とサイズ「M」の価格を取り出します。
図1 商品名とサイズから価格を求めたい。
方法1…INDEX関数＋MATCH関数を使う方法
MATCH関数は、［検査値］が［検査範囲］の何番目にあるかを求める関数です。まず、空いているセルC2に MATCH関数を入力して、「ホットティー」が商品名のセルE3～E6 の何番目にあるかを調べます。［検査値］に「ホットティー」が入力されたセルB2、［検査範囲］に商品名のセルE3～E6、［照合の型］に完全一致検索を行うための「0」を指定します。「ホットティー」は 3番目の位置にあるので、結果は「3」になります。
指定した範囲の中から検索値の位置を求める
=MATCH(検査値, 検査範囲, ［照合の型］)
セルC2 | =MATCH(B2,E3:E6,0)
図2 セルC2 に MATCH関数を入力して「ホットティー」の位置を求める。結果は「3」。
同様に、セルC3に MATCH関数を入力して、「M」がサイズ欄のセルF2～H2 の何番目にあるかを調べます。結果は「2」になります。
セルC3 | =MATCH(B3,F2:H2,0)
図3 セルC3 に MATCH関数を入力して「M」の位置を求める。結果は「2」。
ここまで来たらあと一歩。最後に INDEX関数を使います。
INDEX関数は、［参照］のセル範囲の中から［行番号］行［列番号］列の位置にあるセルを返す関数です。［参照］に価格欄のセルF3～H6 を指定し、［行番号］と［列番号］にMATCH関数を入力したセルC2とセルC3 を指定すると、「=INDEX(F3:H6,3,2)」が実行されて価格欄の「3」行「2」列目の位置にある「200」が求められます。
指定した行と列の位置にあるセルを取り出す
=INDEX(参照, 行番号, ［列番号］, ［領域番号］)
セルB4 | =INDEX(F3:H6,C2,C3)
図4 セルB4に INDEX関数を入力して価格欄の「3」行「2」列目のデータを取り出し、［通貨表示形式］を設定した。
メモ
方法2…XLOOKUP関数を使う方法
Excel 2021と Microsoft 365では、XLOOKUP関数を使う方法もあります。2つのXLOOKUP関数を入れ子にして、行見出しと列見出しをそれぞれ検索する方法です。XLOOKUP関数の基本的な使い方とスピルの利用については、下記を参照してください。
考え方を理解するために、列見出しを検索する数式の動作を確認しておきます。空いているセルJ3に、XLOOKUP関数を入力します。［検索値］に「M」が入力されたセルB3、［検索範囲］にサイズ欄のセルF2～H2、［戻り値範囲］に価格欄のセルF3～H6 を指定します。するとスピル機能が働き、図5 のようにセルJ3～J6 の範囲に「M」サイズの価格が一覧表示されます。
つまり、「=XLOOKUP(B3,F2:H2,F3:H6)」という数式で、「M」サイズの価格を1列分丸ごと取り出せるというわけです。
さまざまな条件を指定してデータを取り出す
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, ［見つからない場合］,［一致モード］, ［検索モード］)
セルJ3 | =XLOOKUP(B3,F2:H2,F3:H6)
図5 動作確認のためにセルJ3にXLOOKUP関数を入力。「M」サイズの価格の列を丸ごと取り出せた。
動作確認できたら、ここからが本番です。セルB3に XLOOKUP関数を入力し、［検索値］に「ホットティー」が入力されたセルB2、［検索範囲］に商品名欄のセルE3～E6、［戻り値範囲］に図5 のXLOOKUP関数を指定します。すなわち［戻り値範囲］には「M」サイズの価格のセルG3～G6を指定したのと同じことになります。「ホットティー」は［検索範囲］の 3番目で見つかるので、［戻り値範囲］の 3番目の位置にある「200」が取り出されます。
セルB4 | =XLOOKUP(B2,E3:E6,XLOOKUP(B3,F2:H2,F3:H6))
図6 セルB4に XLOOKUP関数を入れ子にして入力する。ホットティーのMサイズの価格が求められる。