ExcelのVLOOKUP関数であいまい検索をする方法をお探しですね。

広告

ExcelのVLOOKUPで「あいまい検索」する方法を分かりやすく解説

Excelで商品名や住所、氏名などを検索するとき、「完全に一致しなくても、一部の文字が含まれていればヒットしてほしい」と思ったことはありませんか？通常のVLOOKUPは完全一致で使うことが多いので、そのままだと#N/Aエラーが出てしまうことがあります。

この記事では、VLOOKUPで「部分一致」や「あいまい検索」をする方法を、初心者の方にも分かりやすく解説していきます。

1. VLOOKUPの「部分一致」って何？

まず、VLOOKUP関数のおさらいです。

VLOOKUPは、表の左端の列から検索したい値を探して、同じ行にある別の列のデータを取り出してくれる便利な関数です。

基本の書き方はこんな感じです。

`=VLOOKUP(検索値, 範囲, 列番号, 検索方法)`

普段よく使うのは、検索方法のところに「FALSE」を指定して、ピッタリ一致するデータだけを探す使い方ですよね。

例えば商品コード「A001」と完全に一致する行を見つけて、その価格を表示する、みたいな感じです。

では「部分一致」とは何でしょうか？これは、検索したい文字列の一部が含まれていればOKという、ゆるめの検索方法です。

例えば、こんな商品名の一覧があるとします。

– 筆箱（ブラック）
– ノート ブラック
– ブラック替芯

この中から「ブラック」という文字を含む商品の価格を取り出したい場合に使えます。

完全一致だと「ブラック」という商品名そのものしか見つけられませんが、部分一致なら「ブラック」という文字が含まれていればすべて検索対象になります。

商品名や住所、部署名付きの氏名など、表記にバラつきがあるデータを扱うときにとても便利な方法です。

**ちょっと注意！**

VLOOKUPの第4引数（検索方法）に「TRUE」を指定する「近似一致」と、今回説明する「部分一致」は別物です。

TRUEは主に数値データで「近い値」を探すときに使うもので、「文字列の一部を含むものを探す」という意味ではありません。

文字列の一部を含む検索をしたいときは、「ワイルドカード」というものを使って、検索方法は基本的に「FALSE」を指定します。

2. ワイルドカード「*」と「?」の使い方

VLOOKUPで部分一致検索をするには、「ワイルドカード」という特殊な記号を使います。

ワイルドカードは「何でもいい文字」を表す記号で、Excelでは主に「*（アスタリスク）」と「?（クエスチョンマーク）」の2つを使います。

記号の意味

| 記号 | 意味 | 使用例 |
|—|—|—|
| * | 何文字でもOK（0文字でもOK） | “*東京*” → 「東京都」「新宿区東京支店」「東京営業所」など |
| ? | 何でもいいけど1文字だけ | “?田” → 「山田」「佐田」など（「佐々木」は×） |
| ~ | *や?の記号そのものを検索したいとき | “~*” → アスタリスク記号を検索 |

「*」は文字数を問わないので、よく使います。

「?」は文字数を限定したいときに便利です。

実際に使ってみよう

例えば、A列に商品名、B列に価格が入っている表があるとします。

「ブラック」という文字を含む商品の価格を取得したいときは、こう書きます。

`=VLOOKUP(“*ブラック*”,A2:B10,2,FALSE)`

この式では、検索値の前後に「*」を付けています。

これで「ブラック」の前後にどんな文字があってもヒットするようになります。

– 「筆箱ブラック」→ ○
– 「ブラックボールペン」→ ○
– 「高級ブラックノート」→ ○

全部ヒットします！

「?」の使い方

「?」は1文字だけを表すので、文字数を限定したいときに使います。

例えば「?木」と指定すると、

– 「鈴木」→ ○（前に1文字）
– 「青木」→ ○（前に1文字）
– 「佐々木」→ ×（前に2文字）

こんな感じで、文字数の条件も指定できます。

**使い分けのコツ：** 幅広く探したいなら「*」、文字数まで絞り込みたいなら「?」と覚えておきましょう。

3. セルに入力した言葉で検索する方法

実際の仕事では、数式の中に直接「ブラック」とか「東京」と書くより、別のセルに検索したい言葉を入力して使う方が便利です。

例えば、D2セルに検索キーワードを入力して、A2:B10の表から該当する価格を返したい場合は、こう書きます。

`=VLOOKUP(“*”&D2&”*”,A2:B10,2,FALSE)`

ポイント

「*」だけをダブルクォーテーション（”）で囲んで、セル番地のD2は囲みません。

