MATCH関数、ADDRESS関数、INDIRECT関数を使って検索する

MATCH関数、ADDRESS関数、INDIRECT関数を使ってVLOOKUP関数のように一覧表のなかから目的の検索値を表示させることができます。

実務では、VLOOKUP関数を使えば良いので、この方法はあまり使う機会はないかと思いますが、記事にしてみました。

（使用Excel；Excel2010、Excel2013）

MATCH関数によって行数を検索

MATCH関数は次のように使います。

「＝MATCH（検索値、検索範囲、照合の種類）」

MATCH関数の引数の設定

□　検索範囲

行数を知りたい場合は、検索範囲を1行目から指定しておいた方が良いと思います。

□　照合の種類

次の3つのうちいずれかを指定します。私が経理資料を作成する場合は「０」を指定しています。

[aside type=”boader”]

「１」・・・以下

「０」・・・完全一致

「－1」・・・以上

（省略することもできます。）

[/aside]

MATCH関数の使用例

次のような表を用意しました。

請求書IDの行数を検索するためにMATCH関数を次のように使います。

F3セルには、次のような式が入力されています。

「=MATCH(E3,A:A,0)」

E3セルに入力されている請求書IDと同じ請求書IDのセルを先ほどの表から検索し、その行数を表示します。

上記の例の場合、「S117」は8行目にありますので、F3セルには、「8」が表示されます。

ADDRESS関数によるセル位置の表示

次にMATCH関数で検索した行数とADDRESS関数を使い、検索したいセルの位置を表示します。

G3セルには、次のような式が入力されています。

「=ADDRESS(E3,2,1,TRUE,”test1″)」

ADDRESS関数の引数の設定

□　行番号、列番号

行番号には、MATCH関数で検索した数値が入力されているE3セルを設定し、行番号には「2」を設定しました。

請求書の一覧表の請求額が記載されている列がB列（左から2番目なので、「2」を設定しました。

□　参照の種類

列番号の後には「参照の種類」を設定します。参照の種類には、1～4に数値を設定します（省略も可）。

違いは次のとおりです。

[aside type=”boader”]

「1」または省略　絶対参照・・・表示例『＄B$8』

「2」　行が絶対参照、列が相対参照・・・表示例『B$8』

「3」　行が相対参照、列が絶対参照・・・表示例『$B8』

「4」　相対参照・・・表示例『B8』

[/aside]

□　参照形式

TRUE（または「1」。省略も可）かFALSE（または「0」）を設定します。TRUE（または「1」。省略も可）の場合は、「A1形式」アルファベットと数値でセル位置を表示します）、FALSE（または「0」）の場合は「R1C1」で表示されます。

上記の例の場合、TRUE（または「1」。省略も可）を設定すると「$B$8」、FALSE（または「0」）を設定すると「R8C2」と表示されます。

□　シート名

「””」で囲ったシート名称を記載しますが、同シートのセルを参照する場合は、省略しても問題ありません。今回は、シート名を「test」にしてありますので、「”test”」と設定しました。

上の画像の場合は、G3セルに次のように表示されます。

「test!$B$8」

G3セルには、請求書IDが「S117」の請求額のセル位置「B8」（シート名と絶対参照の設定がしてあるため、正確には「test!$B$8」）が表示されています。

INDIRECT関数による検索値の表示

ここでH3セルに「=G3」と入力した場合は、H3にもG3と同じ「test!$B$8」という文字列が表示されてしまいますが、INDIRECT関数を使うと、G3に表示されているセル位置（つまり「B8」）に入力されている数値等（今回の例では請求書ID「S117」の請求額）が表示されるようになります。

H3セルには、次のような式が入力されています。

「=INDIRECT(G3,TRUE)」

INDIRECT関数の引数の設定

□　参照形式

参照形式は、ADDRESS関数で「A1形式」か「R1C1」形式を選択しているので、同じ形式を指定する必要があります。

例では、H3セルに請求書ID「S117」の請求額「325,000」円が表示されます。（下の画像の赤い枠囲み部分）

VLOOKUP関数を使うとH5セルに入力されているような数式になります。（上の画像の緑色の枠囲み部分）

まとめ

MATCH関数、ADDRESS関数、INDIRECT関数をまとめると次のような式になります。（下の画像のH7セル。ピンク色の枠囲み部分）

■□◆◇　編集後記　◇◆□■
風が強く、今朝はそのせいか、とても寒かったです。暖かいコーヒーがともておいしいです。