【Excel】INDEX関数で大文字・小文字を区別する方法

Excel

私は仕事でExcelをよく使っていますが、たまにちょっと面倒な計算をさせようとすると、解決策が簡単に見つからないことがあります。

 

ExcelのそんなTipsについて、自分の備忘録も兼ねて書いておきたいと思います。

 

VLOOKUPもINDEXも大文字・小文字が区別できない

 

下表右側(E1:G9)のようなテーブルがあって、左側の表でID(A2)を指定して、Country(B2)とCapital City(C2)の値を表示させたい場合、通常であれば、まず、VLOOKUPかINDEXを使うと思います。

 

 

しかしながら、この表には一か所IDにトラップがあり、E5=55iIk、E6=55iIKと、最後の一桁が大文字と小文字の違いがあることを除けば、完全に同じになっています。

 

この場合、VLOOKUPもINDEXもそのままでは大文字・小文字の区別が出来ないので、使えません。

 

A2=55iIK(E6の値)として、B2に、

 

=VLOOKUP(A2,E2:G9,2,FALSE)

 

もしくは、

 

=INDEX(F2:F9,MATCH(A2,E2:E9,0))

 

とすると、返ってくる答えは「Indonesia」(F5の値)です。

 

本来、E6の「55iIK」に対応するF6の「Laos」をひっかけて欲しいのですが、上記の式は、どちらも、大文字・小文字を区別しないため、先に出てくるE5の「55iIk」の方がひっかかり、F5の「Indonesia」の方が返ってきます。

 

このように、Excelでは、VLOOKUP、INDEX共に大文字・小文字を区別せず、先に出てきた値を返す、という特徴があります。

 

INDEX関数で大文字・小文字を区別させる

 

これを解消するには、大文字・小文字を区別する関数EXACTを使って、少々複雑な式を組む必要があります。

 

この例では、B2の式は、

 

=IF(SUMPRODUCT(EXACT(E1:E9,A2)*1)>0,INDEX(F1:F9,SUMPRODUCT(EXACT(E1:E9,A2)*ROW(E1:E9))),””)

 

とすると、無事に「55iIK」に対応する「Laos」が返ります。

 

 

解説すると、

 

INDEXとROWを組み合わせるときのちょっとした注意点

 

なお、この式は、

 

=IF(SUMPRODUCT(EXACT(E2:E9,A2)*1)>0,INDEX(F1:F9,SUMPRODUCT(EXACT(E2:E9,A2)*ROW(E2:E9))),””)

 

でも同じ答えが返ってきますが、

 

=IF(SUMPRODUCT(EXACT(E2:E9,A2)*1)>0,INDEX(F2:F9,SUMPRODUCT(EXACT(E2:E9,A2)*ROW(E2:E9))),””)

 

とすると、Laosの下のCambodiaが返ってきてしまいます。

 

後半の「SUMPRODUCT(EXACT(E1:E9,A2)*ROW(E1:E9))」では、E1:E9のうちのA2の値に合致する行番号を求めているので、選択した範囲がE1:E9でもE2:E9でも返ってくる値は「6」です。

 

が、その後のINDEX関数では、F1:F9の6番目の値を返すのか、F2:F9の6番目の値を返すのかで意味が異なってしまいますので、この差分が生じます。

 

 

コメント

  1. […] 昨日の「INDEX関数で大文字・小文字を区別する方法」に引き続き、今日は、INDEX関数で複数列の条件を抽出させる方法です。 […]

タイトルとURLをコピーしました