Arte Sheets学ぶ・試す・理解する

検索と参照

VLOOKUP関数の完全一致(FALSE)で商品コードから単価を安全に引く

VLOOKUP関数の第4引数にFALSEを指定して近似一致の事故を防ぎ、未登録コードを分かりやすく表示する方法です。

行番号ではなく商品コードを手がかりにする

ある修理店で交換部品の価格表を管理しているとします。顧客から部品コード P-240 の問い合わせがありました。件数が少なければ目視でも探せますが、見積書や集計表で何十件ものコードを扱う場合は検索関数が不可欠です。まず A1:C4 に次の表を用意します。

コード (A)部品名 (B)価格 (C)
P-110ケーブル12
P-240アダプター28
P-390スイッチ45

完全一致を指定して価格を取得する

第1引数は検索したい商品コードです。A2:C4 は検索対象となる表の範囲です。数値の 3 は、その範囲内の左から3列目(価格)を返すことを意味します。最後の FALSE は完全一致での検索を指定します。結果として 28 が返ります。

=VLOOKUP("P-240",A2:C4,3,FALSE)

第4引数を省略してはいけない理由

商品コードは特定の1品目を指し示すものであり、近い番号の別商品で代用することはできません。Excelで VLOOKUP の最後の引数を省略すると「近似一致」が選ばれてしまいます。近似一致は昇順に並んだ料金テーブルの段階判定などには便利ですが、商品コードの価格表には不適切です。コードの一致が必須の場面では、必ず明示的に FALSE を指定してください。

また、列番号はシート全体ではなく「選択した範囲の左端から何列目か」で数えます。もし表が D列から F列に配置されているなら、3列目は C列ではなく F列になります。正しい行は見つかっているのに別の項目が返ってしまう場合は、範囲指定を見直しましょう。

未登録コードと「価格0円」を明確に区別する

一覧に存在しないコード P-999 を検索すると、完全一致では #N/A エラーが返ります。見つからない結果を安易に 0 に置き換えないでください。無償パーツ(価格0)だと誤解される恐れがあります。IFNA 関数を使えば、該当なしの場合だけ "Check code"(コード確認)のような分かりやすいラベルを表示し、他の種類の数式エラーは隠さずに残せます。

=IFNA(VLOOKUP("P-999",A2:C4,3,FALSE),"Check code")

検索値を入力セルに切り出す

検索したいコードを E2 に入力します。数式の検索値として E2 を参照し、数式を下の行へコピーする場合に備えて元の表の範囲を $A$2:$C$4 のように固定します。E2 を P-240 から P-390 に書き換えると、結果が 28 から 45 に変わることを確認してください。

=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

結果を信頼する前に重複コードがないか確認する

完全一致指定は「コードが重複していないこと」までは保証しません。同じコードが表の中に2回登場している場合、VLOOKUP は上から最初に一致した行を返します。P-240 に2種類の価格が存在するのは元データの品質問題であり、検索関数だけでは解決できません。

さらに VLOOKUP は指定範囲の左端列を検索し、それより右側の列しか返せません。右側の列を検索して左側の列を取り出したい場合は、マイナスの列番号を指定しようとするのではなく、INDEX と MATCH の組み合わせなど別の検索パターンを使ってください。

サンプルを試す

初期値のまま計算するか、値を変更して動作を確認できます。