vlookup関数のエラー対策について

質問

いつもお世話になっております。

vlookup関数ですが、現状は検索値や参照先が未入力の場合は空白になるように、
＝if( =" ", vlookup( , ,0)&" ")という式（…式1）を使っています。
これは文字列扱いになるらしく参照した金額などを範囲選択しても合計金額が表示されないようです。
一方でiferror関数はエラーがあると空白になるように、
=iferror(vlookup( , ,0)," ")という式（…式2）を入れると数値扱いになる認識ですが、他のエラーは表示されない（？）と把握しています。

ここで質問ですが、自分の希望としては
(1)エラー表示したい（空白にしたくない）…検索値が誤っていたり（一致しない）、テーブル範囲の間違い、参照先が見つからないなど（#N/Aや#REF）
(2)エラー表示したくない（無視したい）…検索値や参照先が未入力（←式1はクリア）

のですが、他にも出る可能性があるエラーはありますか？
また式1は上記(1)のエラー表示はされる、式２はされない認識でいいんでしょうか？
上記希望をできるだけ満たした算式があれば教えていただきたく（できれば数値が好ましい）よろしくお願いします。

回答No.2

bunjii · ベストアンサー

>これは文字列扱いになるらしく参照した金額などを範囲選択しても合計金額が表示されないようです。
SUM関数は文字列を無視しますので０が返るでしょう。

>一方でiferror関数はエラーがあると空白になるように、・・・・
IFERROR関数はすべてのエラー検出に対して代替値を返しますのでエラーの種類を返すことはありません。

>(1)エラー表示したい（空白にしたくない）
IFERROR関数は使えません。

>(2)エラー表示したくない（無視したい）…検索値や参照先が未入力
IFERROR関数で対応可能です。
VLOOKUP関数で検索値が未入力では#N/Aが変えり、IFERROR関数でエラー時の代替値を返します。
また、VLOOKUP関数の抽出対象セルが未入力のときは０が返りますのでエラーになりません。

>上記希望をできるだけ満たした算式があれば教えていただきたく
(1)と(2)は相反する要件なので両方を満たすことはできないでしょう。
提示のIF関数を次のように修正されては如何でしょうか？。
=if( =" ", vlookup( , ,0)&" ")
　　　　↓
=if( =" ", vlookup( , ,0))
VLOOKUP関数の返り値をそのまま使うことで合計を算出するときに不都合が無くなります。
尚、数式の添削を求めるには実際のセル範囲や模擬データを提示すべきです。
質問の内容では的確な数式を提示することが困難です。（要件が不備です）

お礼

みなさま、回答ありがとうございます！

ベストアンサーは非常に迷いましたが丁寧でわかりやすく感じましたので
選ばせていただきました。
どうもありがとうございました。

回答No.4

kagakusuki

質問者様がやりたいと仰っている内容に矛盾があります。

>(1)エラー表示したい（空白にしたくない）…検索値が誤っていたり（一致しない）、テーブル範囲の間違い、参照先が見つからないなど（#N/Aや#REF）
>(2)エラー表示したくない（無視したい）…検索値や参照先が未入力（←式1はクリア）

との事ですが、「検索値が未入力」というのは「検索値が誤っていたり（一致しない）」の一種であり、同じエラー内容の事です。(#N/Aとなる)
　同様に、「参照先が未入力」の場合、探すべき参照先が設定されていないという事であり、無いものを見つける事は出来ませんから、つまるところ「参照先が未入力」というのは「参照先が見つからない」の一種であり、同じエラー内容の事です。(#REFとなる)
　同一のエラー内容に関して、「表示しない様にしながら表示させたい」というのは矛盾しております。

　従って、質問者様がやりたいと希望しておられる事は矛盾しているため、実現する術は存在致しません。

お礼

ありがとうございます！

回答No.3

mshr1962

>また式1は上記(1)のエラー表示はされる、式２はされない認識でいいんでしょうか？
良いと思いますよ。

>できれば数値が好ましい
式1でもVLOOKUPの最後の &"" を除けば数値になりますよ。
問題は、参照先側の表示する列の内容が未入力の場合、空白ではなく0が表示されることです。
&""　はその回避のための設定でしょうね。
でも、その代わりに数値はすべて文字列になります。

>他にも出る可能性があるエラーはありますか？
VLOOKUPの最後の引数が0(FALSE)の場合、
検索値に一致するデータがない場合の#N/A!だけです。
#REF!は参照先セルが削除された時に数式自体に表示されるものですので。。。

式2の方ならEXCEL2007以前ではIFERROR関数が無かったので下記の数式が使われてました。
=IF(ISNA(VLOOKUP(検索値,参照先,列番号,0)),"",VLOOKUP(検索値,参照先,列番号,0))
　※ISNAの変わりにISERRORでも可
=IF(COUNTIF(参照先の左列,検索値),VLOOKUP(検索値,参照先,列番号,0),"")

回答No.1

ushi2015

こんにちは
それぞれの式で
vlookup( , ,0)*1
vlookup( , ,0),0
にするという事でしょうか？