エクセルVLOOKUP関数でヒットしない数字

質問

A列　　　　B列　　　　　　　　　D列　　　　　E列
CODE1　　　　VLOOKUP　　　　CODE2　　　　NAME
01-A000　　ああああ　　　　01　　　　ああああ
10-A123　　#N/A　　　　　　05　　　　いいいい
20-B223　　#N/A　　　　　10　　　　うううう
30-C444　　#N/A　　　　　15　　　　ええええ
AB-S455　　きききき　　　　　18　　　　おおおお
A4-5554　　くくくく　　　　　20　　　　かかかか
　　　　　　　　　　　　　　　　AB　　　　きききき
　　　　　　　　　　　　　　　　A4　　　　くくくく
　　　　　　　　　　　　　　　　以下略　　　　以下略

のようなデータがあります。
A列のコード１の先頭2文字でD列をVLOOKUP検索します。
A列、B列ともに書式は文字列としています。
=VLOOKUP(LEFT(A2,2),$D$2:$E$29,2,0)　という式をいれフィルドラッグすると先頭2文字が数字になるデータだけが01以外ヒットしません。
=VLOOKUP(IF(ISERROR(LEFT(A2,2)*1),LEFT(A2,2),LEFT(A2,2)*1),$D$2:$E$29,2,0)　とすると、ある程度解消するのですが、1をかけると1になってしまう01がヒットしなくなるのは当然として、それ以外の数字でもD列にあるのにヒットしない場合があります。
D列のデータに先頭にシングルクォーテーションを入れればすべて解決するのですがそれ以外に解決する方法はないでしょうか？

回答No.5

Wendy02 · ベストアンサー

>D列のセル書式は文字列になっています。

Excel は、文字列書式にしたら、そのセルを参照している数式は「不活性化」になるはずだと思いますが、私だけでしょうか？　調べてみましたが、Excel 2007までは、「不活性」状態になる現象は直っていないはずです。

ところで、

現在の数式
=VLOOKUP(IF(ISERROR(LEFT(A2,2)*1),LEFT(A2,2),LEFT(A2,2)*1),$D$2:$E$29,2,0)

私の設定なら、エラーは発生していません。
A列の[CODE1]も、当然、一般/Gなどの書式にしておきます。
D列の[CODE2]は、数値のままで、左詰め、書式、「00」です。

書式-文字列は、孤立しているセルなり列ならよいのですが、参照先としては、そのような設定は使えないはずです。だから、どうしてもという場合は、Lotus1-2-3にある、プレフィクスの「'」を代用するわけです。別に、それが何かを邪魔するというわけでもありませんが、数式バーには出てきてしまいます。

後、エラーが出るとすれば、[CODE1]のA列の2文字が正しくないとか、そういう所ぐらしかないように思いますね。

お礼

ありがとうございます。
D列については自分で作ったデータではないので一律にプレフィクスの「'」を付加することにしました。
これなら安心ですよね？

回答No.7

nag0720

＃４です。

書式が文字列になっているなら、

If IsNumeric(C.Value) Then
　C.Value = Format("00", C.Value)
End If

でもＯＫです。

お礼

ありがとうございます。

回答No.6

mu2011

添付の画像からはＤ列がセルの配置で左詰を指定していない限り数値になっているとは考えずらい。
よって、一致しない数字のセルにスペース等の不要なコードが混入している可能性がありますので確認して下さい。

>D列のデータに先頭にシングルクォーテーションを入れればすべて解決するのですがそれ以外に
>解決する方法はないでしょうか？
　⇒仮にセルの配置で左詰でセルの書式が標準、数値の場合、その範囲を選択→データ→区切り位置→
　　次へ→次へ→文字列を選択→ＯＫとして見て下さい。

お礼

>　添付の画像からはＤ列がセルの配置で左詰を指定していない限り数値になっているとは考えずらい。

その通です。
数値ではないのに数値の扱いになるのが問題なのです。

>　一致しない数字のセルにスペース等の不要なコードが混入している可能性

チェック済みですからありません。

回答No.4

nag0720

D列の書式は文字列とのことですが、D列に数値を入力した後に書式を文字列に変更していませんか？
数値を入力した後に書式を文字列にしても、見た目は左詰めで表示されるので文字列のように見えますが、中身は数値のままです。

