Excel VLOOKUP関数で大文字小文字を区別する方法

2020年10月15日

VLOOKUP関数XLOOKUP関数は検索の際に

大文字・小文字を無視して検索してしまうため

意図した結果にならないことがあります。

VLOOKUP関数で大文字小文字を無視して検索してしまう場合のキャプチャ

この記事ではその問題を解決する方法を紹介します。

手順

IF関数SUMPRODUCT関数EXACT関数INDEX関数ROW関数を利用します。

=IF(SUMPRODUCT(EXACT(検索範囲, 検索値)*1)>0,INDEX(表示項目の範囲,SUMPRODUCT(EXACT(索範囲,検索値)*ROW(検索範囲))),"")

検索値VLOOKUP関数の第1引数検索範囲VLOOKUP関数の第2引数(の1列のみ)

表示項目の範囲VLOOKUP関数の第3引数(列番号ではなく列範囲)

考えると理解しやすいでしょう。

大文字と小文字を区別した検索を行う図解

VLOOKUP関数XLOOKUP関数ではこの場合、範囲は3行目から指定すれば良いですが

この数式では必ず1から指定する必要があります。

これにより大文字小文字を区別した検索が行われます。

数式の改善例(LET関数が使える場合)

2020年10月頃にMicrosoft(Office)365で追加された新機能のLET関数を利用すれば

検索範囲のセル指定の重複を除外することが可能です。

これによりセルアドレス指定が一か所になるため、

参照セルを変更する際に場合に変更する手間と間違えるリスクを減らすことが可能です。

=IF(
    SUMPRODUCT(EXACT($B$1:$B$6, E3)*1)>0,
    INDEX($C$1:$C$6,SUMPRODUCT(EXACT($B$1:$B$6,E3)*ROW($B$1:$B$6))),
    ""
)

↑改善前 ↓改善後(名前と改行、インデントは任意です)

=LET(
  検索範囲,$B$1:$B$6,
  表示項目の範囲,$C$1:$C$6,
  検索値,E3,
  IF(
     SUMPRODUCT(EXACT(検索範囲,検索値)*1)>0,
     INDEX(表示項目の範囲,SUMPRODUCT(EXACT(検索範,検索値)*ROW(検索範囲))),
     ""
  )
)

表示項目の範囲は重複していないためLET関数で名前を付ける意味は低いですが、

セル範囲の意図を明確にするために名前をつけています。

関連記事