今回は、ExcelのXMATCHで位置検索を使う方法を紹介します。
XMATCHは値の位置を探す関数
Excelで一覧表を扱っていると、特定の名前やコードが何番目にあるのかを調べたい場面があります。該当する行番号を知りたい、別の関数と組み合わせて値を取り出したい、検索対象がどこにあるか確認したい、といった場面です。
XMATCHは、検索した値が範囲内の何番目にあるかを返す関数です。値そのものを返すのではなく、位置を返す点が特徴です。位置を調べてから別の処理につなげると、表の検索や参照を組み立てやすくなります。
似た関数にMATCHがありますが、XMATCHは検索方法や一致の扱いを指定しやすく、縦方向にも横方向にも使えます。新しく表を作るなら、XMATCHを使う選択肢を持っておくと便利です。
基本の使い方
XMATCHでは、探したい値と検索範囲を指定します。たとえばA列に社員番号が並んでいて、指定した社員番号がA2からA20の中で何番目にあるかを調べるような使い方です。
返るのはシートの行番号ではなく範囲内の位置
XMATCHで返る数値は、検索範囲の中での順番です。A2:A20を検索範囲にして、A2に一致すれば1、A3に一致すれば2になります。シート上の行番号とは違うため、ここを混同しないようにします。
この性質は、INDEX関数と組み合わせると役立ちます。XMATCHで位置を調べ、その位置をINDEXに渡して別の列の値を取り出せます。検索と取得を分けて考えると、式の意味が理解しやすくなります。
完全一致を基本にする
コード、氏名、商品名などを検索する場合は、完全一致を基本にします。近い値を探す設定にすると、意図しない値が見つかることがあります。特にIDや管理番号では、似た値を返されると確認ミスにつながります。
完全一致を指定しておけば、探した値がない場合はエラーになります。エラーは悪いものではなく、該当データが見つからないことを知らせる合図として使えます。
縦方向と横方向の検索に使える
XMATCHは、縦に並んだ一覧だけでなく、横に並んだ見出しの検索にも使えます。たとえば列見出しから「売上」や「担当者」の位置を探し、その列を別の関数で取り出すような使い方です。
列見出しの位置を探す
表の列順が変わる可能性がある場合、列番号を固定して式を作ると崩れやすくなります。XMATCHで見出し名の位置を探せば、列の順番が変わっても目的の列を参照しやすくなります。
たとえば1行目に見出しがあり、その中から「金額」の位置を探します。その位置を使ってINDEXやCHOOSECOLSなどと組み合わせると、列番号を直接書かずに処理できます。
行見出しの位置を探す
横方向に月や区分が並んでいる表では、行見出しや列見出しを検索して位置を取得できます。縦横の位置をそれぞれXMATCHで求め、INDEXで交差するセルの値を取り出す形にすると、表の中から目的の値を参照できます。
この方法は、価格表、担当表、予定表などで使いやすい考え方です。見出し名を条件セルにしておけば、条件を変えるだけで参照先を切り替えられます。
検索モードを使い分ける
XMATCHでは、検索の方向を指定できます。通常は先頭から検索しますが、最後から検索することもできます。履歴データや更新記録を扱うときに役立ちます。
最新の該当行を探す
同じ顧客名や商品コードが複数回出てくる表では、最後に出てきた行を探したいことがあります。検索方向を最後からにすると、末尾側の一致を取得できます。
たとえば、対応履歴の中から特定顧客の直近の記録を探したい場合に使えます。表の上から探すと最初の記録が返りますが、下から探せば新しい記録に近い行を見つけやすくなります。
近似一致は用途を限定する
XMATCHでは、完全一致だけでなく近い値を探す設定もできます。料金表やランク表のように、範囲に応じて該当する値を探したい場合に使えます。
ただし、近似一致は表の並び順や条件の意味を理解して使う必要があります。コード検索や氏名検索では完全一致を使い、近似一致は段階表やしきい値を扱う場面に限定すると、誤った結果を避けやすくなります。
見つからないときの扱い
XMATCHで値が見つからない場合はエラーになります。実務では、エラーをそのまま表示すると読みにくいことがあります。必要に応じてIFNAやIFERRORと組み合わせ、表示を整えます。
未登録と表示する
検索対象に値がない場合、「未登録」や「該当なし」と表示すると、表を見る人が状況を判断しやすくなります。特に入力欄と連動した検索表では、エラー記号だけでは意味が伝わりにくいことがあります。
ただし、すべてのエラーを隠すと原因が分かりにくくなる場合もあります。検索値が未入力なのか、表に存在しないのか、範囲指定が間違っているのかを分けたい場合は、条件を分けて表示を作ります。
余分な空白を確認する
見つかるはずの値が見つからないときは、余分な空白や表記揺れを確認します。見た目が同じでも、末尾にスペースがあると別の文字列として扱われます。
外部データを取り込んだ表では、全角スペース、半角スペース、改行が混ざることがあります。検索式を直す前に、元データをフィルターで確認すると原因を見つけやすくなります。
他の関数と組み合わせる
XMATCHは単体でも使えますが、他の関数と組み合わせることで力を発揮します。位置を返す関数なので、その位置を利用して値を取り出したり、列を選んだりできます。
INDEXと組み合わせる
INDEXは、指定した範囲の中から行番号や列番号に応じた値を返します。XMATCHで行の位置を探し、INDEXで別列の値を返すと、検索表を作れます。
この組み合わせは、検索列と取得列が離れている場合にも使いやすいです。VLOOKUPのように検索列が左端にある必要がないため、表の形に合わせて柔軟に使えます。
XLOOKUPと使い分ける
単純に検索して値を返すだけなら、XLOOKUPのほうが式を短くできます。XMATCHは、位置を使って別の処理をしたいときに向いています。
たとえば、見出し位置を取得して列を選びたい、行と列の交差位置を求めたい、最後に一致する位置だけを知りたい場合はXMATCHが使いやすくなります。目的に合わせて選ぶと、式が読みやすくなります。
まとめ
ExcelのXMATCHは、検索した値が範囲内の何番目にあるかを調べる関数です。シートの行番号ではなく、指定範囲内の位置を返す点を理解すると、INDEXなどとの組み合わせが使いやすくなります。
完全一致を基本にし、必要に応じて検索方向や近似一致を使い分けます。見つからない場合の表示や、余分な空白の確認も含めて設計しておくと、位置検索を使った表を安定して運用できます。