はじめに
人気、機能ともにトップクラスのExcel関数VLOOKUP関数ですが、実は検索の際に大文字と小文字を区別できないという欠点があります
（VLOOKUP関数にとっては『Aa』も『AA』も同じなのです）
同じようなことができるINDEX関数とMATCH関数のネストも有名ですが、MATCH関数が大文字と小文字を区別できないため、こちらの方法でも大文字と小文字は区別できません
しかし！大文字と小文字を区別してVLOOKUP的なことがしたいケースもあると思うので、この記事で数式を紹介します！
『VLOOKUP的な表引き』と表現していますが、VLOOKUP関数との挙動が異なる点がいくつかあります
その点については『VLOOKUP関数との違い』に記載していますので、そちらを参照して下さい
はじめに紹介する簡易的な式には
意図しない結果になる可能性があるので
ご自身の用途に照らし合わせて下さいね！
大文字と小文字を区別してVLOOKUP関数のように表引きする数式
シノさん！
何もしていないのにExcelが壊れました！！
えっ！？なんですか急に？？
VLOOKUP関数が大文字と小文字を
区別してくれないんです…
あ、それは正常ですね
そうなんですか！？
安心して下さい、大文字と小文字を区別して
VLOOKUP的なことができる数式を紹介します！！
大文字小文字を区別する表引きの数式（簡易版）
<例で使用している数式>
・大文字と小文字を区別してA列を検索、B列の値を返す
=INDEX(B:B,SUMPRODUCT(ROW(A2:A3)*EXACT(D2,A2:A3)))
・大文字と小文字を区別せずA列を検索、B列の値を返す
=VLOOKUP(D2,A2:B3,2,FALSE)
EXACT関数…
EXACT関数は、大文字と小文字を区別して
2つの値が一致しているかどうかを判定して
答えを返してくれるExcel関数です！
一致ならTrue、不一致ならFalseを返します
ネストが複雑で難しいですね
内側から見てみましょうか
EXACT関数で検索値と一致しているかどうかを
TrueとFalseで判定します
その結果とROW関数をかけることで
一致する行の番号が得られます
例でいうと
{2,3}と{FALSE(0),TRUE(1)}を掛け算して
{0,3}…ということですね
はい、その{0,3}をSUMPRODUCT関数で合計した3を
INDEX関数の第2引数(行番号)に渡します
第1引数がB:Bですので、セルB3という結果になります
な、なるほど～
お疲れ様でした
ただ、この数式には問題があるんです
紹介した数式の問題点（対策数式あり）
しかしこの数式には欠点があります
検索範囲に検索値と一致する値が複数あると
結果が意図しないものになります
なるほど、3行目と4行目で一致する値を見つけると
3+4で7行目を返すんですね
問題点を解消した大文字と小文字を区別する表引きの数式
そこで、3と4を足し算するのではなく
小さい方、大きい方をINDEX関数に渡すことで
この問題を解消します
<例で使用している数式>
配列数式ですので、スピル非対応のバージョンでは『Ctrl + Shift + Enter』での確定が必要です
数式の前後にカッコ（{}）が追加されます
・上から探して一番
{=INDEX(B:B,SMALL(ROW(A2:A8)*EXACT(D2,A2:A8),SUM(((ROW(A2:A8)*EXACT(D2,A2:A8))=0)*1)+1))}
・下から探して一番
{=INDEX(B:B,MAX(ROW(A2:A8)*EXACT(D2,A2:A8)))}小さい方…上から探す方は数式が長いですね！
{0,3,4,0,0,0,0}の中からMAX関数で一番大きな数字を拾うのは簡単ですが
0を除いて一番小さい数字を拾うのは少々苦労します
SMALL関数は複数の数字から
小さい順で指定した順位の数字を返す関数…でしたよね
その通りです！
『SUM(((ROW(A2:A8)*EXACT(D2,A2:A8))=0)*1)+1』
この部分で小さい順で、0の数+1の順位にあたる数字を返します
{0,3,4,0,0,0,0}の場合は0が5個なので
小さい順6位の3になるんですね
A列の値に対するB列の値が1通りなら
MAX関数の方を使いたいです
それで良いと思いますよ！
（上位互換）大文字と小文字を区別する表引き数式の紹介
Twitterで光希桃先生によりスマートな数式を教えていただきました。
素晴らしい数式を紹介します！
<例で使用している数式>
・上から
=INDEX(B2:B8,MATCH(TRUE,INDEX(EXACT(D2,A2:A8),0),0))
・下から
=LOOKUP(2,1/EXACT(D2,A2:A8),B2:B8)これはまた…ずいぶんとスッキリしましたね
VLOOKUP関数的なことをしたいのに、VLOOKUP関数の要素が一欠片もなかったのでLOOKUP関数が出てきてちょっと安心したところありますよ
たしかに…
まず上から一番目を返す数式についてですが EXACT関数で判定したTRUEとFALSEの配列からMATCH関数でTRUEを探せば一番最初に見つかった位置の場所を返してくれるということのようです
下から一番目の数式は 1をEXACT関数で判定したTRUEとFALSEの配列で割ることで1と#DIV/0!エラーの配列を作り出し、 そこからLOOKUP関数を使って2を探すことで下から一番に見つかる値を返しています
近似一致の仕様、だそうです
近似一致の仕様…
Excel関数は奥が深いですね
【重要】VLOOKUP関数との違い
この記事ではVLOOKUP的な表引きという表現をしていますが、VLOOKUP関数での表引きと異なる点があります。
VLOOKUP関数の機能に更にプラスアルファで大文字と小文字を区別する機能が加わったという認識で今回紹介した数式を使用すると、思わぬ事態を招く可能性がありますのでご注意下さい。
（こちらもTwitterにてご指摘頂きました。ありがとうございます！）
- ワイルドカードに非対応
- 数値の1と文字列の”1″、論理値のTRUEと文字列の”TRUE”などを区別しない
- 大文字小文字を区別かつ近似一致検索はできない（大文字小文字の『順番』を作れる関数がないため）
おわりに
VLOOKUP関数が大文字と小文字を区別しないというのは予期せぬトラブルを生みそうですね
とはいえ、あまり話題に上がらない気がするのは、トラブルが起きる状況がかなり限定的ということでしょうか
たしかにマスタに並べる検索コードが大文字と小文字が混在で、大文字と小文字を区別しない場合同一となるルールで作成されるケースはあまりないのかもしれません
そういうニッチな用途での数式を考えて紹介してみました
筆者のExcelはバージョンが2016なのでMINIFS関数が使えないのですが、使える環境であれば検索値が複数で上から探す数式はSMALLを外してもっとスマートになりそうです
最後までお付き合い頂き、ありがとうございました！
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています！
よろしければこちらの記事もどうぞ～！
コメント