【Excel】右から左へ検索するINDEX・MATCHの使い方と範囲ずれの直し方

商品コードがB列、商品名がA列にある表で、コードから左側の商品名を取り出したいなら、INDEXとMATCHを組み合わせます。VLOOKUPの表を無理に並べ替えず、検索する列と返す列を別々に指定できます。

式の仕組みは、MATCHで「検索範囲の何番目か」を求め、INDEXで「返す範囲の同じ番目」を取り出すというものです。2つの範囲の開始行と終了行をそろえることが、正しい結果を得るための要点です。

右のコードを探し、左の商品名を返す例

A1を「商品名」、B1を「商品コード」とし、次の表を作ります。検索したいコードはE2へ入力し、結果はF2へ表示します。E1を「検索コード」、F1を「商品名」としておけば、元表と検索欄を見分けられます。

VLOOKUPは指定した表の先頭列を検索し、そこから右側の列を返します。この配置のままB列を検索してA列を返す用途には、INDEXとMATCHが適しています。以下の式はExcel 2016・2019を含むデスクトップ版でも使える組み合わせです。

練習ではコードを文字列のID_100などにそろえています。実データへ応用する前に、商品コードに重複がないこと、名前とコードが同じ行に対応していることを確認してください。

MATCHの結果はシートの行番号ではない

E2へ「ID_102」と入力し、確認用のG2へ=MATCH(E2,$B$2:$B$5,0)を入れます。結果は3です。ID_102がワークシートの4行目にあっても、検索範囲B2:B5の先頭から数えると3番目だからです。

第3引数の0は完全一致を指定します。省略すると1となり、昇順に並んだ範囲で近似一致を行う条件になるため、商品コード検索では省略しないでください。完全一致なら、この例のコード列を昇順へ並べ替えておく必要はありません。

MATCHが返すのは商品名でもセル番地でもなく、範囲内の位置です。式がうまく動かない場合、まずこの部分だけを別セルで確かめると、検索に失敗しているのか、値を取り出す範囲が違うのかを分けて調べられます。

INDEXに検索位置を渡して商品名を取り出す

F2には=INDEX($A$2:$A$5,MATCH(E2,$B$2:$B$5,0))を入力します。内側のMATCHが3を返し、INDEXがA2:A5の3番目にある「消しゴム」を取り出します。A列とB列が隣接している必要はありませんが、同じ位置が同じ商品の行を指す必要があります。

動作を分けて確かめるなら、=INDEX($A$2:$A$5,3)だけを試します。これで消しゴムが出るのに組み合わせた式で出ない場合は、検索値やMATCHの範囲を見直します。個々の式を確認してから組み合わせると、長い式を一度に直すより原因を探しやすくなります。

E3以降にもコードを並べてF2の式を下へコピーする場合、E2はE3、E4と変わり、$A$2:$A$5と$B$2:$B$5は固定されます。結果欄を何行にも増やす前に、先頭と末尾のコードで正しい名前が出るか確認してください。

参照範囲は行の位置も長さもそろえる

返す範囲をA3:A6、検索範囲をB2:B5にすると、両方4セルでも対応が1行ずれます。MATCHの3に対してA3:A6の3番目、つまりA5を返すため、消しゴムのはずが定規になる例です。エラーにならず間違った値を返す点に注意します。

見出しを含める場合も、片方だけ1行目から指定しないようにします。通常は両方とも明細の開始行から最終行までにそろえると、式を読む人にも意図が伝わります。件数が増えたら、返す範囲と検索範囲を同時に広げます。

行や列の挿入時にはExcelが参照を調整する場面がありますが、どんな表の変更でも壊れないわけではありません。参照先の削除、片側の列だけの並べ替え、片方の範囲だけの拡張などで対応は失われます。表の構造を変えた後は、既知のコードをいくつか検索して確認します。

#N/Aでは存在とデータ型を順番に確認する

#N/Aが出たら、まずE2のコードがB2:B5に存在するかを調べます。範囲の外へ追加された商品は見つかりません。次に、先頭・末尾の空白、改行、コードの全角・半角など、見えにくい違いを確認します。

数字だけのコードでは、片方が数値の123、もう片方が文字列の「123」になっている場合があります。ISNUMBERやISTEXTで型を調べ、元データのルールに合わせます。コードが00123なら、先頭ゼロに意味があるため、安易に両方を数値へ変換しないでください。

存在しない場合の表示を整えるなら、確認後に=IFNA(INDEX($A$2:$A$5,MATCH(E2,$B$2:$B$5,0)),"未登録")とできます。ただし、未登録という表示の中には、表記違いによる検索失敗も含まれます。参照が壊れた#REF!などまで一律に隠すのではなく、原因を調べられる状態を残します。

重複コードと完全一致の例外に注意する

MATCHの完全一致は、一致する値が複数あれば最初の位置を返します。ID_102が2行にあり、別の商品名が付いている場合、正しいほうを選んでくれるわけではありません。コードを一意にするか、版や店舗などの条件を追加する必要があります。

また、MATCHは英字の大文字・小文字を区別しません。ID_102とid_102を別コードとして運用しているなら、この式をそのまま採用せず、区別する照合方法を検討します。

検索値に「*」「?」がある場合は、完全一致の指定でもワイルドカードとして扱われます。文字そのものを探すには、直前にチルダ「~」を付けるなどの対応が必要です。通常の英数字コードの例が動いたからといって、特殊記号を含むコードでも同じ条件とは限りません。実際に使う文字の種類を確認してください。

XLOOKUPが使える環境では式を短くできる

Microsoft 365やExcel 2021・2024などでは、=XLOOKUP(E2,$B$2:$B$5,$A$2:$A$5,"未登録")でも左側の値を取り出せます。検索値、検索範囲、返す範囲を順に指定でき、既定は完全一致です。

Excel 2016・2019ではXLOOKUPを利用できないため、配布先のバージョンが混在している場合はINDEXとMATCHを残す判断もあります。自分のパソコンで動くかだけでなく、実際にファイルを使う人の環境を確認します。

どちらの関数でも、表の行対応や重複コードの問題が消えるわけではありません。式を短くする前に、検索キーと元表が正しく整っていることを確認すると、更新のたびに結果が変わる原因を減らせます。

結果を確かめてから既存の検索表へ組み込む

完成したら、ID_100でノート、ID_102で消しゴム、ID_103で定規が出ることを確認します。存在しないID_999も試し、#N/Aまたは設定した未登録表示になるか確かめます。真ん中の1件だけでなく、両端と不一致の例を使うと参照範囲のずれを見つけやすくなります。

既存のVLOOKUPを置き換える場合は、最初に別列へ新しい式を入れ、旧結果と見比べます。誤りがあればCtrl+Zで戻すか、保存したコピーから復元します。複数の検索式を一括で書き換える前に、数行で一致条件を確定させてください。

検索結果を値として保存する必要があるなら、元式を残したうえで別シートへ値貼り付けします。表が更新されれば数式の結果も変わるため、確定した時点の一覧と、日々更新する検索表を分けると扱いやすくなります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】