マスタテーブル(セル範囲)からコードに相当する名称を見つけて表示する

一般的なコンピュータ処理では、売上明細など個々の明細データには「名称」の項目はなく、「コード」から該当のマスタを参照して「名称」を取り出します。
ここで説明するのが、そういったマスタから名称やそれ以外の補足情報を参照する方法です。
このサンプルは、社員番号から氏名を表示させています。

(画像をクリックすると、このサンプルがダウンロードできます)
「VLOOKUP関数」を使います。「氏名」セルの数式は、

 =VLOOKUP($B$2,マスタ!$A$1:$B$7,2,FALSE)

このようになります。

関数	概略説明
VLOOKUP関数	テーブルまたは範囲の内容を行ごとに検索し、見つかった行の指定列番号の値を返します。  検索はテーブルの左端列で行なわれます。引数は以下の通りです。  ① 検索値  ② テーブルとなるセル範囲  ③ 戻り値となる列番号(セル範囲の左端列を｢1｣とする相対番号)  ④ 検索方法(完全一致はFALSEとする)

※通常は、このように「縦方向」に向かって参照しますが、例えば「コード」が横に並んでいるような場合は、HLOOKUP関数を同じように使います。

マスタテーブル(セル範囲)からコードに相当する相対位置を見つける

「VLOOKUP関数」では、テーブル位置の「他の列」の内容を表示しますが、場合によってはその内容ではなく、「何番目」にあるかが取り出したい場合があります。 「コード」を見つけた同じ行から複数の列の値を参照したい場合や、「コード」の列が参照したい内容の列より右にある場合などが一般的なケースでしょう。
このような場合は、MATCH関数を使います。

(画像をクリックすると、このサンプルがダウンロードできます)
この例の程度であれば、同じように「VLOOKUP関数」を「氏名」「部署」「内線」それぞれに置けば良いのですが、一度の検索で「位置」を掴んでしまえば同じ配列位置からそれぞれの参照値を取り出せるということも多いと思います。
このような場合は、一旦欄外のセルに参照位置の「番号」を１回だけ「MATCH関数」で参照しておき、「氏名」「部署」「内線」の項目は、「INDEX関数」でその「番号」を使って直接マスタシートのテーブルを参照しています。 欄外のセル(F2)の数式は、

 =MATCH($B$2,マスタ!$A$2:$A$8,0)

このようになります。 一方、「氏名」「部署」「内線」セルの数式は、

[氏名(B4)]
 =INDEX(マスタ!$A$2:$D$8,$F$2,2)
[部署(B5)]
 =INDEX(マスタ!$A$2:$D$8,$F$2,3)
[内線(B6)]
 =INDEX(マスタ!$A$2:$D$8,$F$2,4)

このようになります。

関数	概略説明
MATCH関数	検索値で検索セル範囲を検索し、見つかった位置の検索セル範囲内での相対位置を返します。  引数は以下の通りです。  ① 検索値  ② 検索するセル範囲  ③ 照合の型(省略可：省略時は1)    ⇒1:検索値以下の最大値、0:検索値と一致する値、-1:検索値以上の最小値      ※0以外の場合は検索するセル範囲が値の昇順(1)、または降順(-1)に並んでいる必要があります。
INDEX関数	行番号、列番号により、テーブルにある値、またはセル範囲あるいはその値のセル参照を返します。  引数は以下の通りです。  ① 配列またはセル範囲  ② 行番号(相対値)  ③ 列番号(相対値)

XLOOKUP関数を使ってみましょう。

「XLOOKUP関数」は「Office 365(現在はMicrosoft 365)」と「Office 2021以降」のExcelにしか実装されていない関数です。
2019年頃から「スピル」が話題になっていますが、この関数でもこの「スピル」を利用します。

(画像をクリックすると、このサンプルがダウンロードできます)
計算式は、

 =XLOOKUP($B$2,マスタ!$A$2:$A$8,マスタ!$B$2:$D$8)

