Excel INDIRECT関数の参照文字列をXMATCH関数で取得する

INDIRECT関数は参照文字列のセルの値を返す関数です。

参照文字列を固定の文字列を参照するだけでは、あまり使い道がありません。
参照文字列を動的に変えることができれば、数式を変更せずに結果を変えることができます。

例えば、XMATCH関数で検索値に一致したセルの位置を取得し、その位置を参照文字列の一部に組み込めば、数式を変えずに様々でデータを抽出することができて便利です。

INDIRECT関数やXMATCH関数の使い方が分からない人はリンクを参照ください。

XMATCH関数の検索値に一致した行のデータを抽出する

下図はINDIRECT関数の第１引数にXMATCH関数を使用した簡単な例です。

INDIRECT関数を含んだ数式はD4のセルに入力しており、支部名と一致した売上が表示されるようになっています。

INDIRECT関数のセル番号の行番号をXMATCH関数に合致した位置としています。

XMATCH関数は第１引数の検索値は支部名が入力されている「E1」とし、第２引数の検索範囲は「A:A」としており、支部の列全体としています。

東京は検索範囲の2番目なので、XMATCH関数は2を返します。

「=INDIRECT(“B”&XMATCH(E1,A:A))」
→「=INDIRECT(“B2”)」
→「3,500,000」

支部名を名古屋や大阪に変更すると、数式は変えることなく各支部の売上データを抽出することができます。

気づいたかもしれませんが、VLOOKUP関数、XLOOKUP関数、INDEX関数などを使用したデータを抽出同様のことができてます。

行番号の検索結果からセル番地を指定するならXMATCH関数だけで問題ないが、列番号の検索結果は数値からアルファベットに変換する必要がある。この変換はADDRESS関数を使うとできる。

ADDRESS関数は、「ADDRESS（行番号, 列番号）」という引数を入力することで使用できる。
例えば「ADDRESS（3,2）」ならば「B3」を返す。

下図はADDRESS関数でINDIRECT関数の参照文字列を指定した例です。

ADDRESS関数の行番号および列番号はXMATCH関数で指定している。
行番号は支部（東京）と一致したセルの位置の「2」となる。
列番号は項目（利益）と一致したセルの位置の「3」となる。
よって、ADDRESS関数は「C2」という参照文字列を返す。

「=INDIRECT(ADDRESS(XMATCH(F2,A:A),XMATCH(F3,1:1)))」
→「=INDIRECT(ADDRESS(2,3)」
→「=INDIRECT(“C2”)」
→「350,000」

支部や項目を変更すると対応したデータを抽出することができる。
例えば、支部を名古屋、項目を売上とすると、名古屋の売上である「4,500,000」が抽出される。

INDEX関数とXMATCH関数を組合わせても同じデータの抽出をすることができます。