VLOOKUP・XLOOKUP・INDEX/MATCHの選び方と、AIへの頼み方の違い

まず環境で選ぶ:選択表

Microsoftの公式ページでは、XLOOKUPはExcel 2016とExcel 2019では使えず、Microsoft 365、Excel 2021、Excel 2024で使えると案内されています。一方、VLOOKUPは2016から2024、Microsoft 365までが対象です。INDEXとMATCHも、公式ページの対象にExcel 2016以降が並んでいます。下の表は、この記載をもとにした編集上の目安です。

• 自分も相手もMicrosoft 365 / 2021 / 2024、選ぶ関数：XLOOKUP、理由：検索列と戻り値列を別々に指定でき、見つからない時の表示も式の中で決められる
• 相手にExcel 2016・2019がいる、選ぶ関数：VLOOKUPまたはINDEX/MATCH、理由：XLOOKUPは公式に非対応の版がある
• 相手の版が分からない、選ぶ関数：INDEX/MATCH(表が単純ならVLOOKUP)、理由：XLOOKUPを避け、2016から使える関数に寄せる
• 検索キーが戻したい列より右にある、選ぶ関数：XLOOKUP、版の都合で使えなければINDEX/MATCH、理由：VLOOKUPは検索値が範囲の最初の列にある必要がある
• 列の挿入・削除が頻繁にある、選ぶ関数：XLOOKUPまたはINDEX/MATCH、理由：VLOOKUPは列番号を数字で渡すため、列が増減するとずれやすい

最後の行の「ずれやすい」は式の作りから考えた編集上の指摘で、公式ページの記述ではありません。

同じ検索を3方式で書く

次の表の前提は、こうです。

• シート「マスタ」:A列=商品コード、B列=商品名、C列=単価、D列=在庫(2〜100行目)
• シート「注文」:A2に商品コードを入力し、単価を引く

ケース1:商品コードから単価を引く(右方向)

• VLOOKUP、説明用の式：=VLOOKUP(A2,マスタ!$A$2:$D$100,3,FALSE)
• XLOOKUP、説明用の式：=XLOOKUP(A2,マスタ!$A$2:$A$100,マスタ!$C$2:$C$100,"該当なし")
• INDEX/MATCH、説明用の式：=INDEX(マスタ!$C$2:$C$100,MATCH(A2,マスタ!$A$2:$A$100,0))

VLOOKUPの最後のFALSEは重要です。公式ページでは、省略すると近似一致が既定になると説明されています。商品コードのような完全一致ではFALSEを付けます。

XLOOKUPは既定が完全一致で、見つからなければ#N/Aを返し、第4引数で代わりの表示を指定できます。INDEX/MATCHでは、MATCHの第3引数が既定で1で、並び順に依存する探し方になります。完全一致で最初の値を探すには0を付けます。

ケース2:商品名から商品コードを引く(左方向)

注文側のE2に商品名があり、マスタのB列で探してA列の商品コードを返す場合です。

• VLOOKUP、説明用の式：検索列が範囲の左端になければならず、戻り値のA列が左にあるため、そのままでは書けない
• XLOOKUP、説明用の式：=XLOOKUP(E2,マスタ!$B$2:$B$100,マスタ!$A$2:$A$100,"該当なし")
• INDEX/MATCH、説明用の式：=INDEX(マスタ!$A$2:$A$100,MATCH(E2,マスタ!$B$2:$B$100,0))

MicrosoftのVLOOKUP・INDEX/MATCHの解説ページも、表がVLOOKUPの向きに作られていないならINDEXとMATCHの組み合わせを使うよう案内しています。同じページにはXLOOKUPとXMATCHを試す案内もあります。

AIへの頼み方は関数ごとに押さえる点が違う

AIに任せるのは式の下書きと各部分の説明までです。どの版で使うかの決定と結果の検算は、人が行います。

共通で渡す情報

• Excelの版(自分と相手の両方。分からなければ「不明」と書く)
• 検索する列と、戻したい列の見出し名・列記号
• 範囲の行数と、見つからない時にどうしたいか
• ダミーの見出しと数行のサンプル(本物の顧客名や社内単価は置き換える)

依頼文の例

Excelの検索式を3通り作ってください。
- 私はMicrosoft 365、共有相手はExcel 2019の可能性があります。
- シート「マスタ」A=商品コード、B=商品名、C=単価(2〜100行目)
- シート「注文」A2の商品コードで単価を引きたい
- 完全一致のみ。見つからない時は「該当なし」と表示
- VLOOKUP、XLOOKUP、INDEX/MATCHの順に式を出し、
  それぞれ古い版で動かない可能性があるかを明記してください
- 範囲は絶対参照($)にしてください

関数別に追記する指示

• VLOOKUP:「完全一致のFALSEを明示」「列番号が何列目か、数え方も説明」と頼みます。列番号の数え違いが典型的なミスです。範囲の列数より大きい番号を指定すると#REF!になる、と公式ページにあります。
• XLOOKUP:「見つからない時の表示を第4引数で指定」と頼みます。版の制約を先に伝えないと、相手の環境を考えないXLOOKUPが返ってきます。
• INDEX/MATCH:「MATCHの第3引数を0にし、INDEXとMATCHの範囲の行数を揃える」と頼みます。開始行がずれると、別の行の値が返ります。

人が確認すること

AIが出した式は、そのまま全行に流さず、次の順で確認します。

1. 答えが分かっている行を3件選び、手で探した値と一致するか見ます。
2. マスタにないコードを1件入れ、「該当なし」や#N/Aなど想定した表示になるか見ます。
3. 数値のコードと文字列のコードが混在していないか見ます。見た目が同じでも一致しないことがあります。
4. 前後に余分なスペースがないか見ます。
5. 式をコピーしても範囲が動かないか($の位置)を見ます。
6. 共有相手の版のExcelで、実際にファイルを開いて計算されるか確かめます。XLOOKUPを使ったファイルを非対応の版で開いたときの表示は、この記事では確認していません。

判断がつかないときは、AIに3方式を出してもらい、小さなサンプル表で3つとも同じ結果になるかを自分で確かめてから採用します。