VLOOKUP関数では検索値が重複する場合、最初に見つかった値のみを返します。しかし、これでは間違ったデータを表示させてしまう可能性があります。そこで、検索値が重複する場合には「重複あり」というメッセージを表示させるようにします。
VLOOKUP関数で検索値が重複する場合、重複があることを知らせる
たとえば以下のように注文内容の商品名から価格を表示させる場合を考えます。以下の商品リストでは「商品D」という商品名が重複しています。
以下のように「商品D」の価格をVLOOKUP関数を使って表示させるようにします。VLOOKUP関数の詳しい使い方は以下の記事をご覧ください。
=VLOOKUP(B3,$E$3:$F$12,2,FALSE)
そうすると、最初の「商品D」の価格が表示されます。
しかし、もし下の価格が正しい場合、誤った結果を表示させていることになります。そこで、検索値が重複する場合は重複していることを知らせるようにします。
ここではIF関数とCOUNTIF関数を使って検索値が重複していることを知らせるようにします。
IF関数では、以下のように条件分岐させます。
- 重複がある場合⇒「重複あり」という文字列を表示
- 重複がない場合⇒VLOOKUP関数で検索した結果を表示
重複があるかはCOUNTIF関数を使用します。条件としては「商品D」と同じ値が1つより多いこととします。
数式にまとめると以下のように入力します。
=IF(COUNTIF($E$3:$E$12,B3)>1,”重複あり”,VLOOKUP(B3,$E$3:$F$12,2,FALSE))
これで商品Dの価格には「重複あり」というメッセージが表示されます。
重複しない商品については正しく価格が表示されます。
更新頻度が多く、重複が発生する可能性がある場合は、IF関数とCOUNTIF関数を使って重複を検知する仕組みをつくっておくことをおすすめします。
【補足】XLOOKUP関数を使って下方向から検索
Excel 2021以降またはMicrosoft 365を使用している場合は、XLOOKUP関数を使って下方向から検索することもできます。XLOOKUP関数の使い方については下記記事をご覧ください。
XLOOKUP関数の最後の引数「検索モード」で「末尾から先頭へ検索（-1）」を指定します。
=XLOOKUP(B3,$E$3:$E$12,F3:F12,,,-1)
そうすると、下の商品Dの価格が表示されます。
最新のデータを下に更新していくような表であれば、この方法で常に最新のデータを取得することができます。ただし、この方法ではデータが1対1で対応せず、誤解を生じさせる可能性があるので、あまりおすすめはしません。
コメント