01と05だけは、入力した後に書式を文字列にすると前ゼロが付かないので、書式を文字列に変更した後で入力し直したのではありませんか？
書式を文字列にした後に入力すれば数値であっても文字列となります。
01だけうまくいっているのはそのためでしょう。

10,15,18,20,30などの２桁の数値を再度入力し直せば大丈夫だと思います。

お礼

> D列の書式は文字列とのことですが、D列に数値を入力した後に書式を文字列に変更していませんか？

掲示したデータはそうです。
本番データはよそからもってきたものですからどういう入力をされたかはわかりません。

> 10,15,18,20,30などの２桁の数値を再度入力し直せば大丈夫だと思います。

どうもデータを作り直さなくてはならないようですね。
数が多いので

For Each C In Selection
If IsNumeric(C.Value) Then
C.Value = "'" & C.Value
End If
Next

で、あたまにシングルクォーテーションをつけて逃げることにします。

回答No.3

old-tigers

状況からみると、No.1さんがおっしゃる通り、D列は数値として認識されているようですが、
=VLOOKUP(LEFT(A2,2),$D$2:$E$29,2,0)
↓
=VLOOKUP(TEXT(LEFT(A2,2),"00"),$D$2:$E$29,2,0)　　
でどうでしょうか？

お礼

> =VLOOKUP(TEXT(LEFT(A2,2),"00"),$D$2:$E$29,2,0)　　
> でどうでしょうか？

LEFT関数で取り出した時点で数字は文字列化しますのでTEXT関数では残念ながら意味が無いです。
書式では文字列としているのに数値として認識されているD列をどうするかが問題のようです。
ありがとうございました。

回答No.2

usanekoten

Excel2007だと、30-C444以外は正常にヒットしましたけど。

数式を
=VLOOKUP(IF(ISERROR(LEFT(A2,2)*1),LEFT(A2,2),LEFT(A2,2)*1),$D$2:$E$29,2,0)
にするとある程度解消するという事は、No.1さんの言われるとおり、D列の中身が数字扱いになっていると思われます。

データを先に入れた後に書式を変更しても見た目は変わりますが、データは元の書式のままなのでその場合は該当セルにて、F2を押して入力状態にした後エンターで確定処理を行ってみてください。

セルの中身が数字かどうかの判定は
=ISNUMBER(D2)
を使えば判定できます。(D2は検査したいセルのアドレスです)

お礼

> Excel2007だと、30-C444以外は正常にヒットしましたけど。

2007ではこんな問題が起きないのですか！？
私はまだ2000なんです。

>　データを先に入れた後に書式を変更しても見た目は変わりますが、データは元の書式のままなのでその場合は該当セルにて、F2を押して入力状態にした後エンターで確定処理を行ってみてください。

ううっ、やはりそうしなきゃだめですか。
なにぶん数がおおいものですから・・・・。

> 数式を
> =VLOOKUP(IF(ISERROR(LEFT(A2,2)*1),LEFT(A2,2),LEFT(A2,2)*1),$D$2:$E$29,2,0)
> にするとある程度解消するという事は、No.1さんの言われるとおり、D列の中身が数字扱いになっていると思われます。

はい、それはわかります。だからこの式をつくったわけですから。

補足

本日、2007で試しました。
やはり同じでした。

回答No.1

dg13

A列の書式が文字列として入っているので、D列も文字列でないとヒットしないです。現在、D列は数値として入っています。シングルクォーテーションを入れる(数値を文字列にする)とヒットするというのはそのためです。
シングルクォーテーションを入れる以外の解決策としては、D列のデータを書式設定から分類を「文字列」に変更すればいいです。A列のデータはコードに英字などの文字が入っているので数値にはできませんからね。VLOOKUPでマッチングするときは形式を揃えてあげないとダメです。

お礼

> D列も文字列でないとヒットしないです。現在、D列は数値として入っています。

ありがとうございます。
D列のセル書式は文字列になっています。

添付画像でD列の数字が左によっているのでわかるとは思いましたが質問に誤記がありました。

誤）A列、B列ともに書式は文字列としています。
正）A列、D列ともに書式は文字列としています。
B列は数式ですので文字列ではありません。