VLOOKUP 関数 作成ツール

検索条件を一度指定するだけで、Excel や Google スプレッドシート用の正しい VLOOKUP が得られます。IFERROR、XLOOKUP、INDEX MATCH 版とライブテストも付き、すべてブラウザ内で動作します。

数式の作成とライブテストは、すべてブラウザ内で動作します。入力した検索値・範囲・サンプルのセルがアップロードされることはなく、ArrayKit に何も送信されません。

開発・運営：Udit Agarwal

VLOOKUP, INDEX MATCH and XLOOKUP, Explained

INDEX MATCH 関数 作成ツールを開く

VLOOKUP 関数 作成 について

うまく動かない VLOOKUP の原因は、決まって同じです。範囲が固定されていない、列番号が 1 つずれている、並べ替えていないデータに近似一致を使っている、数値と文字列を比較している、のいずれかです。この作成ツールは、検索値・表の範囲・返す列を尋ね、引数の順序と引用符が正しい数式を書き出します。列番号の代わりに列の英字を入力すると、表の開始位置からの相対位置に変換されます。IFERROR や IFNA、TRIM、文字列と数値の変換、ワイルドカードはワンクリックで追加でき、引数の区切りは欧州ロケール向けにセミコロンへ切り替えられます。数式の下には編集可能な小さなシートがあり、実際の VLOOKUP の規則で評価して、#N/A も正直に表示します。計算はすべてお使いのデバイス上で行われ、セルも数式もアップロードされません。

機能

  • 引数の順序、引用符、文字列内の二重引用符を正しく処理した VLOOKUP を出力
  • 返す列を番号または英字で指定でき、表の先頭列からの相対位置に変換
  • ワンクリックの $ 固定、列全体の範囲、2 行目から 100 行目までの範囲指定
  • 独自の「見つかりません」テキストを指定できる IFERROR または IFNA のラップ
  • 乱れた検索値向けの、TRIM、文字列を数値に、数値を文字列に、ワイルドカードのオプション
  • 同等の XLOOKUP、INDEX MATCH、FILTER、QUERY の数式を、それぞれコピーボタン付きで提示
  • あらゆるロケールの Excel とスプレッドシート向けに、カンマまたはセミコロンの区切り
  • 完全一致・近似一致・ワイルドカード・未整列データの動作を試せるライブテストの表と、#N/A のチェックリスト

VLOOKUP 関数 作成 の使い方

  1. 検索値を F2 のようなセルで入力するか、探したい文字列や数値を入力する
  2. 検索する列を先頭にして表の範囲を入力し、ドル記号ボタンで固定する
  3. 返す列を番号または英字で選び、完全一致か近似一致かを選ぶ
  4. 必要に応じて IFERROR、TRIM、区切り文字の変更を加え、数式をコピーする
  5. サンプルシートで結果を確認し、#N/A が出る場合はチェックリストを読む

例

入力

Lookup value:  F2  (the cell contains 103)
Table range:   $A$2:$D$100
Return column: 3  (Price)
Match type:    Exact
If not found:  IFERROR with "Not found"

出力

=IFERROR(VLOOKUP(F2,$A$2:$D$100,3,FALSE),"Not found")

XLOOKUP:      =IFERROR(XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100),"Not found")
INDEX MATCH:  =IFERROR(INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)),"Not found")
Live test on the sample table: 103 returns 4.00

同じ検索を 3 通りで記述し、ライブテストでは cherry の行が強調表示されます。

よくあるエラーとトラブルシューティング

  • 値が表に見えているのに、VLOOKUP が #N/A を返す。 — 2 つの値の型が違うか、隠れた空白がある可能性があります。文字列として保存された数値は本物の数値とは決して等しくならず、末尾の空白があると別の値になります。TRIM オプションをオンにするか、数値と文字列のオプションで片側を変換してください。
  • 数式が 1 行目では動くのに、下の行では失敗する。 — 表の範囲が固定されておらず、数式を下にコピーするにつれてずれてしまいました。「$ 固定」ボタンを押して範囲を $A$2:$D$100 の形にし、もう一度下にコピーしてください。
  • 並べ替えていない表で、VLOOKUP が違う行を返す。 — 近似一致は、先頭列が昇順に並んでいることを前提に二分探索を行います。ID・名前・コードには完全一致に切り替え、近似一致は税率区分のような範囲にだけ使ってください。
  • 表の範囲を変更したあと、Excel が #REF! を報告する。 — 列番号が範囲内の列数より大きくなっています。作成ツールはこれを確認し、数式をコピーする前に表の幅を知らせます。

