XLOOKUPが使えない場面とは？INDEX MATCHが役立つ理由
Excelでデータ検索をするとき、最近よく使われるのがXLOOKUP関数です。検索値を指定するだけで、対応するデータを簡単に取り出せる便利な関数で、VLOOKUPよりも柔軟に使える点が評価されています。
しかし、実務では「XLOOKUPを使いたくても使えない」という場面が意外とあります。たとえば、会社で使っているExcelのバージョンが古い場合です。XLOOKUPは比較的新しい関数のため、Excel 2019以前の買い切り版では利用できないケースがあります。自分のPCでは使えても、取引先や上司の環境ではエラーになることもあるため、共有ファイルでは注意が必要です。
また、職場では部署や拠点ごとにExcel環境がバラバラなことも珍しくありません。せっかくXLOOKUPで作った表が、別の人のPCで開いた瞬間に#NAME?エラーになると、確認や修正に余計な時間がかかってしまいます。特に営業資料、在庫管理表、顧客リストなど、複数人で扱うファイルでは「誰でも開けるか」はかなり重要です。
そこで役立つのが、INDEX関数とMATCH関数を組み合わせる方法です。いわゆる「INDEX MATCH」と呼ばれる使い方で、XLOOKUPの代替として長く使われてきた定番テクニックです。
INDEX MATCHの大きなメリットは、古いExcelでも使える互換性の高さです。XLOOKUPが使えない環境でも、INDEX関数とMATCH関数であれば多くのExcelバージョンに対応しています。そのため、社内外で共有するファイルでも安心して使いやすいのが特徴です。
さらに、INDEX MATCHは単なる代替手段にとどまりません。VLOOKUPのように「検索列が一番左にないと使いにくい」といった制限が少なく、左方向への検索にも対応できます。たとえば、社員番号から氏名を探すだけでなく、氏名をもとに左側にある社員番号を取得する、といった処理も可能です。
最初は「関数を2つ組み合わせるなんて難しそう」と感じるかもしれません。しかし、考え方はシンプルです。MATCH関数で探したいデータの位置を調べ、INDEX関数でその位置にある値を取り出すだけです。この役割分担を理解できれば、XLOOKUPに近い感覚で使えるようになります。
つまりINDEX MATCHは、XLOOKUPが使えない職場環境での保険であり、同時に実務力を高める検索スキルでもあります。Excelファイルを誰かと共有する機会が多い20代のビジネスパーソンこそ、覚えておいて損はありません。
まず押さえる：INDEX関数とMATCH関数の基本
INDEX MATCHを使いこなすには、まずINDEX関数とMATCH関数がそれぞれ何をしているのかを理解することが大切です。いきなり組み合わせて覚えようとすると難しく感じますが、1つずつ見るとかなりシンプルです。
INDEX関数は「指定した位置の値を取り出す」関数
INDEX関数は、指定した範囲の中から「何行目・何列目にある値」を取り出す関数です。基本の形は次のとおりです。
=INDEX(範囲, 行番号, 列番号)たとえば、A2:C6の表があり、その中の2行目・3列目の値を取り出したい場合は、次のように書きます。
=INDEX(A2:C6, 2, 3)この場合、A2:C6という範囲の中で、上から2行目、左から3列目にあるデータが表示されます。つまりINDEX関数は、表の中から場所を指定して値を引っ張ってくる関数と考えるとわかりやすいです。
ただし、実務では「2行目を取り出したい」と最初からわかっているケースばかりではありません。多くの場合は、「社員番号がA003の人の部署を知りたい」「商品コードに対応する単価を出したい」といったように、検索した結果として行番号を知りたいはずです。そこで登場するのがMATCH関数です。
MATCH関数は「探した値が何番目にあるか」を調べる関数
MATCH関数は、指定した範囲の中で、検索したい値が何番目にあるかを返す関数です。基本の形は次のとおりです。
=MATCH(検索値, 検索範囲, 照合の種類)たとえば、A2:A6に社員番号が並んでいて、その中から「A003」が何番目にあるかを調べたい場合は、次のように入力します。
=MATCH("A003", A2:A6, 0)
最後の0は「完全一致で探す」という意味です。実務でコードや氏名、商品名などを検索する場合は、基本的にこの0を使うと覚えておけば問題ありません。
もし「A003」がA2:A6の中で3番目にあれば、MATCH関数の結果は3になります。つまりMATCH関数は値そのものを取り出すのではなく、探したデータの位置番号を教えてくれる関数です。
INDEXとMATCHは役割分担で考える
INDEX関数とMATCH関数の関係は、次のように整理できます。
- MATCH関数：検索値が表の中で何番目にあるかを調べる
- INDEX関数：その番号を使って、指定した列の値を取り出す
つまり、MATCH関数で「場所」を見つけて、INDEX関数で「中身」を取り出すイメージです。この考え方がわかれば、次の章で紹介するXLOOKUP代替の基本形もスムーズに理解できます。
XLOOKUPは1つの関数で検索から取得まで行えますが、INDEX MATCHではその処理を2つの関数で分担します。最初は少し長く感じるかもしれませんが、仕組みを理解しておくと、エラーの原因を見つけやすくなり、応用もしやすくなります。
XLOOKUP代替の基本形：INDEX MATCHでデータを検索する方法
ここからは、実際にINDEX MATCHを使ってXLOOKUPの代わりにデータを検索する方法を見ていきましょう。ポイントは、MATCH関数で「検索値が何行目にあるか」を調べ、その結果をINDEX関数に渡して「欲しい列の値」を取り出すことです。
たとえば、次のような社員リストがあるとします。
| 社員番号 | 氏名 | 部署 |
|---|---|---|
| A001 | 佐藤 | 営業部 |
| A002 | 田中 | 経理部 |
| A003 | 鈴木 | 人事部 |
この表で、社員番号「A003」に対応する部署を取り出したい場合、XLOOKUPなら次のように書けます。
=XLOOKUP("A003", A2:A4, C2:C4)これと同じような処理をINDEX MATCHで行う場合は、次の式になります。
=INDEX(C2:C4, MATCH("A003", A2:A4, 0))式だけ見ると少し長く感じるかもしれませんが、分解するとシンプルです。まず、内側のMATCH関数が実行されます。
=MATCH("A003", A2:A4, 0)
これは、A2:A4の中から「A003」が何番目にあるかを探しています。「A003」は3番目にあるため、MATCH関数の結果は3になります。
すると、全体の式は次のように考えられます。
=INDEX(C2:C4, 3)つまり、C2:C4の範囲から3番目の値を取り出す、という意味です。C2:C4の3番目は「人事部」なので、結果として「人事部」が表示されます。
セル参照を使うと実務で使いやすい
実際の業務では、検索値を数式に直接入力するよりも、セルを参照する形にした方が便利です。たとえば、E2セルに社員番号を入力し、F2セルに部署を表示したい場合は、次のようにします。
=INDEX(C2:C4, MATCH(E2, A2:A4, 0))この形にしておけば、E2セルの社員番号を「A001」「A002」「A003」と変更するだけで、対応する部署が自動で切り替わります。検索用の入力欄を作るようなイメージです。
INDEX MATCHの基本形は、次のように覚えておくとわかりやすいです。
=INDEX(取り出したい列, MATCH(検索値, 検索する列, 0))「取り出したい列」と「検索する列」を分けて指定できるのが、INDEX MATCHの大きな特徴です。XLOOKUPと比べると見た目は少し複雑ですが、構造を理解すれば、どの範囲を検索して、どの範囲から取り出しているのかが明確になります。
まずはこの基本形をそのまま使えるようになることが大切です。社員番号から部署を出す、商品コードから単価を出す、顧客IDから担当者名を出すなど、実務でよくある検索作業の多くは、この形だけで対応できます。
実務で使える応用テクニック：左方向検索・複数条件検索への対応
INDEX MATCHを覚えるメリットは、XLOOKUPの基本的な代替だけではありません。実務で特に役立つのが、左方向検索と複数条件検索に対応しやすい点です。ここを押さえておくと、単なる検索関数ではなく、現場で使えるExcelスキルとして一段レベルアップできます。
左方向検索：検索列より左側のデータを取り出す
VLOOKUPでは、基本的に検索する列より右側のデータしか取り出せません。そのため、表の並びによっては列を入れ替えたり、作業用の表を作ったりする必要がありました。
一方、INDEX MATCHでは「検索する列」と「取り出す列」を別々に指定できるため、検索列の左側にあるデータも簡単に取得できます。
たとえば、A列に社員番号、B列に氏名、C列に部署がある表で、「氏名」から左側にある「社員番号」を取り出したい場合は、次のように書きます。
=INDEX(A2:A10, MATCH(E2, B2:B10, 0))
この式では、MATCH(E2, B2:B10, 0)でB列の氏名から検索値を探し、その位置に対応するA列の社員番号をINDEXで取り出しています。
ポイントは、INDEXの範囲に「取り出したい列」、MATCHの範囲に「検索したい列」を指定することです。列の左右関係に縛られないため、表のレイアウトを無理に変更せずに済みます。
複数条件検索：部署＋氏名のように条件を組み合わせる
実務では、検索条件が1つだけでは足りないケースもあります。たとえば、「田中さん」を検索したい場合でも、営業部にも経理部にも田中さんがいるかもしれません。このようなときは、部署と氏名の2条件で検索する方法が便利です。
たとえば、A列に社員番号、B列に氏名、C列に部署があり、E2セルに氏名、F2セルに部署を入力して社員番号を取り出す場合は、次のように書けます。
=INDEX(A2:A10, MATCH(1, (B2:B10=E2)*(C2:C10=F2), 0))
この式では、(B2:B10=E2)で氏名が一致する行を判定し、(C2:C10=F2)で部署が一致する行を判定しています。両方の条件を満たす行だけが1となり、MATCH関数がその位置を見つける仕組みです。
ただし、古いExcelではこのような配列計算を使う場合、入力後にCtrl + Shift + Enterで確定する必要があることがあります。通常のEnterでうまく動かない場合は試してみてください。
互換性を重視するなら作業列を使うのもアリ
社内共有ファイルなどで安定性を優先するなら、作業列を使う方法もおすすめです。たとえばD列に「氏名」と「部署」を連結したキーを作ります。
=B2&"_"&C2そのうえで、検索側も同じ形にしてINDEX MATCHを使います。
=INDEX(A2:A10, MATCH(E2&"_"&F2, D2:D10, 0))作業列は見た目こそ少し増えますが、数式がシンプルになり、他の人が見ても理解しやすいのがメリットです。実務では「かっこいい数式」よりも、誰が見ても直しやすい数式の方が評価される場面も多いです。
左方向検索や複数条件検索まで使えるようになると、INDEX MATCHはXLOOKUPの代替以上に頼れる武器になります。表の形を変えずに柔軟に検索できるため、日々の集計やリスト管理でかなり役立つはずです。
XLOOKUPとINDEX MATCHの使い分け｜職場で迷わない判断ポイント
ここまでINDEX MATCHの基本と応用を見てきましたが、実務では「結局、XLOOKUPとINDEX MATCHのどちらを使えばいいの？」と迷う場面もあるはずです。結論から言うと、自分だけで使うファイルならXLOOKUP、共有や互換性を重視するならINDEX MATCHを選ぶのがおすすめです。
自分用・最新環境ならXLOOKUPが便利
Microsoft 365やExcel 2021以降など、XLOOKUPが使える環境で、自分だけが管理するファイルであれば、基本的にはXLOOKUPを使って問題ありません。数式が比較的読みやすく、検索範囲と戻り範囲を指定するだけで済むため、作業スピードを上げやすいからです。
=XLOOKUP(E2, A2:A10, C2:C10)このように、検索値・検索範囲・取り出す範囲の流れが直感的なので、後から見返したときにも理解しやすいのがメリットです。短時間で資料を作りたいときや、自分だけで完結する分析作業ではXLOOKUPが強力です。
社内外で共有するならINDEX MATCHが安全
一方で、上司・同僚・取引先など複数人が開く可能性のあるファイルでは、INDEX MATCHを選ぶと安心です。特に、相手のExcelバージョンがわからない場合、XLOOKUPを使うと#NAME?エラーになるリスクがあります。
営業管理表、請求データ、在庫リスト、顧客台帳など、業務で長く使われるファイルほど互換性は重要です。「自分のPCでは動くけど、相手のPCでは動かない」という状態は、確認作業や問い合わせ対応の手間につながります。
その点、INDEX関数とMATCH関数は古いExcelでも使えるケースが多いため、共有前提のファイルでは安定した選択肢になります。
判断に迷ったら「誰が使うか」で決める
使い分けに迷ったときは、次のように考えると判断しやすくなります。
- 自分だけが使う：XLOOKUPで効率重視
- チームで共有する：INDEX MATCHで互換性重視
- 相手のExcel環境が不明：INDEX MATCHを選ぶ
- 数式の見やすさを優先：XLOOKUPを検討
- 古いExcelでも動かしたい：INDEX MATCHが無難
また、職場では「自分が作ったファイルを、別の人が修正する」こともよくあります。そのため、数式を短くすることだけでなく、後任者やチームメンバーが理解できるかも大切な判断基準です。
XLOOKUPは新しく便利な関数、INDEX MATCHは互換性と応用力に優れた定番テクニックです。どちらか一方だけを覚えるのではなく、状況に応じて使い分けられるようになると、Excel実務でかなり頼られる存在になれます。
コメント