VLOOKUP()関数でのエラーや完全一致が複数ある場合の対応

・関数VLOOKUP
・書式=VLOOKUP(検索値,範囲,列番号,検索の型)
・内容指定された範囲の左端の列で特定の値を検索し、範囲内の対応するセルの値を返します。


サンプル
  A B C D E F
1            
2   日時 担当 内線 受付  
3   8月10日 鈴木 1234 9:00~18:00  
4   8月11日 佐藤 5678 10:00~19:00  
5   8月12日 佐藤 5678 10:00~19:00  
6   8月13日 鈴木 1234 9:00~18:00  
7   8月14日 鈴木 1234 9:00~18:00  
8   8月15日 鈴木 1234 9:00~18:00  
8   8月15日 佐藤 5678 10:00~19:00  
9   8月16日 鈴木 1234 9:00~18:00  
10   8月17日 鈴木 1234 9:00~18:00  
11   8月18日 佐藤 5678 10:00~19:00  
12   8月19日 佐藤 5678 10:00~19:00  
13   8月20日 鈴木 1234 9:00~18:00  
14   8月21日 鈴木 1234 9:00~18:00  
15            
・エラーを消すだけならISERROR()関数でエラー時に非表示にできます
 =IF(ISERROR(VLOOKUP(B1,B3:E15,2,0)),"", VLOOKUP(B1,B3:E15,2,0))
・エクセル2007以降の新しいエクセルやOFFICE ONLINE、Googleスプレッドシートであれば、IFERROR()関数でエラー時に非表示にできます
 =IFERROR(VLOOKUP(B1,B3:E15,2,0), "")
・エラー非表示でなくメッセージを出す場合
 =COUNTIF(B3:B15,B1)
 検索値の個数を取得

 =IF(COUNTIF(B3:B15,B1)=0,"検索値がありません","")
 検索値が0の時のメッセージ

 =IF(COUNTIF(B3:B15,B1)>1,"検索値が複数あります","")
 検索値が2個以上の時のメッセージ

 =IF(COUNTIF(B3:B15,B1)=1,VLOOKUP(B1,B3:E15,2,0),"")
 検索値が1個の時の値(この式でもISERROR同様エラーが非表示になります)

 =IF(COUNTIF(B3:B15,B1)=1,VLOOKUP(B1,B3:E15,2,0),VLOOKUP(B1,B3:E15,2,1))
 検索値が1個ではない場合に近似値を表示する

上記を組み合わせます
----------
=IF(COUNTIF(B3:B15,B1)=0,"検索値がありません",IF(COUNTIF(B3:B15,B1)>1,"検索値が複数あります",VLOOKUP(B1,B3:E15,2,0)))
----------
これで検索値の判定ができます。
結果例1 セルB1が8月18日の場合)佐藤
結果例2 セルB1が8月30日の場合)検索値がありません
結果例3 セルB1が8月15日の場合)検索値が複数あります


複数時の個数を表示したい場合は
複数の時の式にCOUNTIF(B3:B15,B1)を追加します
----------
=IF(COUNTIF(B3:B15,B1)=0,"検索値がありません",IF(COUNTIF(B3:B15,B1)>1,"検索値が"&COUNTIF(B3:B15,B1)&"個あります",VLOOKUP(B1,B3:E15,2,0)))
----------
これで検索値が複数の場合個数を表示します
結果例1 セルB1が8月18日の場合)佐藤
結果例2 セルB1が8月30日の場合)検索値がありません
結果例3 セルB1が8月15日の場合)検索値が2個あります


さらに検索値が0の場合は近似値を追加するようにするには
検索値が0の時の式にVLOOKUP(B1,B3:E15,2,1)を追加します(VLOOKUPの検索の型を1を指定します)
----------
=IF(COUNTIF(B3:B15,B1)=0,"検索値がありません近似値は「"&VLOOKUP(B1,B3:E15,2,1)&"」です",IF(COUNTIF(B3:B15,B1)>1,"検索値が"&COUNTIF(B3:B15,B1)&"個あります",VLOOKUP(B1,B3:E15,2,0)))
----------
結果例1 セルB1が8月18日の場合)佐藤
結果例2 セルB1が8月30日の場合)検索値がありません近似値は「鈴木」です
結果例3 セルB1が8月15日の場合)検索値が2個あります


さらにさらに複数時の近似値を表示するには
上記でも使っているVLOOKUP(B1,B3:E15,2,1)を追加します
----------
=IF(COUNTIF(B3:B15,B1)=0,"検索値がありません近似値は「"&VLOOKUP(B1,B3:E15,2,1)&"」です",IF(COUNTIF(B3:B15,B1)>1,"検索値が"&COUNTIF(B3:B15,B1)&"個あります近似値は「"&VLOOKUP(B1,B3:E15,2,1)&"」です",VLOOKUP(B1,B3:E15,2,0)))
----------
結果例1 セルB1が8月18日の場合)佐藤
結果例2 セルB1が8月30日の場合)検索値がありません近似値は「鈴木」です
結果例3 セルB1が8月15日の場合)検索値が2個あります近似値は「佐藤」です


上記のメッセージを変えて答えに()で追記する場合
----------
=IF(COUNTIF(B3:B15,B1)=0,VLOOKUP(B1,B3:E15,2,1)&"(近似値)",IF(COUNTIF(B3:B15,B1)>1,VLOOKUP(B1,B3:E15,2,0)&"(同じ結果が"&COUNTIF(B3:B15,B1)&"個あります)",VLOOKUP(B1,B3:E15,2,0)))
----------
結果例1 セルB1が8月18日の場合)佐藤
結果例2 セルB1が8月30日の場合)鈴木(近似値)
結果例3 セルB1が8月15日の場合)鈴木(同じ結果が2個あります)


ここでのおかしいところに気づいた人はすごい
サンプルが担当者を探す表だったので近似値は「鈴木」はおかしいですがそこは考えないようにします
実は最後の2つの結果例3が違う

結果例3 セルB1が8月15日の場合)検索値が2個あります近似値は「佐藤」です
結果例3 セルB1が8月15日の場合)鈴木(同じ結果が2個あります)

なぜ前者は「佐藤」で後者は「鈴木」なんでしょう
これはVLOOKUP関数の検索の型で変わります
「佐藤」と表示したのは検索の型が「1」で
「鈴木」と表示したのは検索の型が「0」だからです

検索の型が「1」の場合8月15日は2つあります
8月15日を検索して新しいのを見つけると上書きするので最後に見つけた8月15日の「佐藤」表示しています

検索の型が「0」の場合
8月15日を検索して見つけたらそれ以上探しません。なので最初に見つけた8月15日の「鈴木」表示しています

VLOOKUP関数の結果が複数あるなら全部出してほしいといつも思いますが
上記の方法で「最初に見つけた」数値と「最後に見つけた」数値の2つは取得可能です
----------
=IF(COUNTIF(B3:B15,B1)=0,VLOOKUP(B1,B3:E15,2,1)&"(近似値)",IF(COUNTIF(B3:B15,B1)>1,VLOOKUP(B1,B3:E15,2,0)&"/"&VLOOKUP(B1,B3:E15,2,1),VLOOKUP(B1,B3:E15,2,0)))
----------
結果例1 セルB1が8月18日の場合)佐藤
結果例2 セルB1が8月30日の場合)鈴木(近似値)
結果例3 セルB1が8月15日の場合)鈴木/佐藤


以上 VLOOKUP関数応用編でした

VLOOKUPTOP
(C) 2001-2017 Digital World