よくある質問

VLOOKUP 関数 作成ツールは、列の英字をどのように列番号に変換しますか？
入力された英字から、表の範囲の先頭列を引いて 1 を足します。表が B 列から始まる場合、返す列 D は番号 3 になります。英字が表より左にある場合は、VLOOKUP は左を検索できないことを説明し、INDEX MATCH を提案します。
VLOOKUP 関数 作成ツールのライブテストでは、実際に何を評価していますか？
ページ上の小さなシートに対して、スプレッドシートと同じ規則で数式のオプションを実行します。完全一致は大文字小文字を区別せずワイルドカードに対応し、近似一致は並べ替え済みのデータを前提とした二分探索で、空の戻りセルは 0 として返り、型が一致しなければ #N/A になります。
VLOOKUP を包むには、IFERROR と IFNA のどちらを選ぶべきですか？
IFNA は「見つからない」エラーだけを隠すので、列番号の誤りや壊れた範囲はそのまま表示されます。IFERROR はすべてのエラーを隠すため、レポートには便利ですが、ミスを見えなくすることがあります。作成ツールは両方を提供し、ライブテストでそれぞれの結果を表示します。
VLOOKUP の近似一致オプションを安全に使えるのはどんなときですか？
先頭列が昇順に並んでいて、検索値を超えない最も近い値がほしいとき、たとえば点数から評価への対応表のような場合だけです。作成ツールは警告を出し、テストの表は未整列のデータを示します。エラーなしに答えが間違うことがあるためです。
VLOOKUP の下に、XLOOKUP と INDEX MATCH の版が表示されるのはなぜですか？
同じ入力に対して同じセルを返しますが、VLOOKUP の制約を避けられます。左方向も検索でき、列を挿入しても壊れません。同僚が使っている Excel やスプレッドシートのバージョンに合うほうをコピーしてください。
VLOOKUP 関数 作成ツールは、表や数式をどこかに送信しますか？
いいえ。数式のテキストとサンプルのセルは、ブラウザ内でのみ処理されます。最後に使った設定とテスト用グリッドのセルはお使いのデバイスのローカルストレージに記憶されるので、ページは前回の状態で開き直されます。

関連ツール

  • INDEX MATCH 関数 作成 — 左方向の検索、2 方向の検索、複数条件の INDEX MATCH を、ライブテストの表とコピーしやすい出力付きで生成します。
  • Excel 数式 作成 — 条件付きの合計、検索、日付、テキスト分割、順位付けなどの作業を選ぶと、そのまま貼れる数式が得られます。
  • Excel 数式 解説 — Excel や Google スプレッドシートの数式を貼り付けると、各関数と引数を平易な言葉で順を追って説明します。
  • Excel データクリーナー — 空白の除去、大文字小文字の統一、空白行と重複の削除、日付と数値の統一を行い、.xlsx または CSV に出力します。
  • Excel to CSV 変換 — .xlsx や .xls をドロップし、任意のシートをクリーンな CSV に書き出します。
  • CSV to JSON 変換 — CSV や TSV を型推論とネスト対応でクリーンな JSON に変換します。

Excel・スプレッドシートの他のツール

  • INDEX MATCH 関数 作成 — 左方向の検索、2 方向の検索、複数条件の INDEX MATCH を、ライブテストの表とコピーしやすい出力付きで生成します。
  • Excel 数式 解説 — Excel や Google スプレッドシートの数式を貼り付けると、各関数と引数を平易な言葉で順を追って説明します。
  • Excel 数式 作成 — 条件付きの合計、検索、日付、テキスト分割、順位付けなどの作業を選ぶと、そのまま貼れる数式が得られます。
  • Excel 関数 翻訳 — Excel の数式を英語、ドイツ語、フランス語、スペイン語などの間で、正しい区切り文字つきで翻訳します。
  • VBA マクロ 作成 — 繰り返し、空白行の削除、PDF 出力、メール送信などのタスクのライブラリから、すぐ実行できる VBA を生成します。
  • CSV Excel 変換 — CSV・TSV ファイルを、区切り文字の判定、ヘッダーの装飾、列の型、複数シートに対応した .xlsx ブックに変換します。
  • JSON Excel 変換 — JSON の配列やネストしたオブジェクトを、展開した列、配列ごとのシート、型付きのセルを持つ Excel ブックに変換します。
  • Excel データクリーナー — 空白の除去、大文字小文字の統一、空白行と重複の削除、日付と数値の統一を行い、.xlsx または CSV に出力します。

Excel・スプレッドシートのツールをすべて見る (9) · すべてのツール