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

この記事には広告を含む場合があります。

記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

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

MATCH_11

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

(使用Excel;Excel2010、Excel2013)


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

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

「=MATCH(検索値、検索範囲、照合の種類)」

 

MATCH関数の引数の設定

□ 検索範囲

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

□ 照合の種類

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

[aside type=”boader”]

「1」・・・以下

「0」・・・完全一致

「-1」・・・以上

(省略することもできます。)

[/aside]

MATCH_15

 

MATCH関数の使用例

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

MATCH_21

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

MATCH_23

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

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

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

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

 

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

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

MATCH_24

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」

MATCH_25

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

 

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

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

MATCH_26

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

「=INDIRECT(G3,TRUE)」

INDIRECT関数の引数の設定

□ 参照形式

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

 

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

MATCH_27

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

まとめ

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

MATCH_28

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