ExcelのVLOOKUP関数では、検索先のセルが空白であっても、結果として「0」が表示されることがあります。
本来は空欄のままにしたいのに、見積書、在庫表、顧客管理表、売上集計表などで不要な0が並ぶと、表が読みにくくなるものです。
このような場合は、VLOOKUP関数にIF関数を組み合わせることで、検索値や取得結果が空白のときだけ空白を返せます。
この記事では、VLOOKUPで空白なら空白を表示する数式、0が出る理由、IFERRORとの使い分け、実務で崩れにくい設定まで順番に解説します。
この記事で扱う主な数式
=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))
=IF(VLOOKUP(A2,$F$2:$H$10,3,FALSE)=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))
=IFERROR(IF(VLOOKUP(A2,$F$2:$H$10,3,FALSE)=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE)),””)
サンプルデータは、1行目を見出しとして作成します。
それでは、VLOOKUPの結果を空白にする方法から確認していきましょう。
VLOOKUPで空白なら空白を返すIF関数
それではまず、検索値が空白のときにVLOOKUPの計算を行わず、結果も空白にする基本の数式について解説していきます。
| 注文番号 | 商品名 | 単価 |
|---|---|---|
| A001 | ノート | 120 |
| A002 | ペン | 80 |
| 0と表示されやすい行 |
検索するセルが空欄なら、VLOOKUPを実行しない形にすると、不要な表示を抑えられます。
検索値をA2、検索表をF2からH10、返す列を3列目とした場合の基本形です。
=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))
検索値が空欄のときに空白を返す数式
検索値を入力するセルそのものが空欄なら、最も使いやすいのはIF関数で先に判定する方法です。
数式を入力するセルがC2で、検索値がA2にある場合、次の数式を入力します。
=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))
IF関数の最初のA2=””は、A2が空白かどうかを判定する条件です。
A2が空白なら、2つ目の””が実行され、数式セルにも空白が表示されます。
A2に注文番号が入力されている場合だけ、3つ目のVLOOKUP関数が実行される仕組みです。
空欄の検索キーに対してVLOOKUPを動かさないため、入力前の行がすっきり見えます。
入力用の表を先に大量に作成するケースでは、あらかじめこの式を最終行までコピーしておくと便利です。
利用者が注文番号を入力した瞬間に商品名や単価が表示され、未入力行には何も表示されません。
【操作のポイント】検索値セルの参照は相対参照のままにし、検索範囲だけを$記号で絶対参照に固定します。
完全一致を指定するFALSEの役割
VLOOKUP関数の最後に指定するFALSEは、検索値と完全に一致するデータだけを返す指定です。
注文番号、社員番号、商品コードのように、登録済みの値を正確に照合したい場面ではFALSEを使用します。
=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))
この式では、A2にA001と入力すれば、検索表からA001の行を探し、3列目にある単価を返します。
一方でA2が空白なら、VLOOKUP部分の計算前にIF関数が空白を返します。
FALSEを省略すると近似一致として処理される可能性があるため、コード検索では明示するのが安全です。
検索表の先頭列は、必ず検索する注文番号や商品コードの列にします。
VLOOKUPは指定範囲の一番左の列からしか検索できないという特徴があるためです。
検索範囲がF列からH列の場合、列番号はF列が1、G列が2、H列が3です。
シート上の列番号ではなく、指定した検索範囲の左端から数える点に注意しましょう。
【操作のポイント】商品コードの前後に空白があると一致しないため、入力規則やTRIM関数で表記を整えると安定します。
数式をオートフィルで下の行へコピーする操作
数式を1行ずつ入力する必要はありません。
C2に数式を入力したあと、セル右下にある小さな四角形のフィルハンドルを下方向へドラッグすると、行番号を自動調整しながら数式をコピーできます。
検索値のA2はA3、A4へと変化しますが、$F$2:$H$10は固定されたままです。
これにより、各行の注文番号に応じた結果を表示できます。
検索範囲を絶対参照にしていないと、コピー先で検索表までずれてしまいます。
Excelのテーブル機能を使っている場合は、数式を1件入力するだけで列全体に自動入力されることもあります。
数式が必要な行だけに設定したい場合は、通常のセル範囲で作成してからオートフィルする方法も選べます。
【操作のポイント】数式セルを選択し、フィルハンドルをダブルクリックすると、隣接列の入力データが続く位置まで自動コピーできます。
検索結果が空欄のときの0表示対策
続いては、検索値は存在するものの、検索先の返却セルが空欄である場合に0が表示される対策を確認していきます。
| 商品コード | 商品名 | 備考 |
|---|---|---|
| P001 | ノート | 通常品 |
| P002 | ペン | |
| P003 | 消しゴム | 在庫確認中 |
検索先のセルが本当に空欄の場合、VLOOKUPは数値として0を返すことがあります。
備考列などを空欄のまま表示したいときは、VLOOKUPの結果をIF関数で確認します。
VLOOKUPの結果そのものをIF関数で判定する式
検索値の空欄ではなく、取得した結果が空欄かどうかを確認したい場合は、VLOOKUP関数をIF関数の条件に入れます。
=IF(VLOOKUP(A2,$F$2:$H$10,3,FALSE)=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))
この数式は、最初のVLOOKUPで取得結果を確認し、その結果が空文字なら空白を返します。
空欄でなければ、後半のVLOOKUPで実際の内容を表示する構成です。
たとえばP002の備考セルが未入力なら、結果セルに0を出さず、空欄として見せられます。
検索キーがある場合でも、参照先の未入力を空欄として扱えるのがこの式の利点です。
ただし、この書き方ではVLOOKUP関数を同じ式の中で2回実行します。
小規模な表では問題になりにくいものの、数万行のデータや複数の検索式があるブックでは、再計算が重くなる場合があります。
【操作のポイント】取得対象が数値列の場合、実際の0と空欄を区別したいかどうかを、数式作成前に決めておきましょう。
空文字と本当の空白の違い
Excelでは、見た目が空欄でも内部的な状態が異なることがあります。
何も入力されていないセルは空白セルです。
一方で、別の数式が=””を返しているセルは、空文字列を返しているセルです。
どちらも画面上では空欄に見えますが、COUNTIF関数、ISBLANK関数、ピボットテーブルなどの集計で扱いが変わることがあります。
VLOOKUPで空欄表示を作るために使う””は、厳密には空白セルではなく空文字列です。
表示を空にすることと、セルの内容を完全に未入力にすることは別の処理と考えると理解しやすくなります。
請求書や帳票の見た目を整える目的なら、空文字列で問題ないケースがほとんどです。
後で空欄数を厳密に数える集計を行う場合は、数式が入ったセルも対象になる点を確認しておきます。
空欄に見えるセルの確認には、次のような式も使えます。
=A2=””
TRUEなら、未入力セルまたは空文字列を返すセルとして判定できます。
【操作のポイント】ISBLANK関数は空文字列を返す数式セルを空白として判定しないため、見た目で空欄を判定するなら=A2=””が便利です。
0を非表示にする表示形式との使い分け
数式を変更せずに、ワークシート上の0だけを見えなくする方法もあります。
Excelのオプションから、シートごとの表示設定でゼロ値を表示しないように設定できます。
また、ユーザー定義の表示形式を利用して、0の部分だけを空欄に見せることも可能です。
ただし、この方法は本来表示すべき数値の0まで見えなくなる可能性があります。
在庫数0、金額0、数量0のように、0自体に意味がある表では不向きです。
一方、備考や担当者名、補足情報のような文字列を返す列で0が邪魔になる場合は、IF関数で数式側を制御するほうが安全です。
数式で空欄を返す方法は、印刷時、PDF保存時、別シート参照時にも意図が伝わりやすいという長所があります。
【操作のポイント】数値0を残す必要がある帳票では、シート全体のゼロ値非表示ではなく、対象列だけをIF関数で調整します。
IFERRORを組み合わせたエラー非表示
続いては、検索値が見つからないときの#N/Aエラーも空欄にしたい場合の数式を確認していきます。
| 入力コード | 検索結果 | 状態 |
|---|---|---|
| P001 | ノート | 正常 |
| P999 | #N/A | 検索表に存在しない |
| 未入力 |
空欄と検索エラーの両方を画面上で非表示にする代表的な式です。
=IFERROR(IF(VLOOKUP(A2,$F$2:$H$10,3,FALSE)=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE)),””)
IFERRORで#N/Aを空欄にする数式
VLOOKUPで検索値が見つからない場合、通常は#N/Aエラーが表示されます。
入力途中の表や、任意入力のコード欄では、#N/Aが多数並ぶと見た目が悪くなるかもしれません。
この場合は、式全体をIFERROR関数で囲みます。
=IFERROR(VLOOKUP(A2,$F$2:$H$10,3,FALSE),””)
この式は、VLOOKUPが正常に結果を返したときはその値を表示し、エラーになったときだけ空欄を返します。
IFERRORは#N/Aだけでなく、#VALUE!や#REF!などもまとめて隠す関数です。
そのため、単純な入力フォームでは扱いやすい一方、数式の誤りまで見えなくなる点には注意が必要です。
数式バーの赤枠部分が、エラーを空欄へ変換するIFERROR関数です。
検索表にないP999を入力しても、C2には#N/Aではなく空欄が表示されます。
【操作のポイント】検索漏れを利用者へ知らせたい業務表では、エラーを完全に隠さず「未登録」などの文字を返す設計も検討します。
検索値の空欄と検索エラーを同時に処理する式
検索値が空欄なら空欄、検索値が存在しない場合も空欄、検索先のセルが空欄なら空欄にしたい場合があります。
この条件をまとめると、次の数式になります。
=IF(A2=””,””,IFERROR(IF(VLOOKUP(A2,$F$2:$H$10,3,FALSE)=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE)),””))
最初のIF関数は、A2が未入力の状態を処理します。
IFERROR関数は、検索表に該当コードがない状態を処理します。
内側のIF関数は、検索先の返却セルが空欄である状態を処理します。
空欄の原因を3種類に分けて考えると、複雑な式でも役割を理解しやすくなります。
ただし、同じVLOOKUPが2回含まれるため、大きなブックでは計算負荷を確認しましょう。
Microsoft 365などでXLOOKUP関数を使える環境なら、より読みやすい式に置き換えられる場合もあります。
【操作のポイント】数式が長くなったら、数式バーで改行しながら確認し、括弧の対応を一組ずつ確かめます。
IFNAとIFERRORを選ぶ判断
VLOOKUPで起こる代表的なエラーは、検索値が見つからないときの#N/Aです。
この#N/Aだけを空欄にしたいなら、IFERRORではなくIFNA関数を使う方法があります。
=IFNA(VLOOKUP(A2,$F$2:$H$10,3,FALSE),””)
IFNA関数は#N/Aだけを処理し、参照範囲の指定ミスによる#REF!などは表示したままにします。
そのため、数式の異常を早期に見つけたい管理用ファイルではIFNAが向いています。
検索対象がないことだけを非表示にしたいならIFNA、すべてのエラーを表示したくないならIFERRORという使い分けです。
提出用の帳票と、作業中の管理表で数式を分ける考え方も有効です。
【操作のポイント】原因不明のエラーがある段階ではIFERRORを外し、エラー内容を表示してから修正すると原因を追いやすくなります。
VLOOKUPとIF関数の参照範囲設定
続いては、空欄表示の数式を正しくコピーするために必要な参照範囲と列番号の設定を確認していきます。
| F列 | G列 | H列 |
|---|---|---|
| 商品コード | 商品名 | 単価 |
| P001 | ノート | 120 |
| P002 | ペン | 80 |
検索範囲を絶対参照に固定する設定
VLOOKUPの検索範囲は、数式を下方向へコピーしても動かないように固定します。
たとえばF2からH10を検索表にするなら、$F$2:$H$10と入力します。
ドル記号は列と行を固定する絶対参照の記号です。
=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))
これをC3へコピーすると、検索値のA2だけがA3へ変わります。
検索範囲は変化しないため、どの行でも同じマスターデータを参照できます。
F4キーを押すと、選択中のセル参照を絶対参照へ切り替えられます。
【操作のポイント】検索表の行数が今後増えるなら、余裕を持った範囲を指定するか、Excelテーブルとして管理します。
列番号の数え方と返却列の確認
VLOOKUPの3番目の引数は、検索範囲の中で何列目のデータを返すかを指定します。
範囲がF2:H10なら、F列は1、G列は2、H列は3です。
商品名を返すなら2、単価を返すなら3を指定します。
シート全体でH列だから8と指定するわけではありません。
列番号は検索範囲の左端から数えるという点がVLOOKUPの重要なルールです。
返却したい列が検索列より左側にある場合、VLOOKUPでは対応しにくくなります。
その場合は、検索表の並びを変更するか、INDEX関数とMATCH関数、またはXLOOKUP関数を検討します。
【操作のポイント】列を途中で挿入すると列番号が意図せず変わることがあるため、数式の結果を確認してから保存します。
検索値のデータ型をそろえる確認
見た目が同じコードでも、片方が文字列、もう片方が数値として保存されているとVLOOKUPで一致しないことがあります。
たとえば00123という社員番号は、数値として入力すると123になり、先頭の0が消えます。
コード類は文字列として統一するのが基本です。
セルの左上に緑の三角形が表示される場合は、数値が文字列として保存されている可能性があります。
一方で、数値として検索すべき金額や数量では、文字列混在がないかを確認します。
不要な前後スペースを除去したい場合の例です。
=IF(TRIM(A2)=””,””,IFERROR(VLOOKUP(TRIM(A2),$F$2:$H$10,3,FALSE),””))
ただし、検索表側にも余分な空白があると一致しないため、元データも整える必要があります。
【操作のポイント】コード列は表示形式を文字列に設定し、貼り付け前に形式をそろえると検索エラーを減らせます。
空欄表示がうまくいかない原因
続いては、IF関数を組み合わせても0やエラーが残る場合に確認したい原因を解説していきます。
| 表示内容 | 主な原因 | 確認箇所 |
|---|---|---|
| 0 | 参照先が空欄 | 返却列の元セル |
| #N/A | 検索値が不一致 | コード、空白、形式 |
| #REF! | 列番号や範囲の不整合 | VLOOKUPの引数 |
数式セルに0が残る場合の確認
空欄用の数式を入れたのに0が表示される場合は、どのセルが空欄なのかを確認します。
検索値セルが空欄なのか、検索先の返却セルが空欄なのかで、必要な式が異なります。
検索値が空欄なら、=IF(A2=””,””,VLOOKUP(…))の形が必要です。
返却先が空欄なら、=IF(VLOOKUP(…)=””,””,VLOOKUP(…))の形が必要です。
数値の0が検索表に実際に入力されている場合は、それを空欄にしてよいか業務上の意味を確認しましょう。
本物の0と、空欄が原因で発生する0を混同しないことが重要です。
【操作のポイント】数式の評価を確認したいときは、数式タブの数式の検証を使うと、どの条件が実行されたか追跡できます。
検索できない#N/Aエラーの対処
#N/Aは、指定した検索値が検索範囲の先頭列に見つからないことを表します。
検索範囲に該当データがない、全角と半角が異なる、余分なスペースがある、文字列と数値が混在しているといった原因が考えられます。
検索値セルをダブルクリックして、実際の文字列を確認します。
検索表側も同じように確認し、見えない空白がないか調べます。
完全一致のFALSEを指定している場合、1文字でも違えば一致しません。
IFERRORでエラーを隠す前に、元データの不一致を修正することが大切です。
【操作のポイント】検索値と検索表のセルを並べてLEN関数で文字数を比較すると、余分なスペースに気付きやすくなります。
数式が長くなったときの管理方法
IF、IFERROR、VLOOKUPを重ねると、数式が長くなり、編集ミスが起きやすくなります。
まずはVLOOKUP関数だけで正しい値を返せるか確認します。
次にIFERRORを追加し、検索できない場合の表示を調整します。
最後に、検索値が空欄の場合や取得結果が空欄の場合のIF関数を加えると、問題の切り分けが容易です。
ExcelのバージョンによってはLET関数を使い、VLOOKUPの計算結果を一度だけ変数に保存する方法もあります。
ただし、共有先に古いExcel環境が含まれる場合は、互換性を優先して基本関数で作るのが無難です。
【操作のポイント】複雑な数式は、完成後に数式セルへコメントやメモを残し、何を空欄にするための式か記録します。
XLOOKUP関数への置き換えと使い分け
続いては、新しいExcelで利用できるXLOOKUP関数を使い、空欄表示をより簡潔にする方法を確認していきます。
| 関数 | 検索方向 | 見つからない場合の指定 |
|---|---|---|
| VLOOKUP | 左から右 | IFERRORなどを追加 |
| XLOOKUP | 左右どちらも可能 | 引数で指定可能 |
XLOOKUPで見つからない場合を空欄にする式
XLOOKUP関数では、検索値、検索範囲、返却範囲に加えて、見つからない場合の値を指定できます。
=XLOOKUP(A2,$F$2:$F$10,$H$2:$H$10,””)
この式では、A2の値をF2:F10から検索し、対応するH2:H10の値を返します。
見つからない場合は、4番目の引数に指定した””により空欄表示になります。
XLOOKUPは検索範囲と返却範囲を別々に指定できるため、VLOOKUPより柔軟です。
返却列が検索列より左側にあっても検索でき、列番号を数える必要もありません。
【操作のポイント】XLOOKUPが使えるかどうかはExcelのバージョンに左右されるため、共有先の環境を確認してから採用します。
検索値が未入力なら計算しないXLOOKUPの式
XLOOKUPでも、検索値セルが空欄のときはIF関数で先に処理すると、入力途中の表示を整えられます。
=IF(A2=””,””,XLOOKUP(A2,$F$2:$F$10,$H$2:$H$10,””))
この式では、A2が空欄なら空欄を返し、値がある場合だけXLOOKUPで検索します。
検索しても見つからない場合は、XLOOKUPの4番目の引数によって空欄になります。
検索先のH列が空欄の場合も、結果として空欄に見えます。
空欄を返す条件をまとめやすいため、Microsoft 365環境では実務向きの選択肢です。
【操作のポイント】VLOOKUPからXLOOKUPへ移行する際は、列番号ではなく返却範囲を指定する書き方へ変更します。
既存ブックでVLOOKUPを使い続ける判断
古いExcelとの互換性が必要なファイルでは、VLOOKUP関数を使い続ける場面があります。
取引先、社内共有PC、マクロ付きテンプレートなどで旧バージョンのExcelを使う可能性があるなら、XLOOKUPは利用できないことがあります。
その場合は、IF関数、IFERROR関数、VLOOKUP関数を組み合わせた数式で十分に対応できます。
重要なのは、どの関数を使うかよりも、空欄、0、検索エラーをどう見せるかという表示設計です。
利用者が入力途中に混乱せず、登録漏れにも気付ける表を目指しましょう。
【操作のポイント】配布用テンプレートでは、使用しているExcel関数と対応バージョンをシート上に記載しておくと親切です。
まとめ エクセルのVLOOKUPで空白なら空白を返す方法
エクセルのVLOOKUPで空白なら空白を返すには、空欄になる原因に合わせてIF関数やIFERROR関数を組み合わせます。
検索値セルが未入力なら、=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$10,3,FALSE))の形が基本です。
検索先のセルが空欄で0が表示される場合は、VLOOKUPの結果をIF関数で判定します。
検索値が見つからない#N/Aも非表示にしたい場合は、IFERRORまたはIFNAを追加しましょう。
空欄、実際の0、検索エラーはそれぞれ別の意味を持つため、表の用途に合わせて表示を設計することが大切です。
検索範囲は絶対参照で固定し、列番号は検索範囲の左端から数えます。
Microsoft 365などの新しい環境では、XLOOKUP関数に置き換えることで、見つからない場合の空欄表示を簡潔にできます。
不要な0やエラーを減らしつつ、必要なデータ異常は見逃さない数式を選び、見やすいExcel表に仕上げましょう。
