AIでExcelのINDEX・MATCH式を作る方法

INDEX/MATCHをAIに作らせるときは、いきなり数式を書かせるより「何を探すか」「どの列で探すか」「どの列から返すか」を先に固定した方が失敗しにくくなります。MATCHは検索値が何行目にあるかを返し、INDEXはその位置の値を返します。役割を分けて確認できるのが、この組み合わせの強みです。

この記事では、1列検索の基本形から、重複Key、空欄、前後Space、数値とTextの違い、式コピー、2方向検索まで具体例で整理します。読後には、AIが出したINDEX/MATCH式をそのまま貼るのではなく、参照範囲とExpected Outputを自分で検算できる状態を目指します。

最初に3つのInputを決める

INDEX/MATCHの最小構成は、lookup_value、lookup_array、return_arrayの3つです。

表は横にスクロールできます

Input	例	意味
lookup_value	D2	探したい商品名
lookup_array	A2:A100	商品名を探す列
return_array	B2:B100	単価を返す列

この3つが曖昧なまま「商品名から価格を出して」とAIへ頼むと、違うColumnを参照した式が返っても気づきにくくなります。

基本形はMATCHを先に確認する

D2の商品名をA2:A100から完全一致で探すなら、まずMATCHだけを試します。

=MATCH(D2,A2:A100,0)

D2がA列の何番目にあるかを数値で返せれば、検索側は動いています。ここで#N/AならINDEXを組み合わせる前に、検索値・表記・Data Type・Rangeを確認します。

MATCHの結果をINDEXへ渡す

検索位置が取れたら、同じ行位置をB列へ渡します。

=INDEX(B2:B100,MATCH(D2,A2:A100,0))

たとえばA2=りんご、B2=120、D2=りんごなら、MATCHは検索Range内の1番目を返し、INDEXはB2:B100の1番目、つまり120を返します。

このように内側のMATCHと外側のINDEXを別々にTestすると、どちらで問題が起きているか切り分けやすくなります。

AIへ渡すFormula Contract

AIには数式だけでなく、Columnの意味とExpected Outputまで渡します。

例：

• D2：検索したい商品名

• A2:A100：商品マスタの商品名

• B2:B100：単価

• 完全一致

• 「りんご」なら120が期待値

• 見つからない場合は、まず#N/Aの原因を説明

• IFERRORで隠す前にRoot Causeを確認

この情報があれば、AIの修正式が正しいか人側でも判断できます。

そのまま使えるPrompt

「ExcelでINDEX/MATCH式を作ってください。D2の商品名をA2:A100で完全一致検索し、対応するB2:B100の単価を返したいです。A列とB列は同じ行が同じ商品を表します。D2=りんご、A2=りんご、B2=120なら120が期待値です。式、INDEXとMATCHの役割、参照Range、見つからない場合の切り分け、3つのTest Caseを出してください。IFERRORで最初から隠さないでください。」

AIには「式」と「検証」を同時に出させるのがポイントです。

検索Rangeと返却Rangeの行を揃える

A2:A100で見つけた位置をB5:B103へ渡すと、同じ位置番号でも違うRecordを返します。

INDEX/MATCHでは、検索Rangeと返却Rangeが同じRecord順になっていることを確認します。開始行、終了行、並び替え後の対応関係まで見ます。

重複Keyでは最初の一致だけでよいか確認する

完全一致のMATCHで同じ商品名が複数ある場合、どのRecordを返すかがBusiness Ruleになります。

「りんご」がA列に3件あり、価格が120 / 130 / 140なら、単純なINDEX/MATCHだけで「最新価格」を保証できません。商品ID、適用開始日、店舗など、Keyを追加すべき可能性があります。

重複があるDataで1件だけ返したいなら、「何を基準に1件へ絞るか」を先に決めます。

#N/Aが出たら式を作り直す前にDataを見る

見た目が同じ文字でも、末尾Space、全角/半角、改行、数値とTextの違いで一致しないことがあります。

正常RowとError Rowを1件ずつ並べ、lookup_valueとlookup_arrayの実値を比較します。G911で扱うように、#N/AはErrorを隠すより検索値の存在と型を確認する方が先です。

商品コードは数値化しない方がよい場合がある

「00123」のようなCodeは、数値123へ変換すると先頭0が消えます。IDとして0が意味を持つならTextのまま扱います。

