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

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

(画像をクリックすると、このサンプルがダウンロードできます)
VLOOKUP関数を使います。VLOOKUP関数は、カッコ内がカンマで４項目に区切られています。
※R1C1参照形式の場合は

 =VLOOKUP(R2C2,マスタ!R1C1:R7C2,2,FALSE)

となります。
カッコ内の「引数」は以下の通りです。

• 最初の引数は、氏名を参照する元の「コード」のセル(B2)を指定します。この例では１件だけなので絶対参照にしています。
• ２番目の引数は、参照先の社員番号・氏名のテーブルを指定します。この例では、隣の「マスタ」シートのA列が「コード」、B列が「氏名」なのでその全体の範囲を指定します。
• ３番目の引数は、２番目で指定した範囲の「何列目」を表示するかを指定します。これを利用すれば、「氏名」の他、「部署」「電話番号」などを並べて表示させることも可能になります。
• ４番目の引数は、指定した「コード」がテーブルにない場合の処置の指定です。
  「FALSE」の場合は、一致したものがないと「#N/A」になります。「TRUE」の場合は、一致したものがないと「内輪の近似値」が選択対象となります。
  ここでは当然、「FALSE」を選択します。

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

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

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

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

 =MATCH(R2C2,マスタ!R1C1:R7C1,FALSE)

となります。

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

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

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

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

です。

この計算式はC2セルだけに投入しており、D2セル、E2セルは「スピル」の機能で反映されたものがそのまま表示されます。
D2セル、E2セルでも同じ計算式が数式バーに表示されますがグレー表示となっています。

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

計算式は、

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

です。
つまり、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)