excel2010 VLOOKUPのエラー

質問

VLOOKUPを使った作業をしていますが、どうしてもエラーになってしまい作業が進みません。

内容は、例えばシート1の9W11という文字をシート2の指定した範囲から探すという作業なのですが、シート1の9W11はセルをダブルクリックすると『'9W11』となり、、シート2の9W11はダブルクリックしても『9W11』で、一致していないことが原因だと思います。
ところが、シート1の『'』を削除したり、逆にシート2に『'』を付けたりして双方一致させてもエラーになるのですが、シート1の9W11を手入力で入力し直すと、結果は同じ『'9W11』なのですが、VLOOKUPが機能します。（元々シート1の9W11は、ダウンロードして引っ張ったもの）

シート1を手入力し直せば問題は解決するのですが、あまりに量が多いこと、また何か原因があるはず・・・と思い、質問させていただいた次第です。
いろいろググって、試してみましたが何か解決のヒントになりそうな情報をよろしくお願いいたします。

回答No.3

bunjii · ベストアンサー

>シート1の『'』を削除したり、逆にシート2に『'』を付けたりして双方一致させてもエラーになるのですが、シート1の9W11を手入力で入力し直すと、結果は同じ『'9W11』なのですが、VLOOKUPが機能します。
先頭の`（シングルクォーティション）は文字列であることを宣言しているもので比較データに含みませんので修正しても無意味です。
「手入力し直すと」見た目では同じですが見えない文字や改行コードが無くなるため正常に検索されると思われます。
確認方法としてはシート1の9W11に対して文字列の長さをチェックしてみると見えない文字（空白）や改行コードの有無が分かります。
=LEN(A1)　→　5以上のとき不要な文字コードが付着している。
除去は次の数式で良いでしょう。
固定長のとき　LEFT(A1,4)
可変長のとき　CLEAN(TRIM(A1))
TRIM関数（余分な空白を除去）とCLEAN関数（制御コードの除去）は何方が先に処理しても同じ結果です。

お礼

ありがとうございました！！
バッチリできて、作業効率を格段にアップできました！！
感謝申し上げます！

回答No.2

chie65535

＞シート1の9W11はセルをダブルクリックすると『'9W11』となり、、シート2の9W11はダブルクリックしても『9W11』で、一致していないことが原因だと思います。

違います。エクセルは、「'」の有無を区別しません。

違う物として検索できないとしたら「どちらかのデータの末尾に、全角スペースや半角スペースなど、見えない文字が付着しているから」です。

不要な空白を削るのはTRIM関数で可能なので、検索される値のすべてをTRIM関数で加工するか、検索する値をTRIM関数で加工しましょう。

お礼

ご回答、ありがとうございました！！
問題解決いたしました！

回答No.1

mt2008

どこからかダウンロードしたデータとの事なので、変なスペースがついていたり、Excelでは表示できないコントローコードが紛れ込んでいる様に思えます。

例えば、A1の値を検索値にVlookupする場合、
=VLOOKUP(A1,E:F,2,FALSE)　＃式は適当です

の、様にするのではなく、↓こんな風にA1の値から余計なスペースやコントロールコードを取り去ったものを検索値としては如何でしょう。
=VLOOKUP(CLEAN(TRIM(A1)),E:F,2,FALSE)