AIに「型を揃えて」と頼むときも、そのColumnが計算用Numberなのか識別用Textなのかを明示します。

コピーする式はAbsolute Referenceを設計する

D2の式を下へコピーするとき、検索値はD2→D3→D4と動かしたい一方、Master Rangeは固定したいことがあります。

その場合の例は =INDEX($B$2:$B$100,MATCH(D2,$A$2:$A$100,0)) です。

固定すべきRangeと動かすCellを分け、1行目と途中行、最終行で参照が正しいか確認します。

架空例：社員IDから部署名を返す

A列=社員ID、B列=氏名、C列=部署、E2=検索したい社員IDとします。部署を返す式は次の形です。

=INDEX(C2:C500,MATCH(E2,A2:A500,0))

Testは少なくとも、存在するID、存在しないID、先頭0を含むIDで行います。返り値が見た目上正しいだけでなく、意図した社員Recordかを確認します。

2方向検索はRowとColumnを別々にMATCHする

表の縦軸と横軸の両方を使って値を返したい場合、INDEXにRow位置とColumn位置を渡します。

たとえばA2:A10が商品、B1:M1が月、B2:M10が売上で、P2の商品・P3の月から売上を返すなら、考え方は次です。

=INDEX(B2:M10,MATCH(P2,A2:A10,0),MATCH(P3,B1:M1,0))

この場合も、Row MATCHとColumn MATCHを別Cellで試してから統合するとDebugしやすくなります。

近似一致は用途が明確なときだけ使う

今回のような商品名・ID検索は完全一致を基本にした方が確認しやすいです。近似一致を使う場合は、どのようなData順序と判定を期待しているかを明示します。

AIが理由なくmatch_typeを変更した場合は、その意図を確認します。検索結果が出たことだけで正しいと判断しません。

Error処理はRoot Cause確認の後に追加する

最終的に「見つからないときは空欄」「未登録と表示」のようなUser-facing処理が必要なことはあります。ただし、最初からIFERRORなどで全Errorを隠すと、範囲ずれやData Type不一致まで見えなくなる可能性があります。

まずMATCH単体とINDEX/MATCHで原因を確認し、正常・未一致・重複のTestを通した後に、想定されたNot Foundだけをどう表示するか決めます。式が見やすくなったことと、正しくなったことは別です。

式の変更前後を1行で記録する

AIに修正してもらった場合は、Original Formula / Cause / New Formula / Expected / Test Resultを残すと、後から「なぜこの式になったか」を追えます。

特に既存Workbookでは、同じ式を大量Copyする前に1セルでChange Logを残しておくと、別担当者がレビューしやすくなります。

XLOOKUPへ置き換えるかは別判断

利用中のExcel環境でXLOOKUPが使える場合、同じReader Jobをより直接書けるケースがあります。ただ、既存WorkbookがINDEX/MATCH前提、互換性を維持したい、既存式の一部だけ直したい、といった事情もあります。

「新しいFunctionだから置き換える」ではなく、既存環境と保守性で判断します。

修正後のTest Matrix

表は横にスクロールできます

Case	Input	Expected
Normal	存在するKey	対応する値
Not Found	存在しないKey	#N/Aまたは設計した処理
Duplicate	同一Key複数	Ruleどおり
Space	前後Space	Data Ruleどおり
Type	Text/Number差	Key設計どおり
Copy	下方向へCopy	Master Range固定

最低でもNormalだけでなくNot FoundとDuplicateを確認します。

AIが出した式を採用する前のChecklist

• lookup_valueは正しいCellか

• lookup_arrayは探したいColumnか

• return_arrayは返したいColumnか

• Rangeの開始/終了行が対応しているか

• 完全一致の意図が反映されているか

• 重複Keyの扱いが決まっているか

• IDのData Typeを壊していないか

• Copy時に固定Rangeがずれないか

• 正常/未一致/重複でTestしたか

AIものさしの判断

INDEX/MATCHをAIで作るときは、完成式を最初に信用するより、MATCHで位置を確認してからINDEXで値を返す順に分解した方が安全です。式の意味が分かれば、#N/Aや参照ずれも自分で切り分けられます。

まず手元の表から正常に答えが分かる1行を選び、lookup_value・lookup_array・return_arrayを3つに分けてください。MATCH単体→INDEX/MATCH→Not Found / Duplicate / Copyの順でTestする。この流れなら、AIを使っても「式は動くが違うRecordを返す」事故を見つけやすくなります。