です。

この計算式はC2セルだけに投入しており、D2セル、E2セルは同じ計算式が数式バーに表示されますがグレー表示となっています。

さて、「スピル」であふれた分の結果はそのままだと上の画像のように右側セルに表示されます。
これを、前項の「MATCH関数」のサンプルのように縦に並べたい時はこのようにします。

計算式は、

 =TRANSPOSE(XLOOKUP($B$2,マスタ!$A$2:$A$8,マスタ!$B$2:$D$8))

です。

関数	概略説明
TRANSPOSE関数	引数で指定されたセル範囲の縦横変換した内容を、数式投入したセルを起点にスピル展開します。

つまり、「XLOOKUP関数」の処理結果に対して、「TRANSPOSE関数」で行列を入れ替えているだけですが、これにより「スピル」のあふれる方向が「縦方向」に替わります。

念のため、XLOOKUP関数に対応していないバージョンのExcelで開いてみました。
該当PCは単なるOffice2019(売り切り版)です。サブスクリプション対応ではありません。

このようになりました。
計算式は、

 ={_xlfn.XLOOKUP($B$2,マスタ!$A$1:$A$7,マスタ!$B$1:$D$7)}

です。
「スピル」なので配列数式に変換されていますが、それより、 「XLOOKUP関数」の前にある「_xlfn.」です。
この「_xlfn.」が付くのは、開いているバージョンで対応されていない関数が使われていることを意味しています。 再計算させてもエラーにはならずにセル表示値も前回保存時の値のままになります。



「_xlfn.」が表示されるのはこのワークブックを作成した側のExcelのバージョンが、 現在開いているExcelのバージョンより高く、さらに高いバージョン側でしか対応していない関数を使用していることを意味しています。

VLOOKUP関数の参照式は合っているはずなのに「＃N/A」になるのはなぜ？

参照シートの方が「数値」、マスタリストの方が「文字列」の場合

一般に「社員コード」「商品コード」というものは数字並びであっても加減乗除の計算をするための「数値」ではなく、桁ごとに意味があることもある数字並びの「文字列」であるはずです。 これを「数値」として扱ってしまうとゼロ始まりの場合が問題となってしまいます。Excelではセルの書式設定を「標準」のままにしておくと、数字並びの入力は基本的に「数値」で扱われます。
VLOOKUP関数の参照式でリストにヒットするはずの値があって参照範囲も正しいのに「＃N/A」になってしまうというような場合は、探索している「コード」の属性をチェックしてみて下さい。

(画像をクリックすると、このサンプルがダウンロードできます)
例えばこのサンプルでは、社員№は「002」と表示されていますが、これはセルの書式設定で前ゼロが付くようになっているからで、数式バーの表示は「2」なのです。

一方、「マスタ」シートの方の社員№がこのように文字列（数式バーにも「002」と表示される）の場合、数値の「2」と文字列の「002」では同じ値だと見なされず「＃N/A」となってしまうのです。

このような場合は、参照シートの方で「型変換」させます。

もちろん、参照シートの方とマスタリストの方で同じ形式にできれば問題ないのですが、作成者が違う別ブックであったりで変更できないこともあります。

そのような場合は、VLOOKUP関数の中で参照シート側の値を「型変換」させてしまいます。

=VLOOKUP(TEXT($B$2,"000"),マスタ!$A$2:$B$10,2,FALSE)

このように、TEXT関数を併用して数値の「2」を文字列の「002」に変換してからVLOOKUP関数に掛けるようにすればヒットするようになります。

逆のケースも同様に対処できます。

逆のケース（参照シートの方が「文字列」、マスタリストの方が「数値」の場合）はこのように対処します。

このケースでは参照シートの方の「文字列」を「数値」にするので、VALUE関数を使用して「型変換」します。

=VLOOKUP(VALUE($B$2),マスタ!$A$2:$B$10,2,FALSE)