「&」でつなぎます。

もし「”*D2*”」と書いてしまうと、Excelは「D2」という文字そのものを探してしまうので注意してください。

前方一致・後方一致の使い方

部分一致には、いくつかパターンがあります。

**前方一致**：指定した文字列で「始まる」ものを探す
– 書き方：`D2&”*”`
– 例：「絵の具」で始まる商品を探す → 「絵の具*」

**後方一致**：指定した文字列で「終わる」ものを探す
– 書き方：`”*”&D2`
– 例：「ブラック」で終わる商品を探す → 「*ブラック」

**含む検索**：どこかに含まれていればOK
– 書き方：`”*”&D2&”*”`
– 例：「ブラック」を含む商品を探す → 「*ブラック*」

実例：住所から氏名を探す

住所一覧から「大阪市」を含む人の氏名を取り出したい場合、住所がA列、氏名がB列、検索語がD2セルにあるなら、こう書きます。

`=VLOOKUP(“*”&D2&”*”,A2:B100,2,FALSE)`

D2セルに「大阪市」と入力すれば、「大阪市北区」「大阪市中央区」など、大阪市を含む住所の人の氏名が取り出せます。

部署名付きの氏名から検索する場合も同じです。

「営業部 山田太郎」「経理部 山田太郎」のようなデータに対して、検索語に「山田太郎」だけを入れれば、部署名の部分は「*」が吸収してくれるので、ちゃんと検索できます。

4. よくある失敗と対策

VLOOKUPの部分一致検索でつまずきやすいポイントをまとめました。

失敗① 検索方法に「TRUE」を指定してしまう

「あいまい検索」という言葉から、第4引数を「TRUE」にしてしまう人がいますが、これは間違いです。

TRUEは主に数値の「近似一致」を探すもので、文字列の部分一致検索とは別物です。

文字列の一部を含むデータを探す場合は、基本的に「FALSE」を指定します。

省略すると自動的にTRUEになってしまうので、必ず「FALSE」と書きましょう。

失敗② 複数の該当データがあるとき

VLOOKUPは、一致するデータが複数あっても、最初に見つかった1件しか返してくれません。

例えば「ブラック」を含む商品が5つある表で「*ブラック*」と検索すると、一番上にある商品の価格だけが表示されます。

これはVLOOKUPの仕様です。

複数の該当データを全部表示したい場合は、VLOOKUP以外の方法を使う必要があります。

– FILTER関数（新しいExcelで使える）
– オートフィルター
– Power Query

などを検討してみてください。

失敗③ 全角・半角や余分なスペースがある

見た目は同じでも、内部的に違っている場合があります。

– 全角と半角が混ざっている
– 名前の前後に余分なスペースが入っている
– 改行が含まれている
– 表記ゆれ（「丁目」と「－」など）

特に外部システムから取り込んだデータは、こういった問題が起こりやすいです。

**対策：**
– TRIM関数で余分なスペースを削除する
– CLEAN関数で不要な制御文字を取り除く
– 検索語の表記を統一する

検索する前に、データをきれいに整えることも大切です。

失敗④ 検索したい列が左側にない

VLOOKUPには「検索する列は指定範囲の一番左でないといけない」という制約があります。

例えば、B列で検索してA列の値を返したい、みたいなことはVLOOKUPだけでは難しいです。

**解決方法：**

Microsoft 365やExcel 2021以降を使っている方は、「XLOOKUP関数」が使えます。

XLOOKUPなら検索範囲と戻り範囲を別々に指定できて、ワイルドカード検索も簡単です。

`=XLOOKUP(“*”&D2&”*”,A2:A10,B2:B10,”見つかりません”,2)`

一致モードに「2」を指定すると、ワイルドカード検索ができます。

まとめ：まずは基本パターンを覚えよう

VLOOKUPで部分一致・あいまい検索をする方法は、検索値に「*」や「?」を組み合わせるだけなので、基本を押さえれば難しくありません。

**覚えておきたい基本パターン：**

– **含む検索**：`”*”&セル&”*”`
– **前方一致**：`セル&”*”`
– **後方一致**：`”*”&セル`

そして、これらのポイントも頭に入れておきましょう。

✓ 複数一致しても、返ってくるのは最初の1件だけ
✓ 「TRUE」は文字列の部分一致検索ではない
✓ データの表記ゆれや余分なスペースに注意
✓ 検索方法は基本「FALSE」を指定する

この記事の内容を実際に試してみて、少しずつ慣れていってください。

部分一致検索ができるようになると、Excelでのデータ検索がグッと楽になりますよ！

広告