=INDEX(C2:C6,MATCH(F2,A2:A6,0))は、A2:A6でF2がある行を探し、C2:C6の同じ行の値を返します。MATCHが位置を探し、INDEXがその位置の値を取り出します。どのバージョンのExcelでも使え、左側も検索できます。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | P-101 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
F2をMilkに変えると、G2は$1.10を返します。C2:C6をB2:B6に変えると、代わりにカテゴリーを返します。
INDEXとMATCHの連携のしかた
この数式は、1つのセルの中で2つの手順を行っています。ここではそれぞれを別のセルに分けて、各部分が何を返すかを見られるようにしています。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | P-101 | Position | 4 | |
| 3 | Pear | Fruit | $1.50 | P-102 | Price | $2.40 | |
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
BreadはA2:A6の4番目の項目なので、MATCH(G1,A2:A6,0)は4を返します。INDEX(C2:C6,4)はC2:C6の4番目の項目$2.40を返します。G2の代わりにMATCHをINDEXの中に入れれば、1セルの数式になります。正しく動かすための規則が2つあります：
- 2つの範囲は同じ行から始まり、同じ高さにする。
MATCH(...,A2:A6,0)は2行目から数えるので、INDEXはC1:C6ではなくC2:C6を読む必要があります(C1:C6だと1つ上の行を返します)。 - MATCHの最後は0にする。 0がないとMATCHはA列が並べ替えられていると仮定した近似一致を行い、名前のリストでは違う行の位置を返すことがあります。3つの照合の種類はMATCHのページで説明しています。
値がリストにないと、MATCHは#N/Aを返し、数式全体も#N/Aになります。=IFNA(INDEX(C2:C6,MATCH(F2,A2:A6,0)),"Not found")なら代わりに文字列を表示します。
左側を検索する
VLOOKUPは探す列より右の列を返します。INDEXとMATCHは列の順番を気にしません。D列を探してA列を返せます。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Code | Product | |
| 2 | Apple | Fruit | $1.20 | P-101 | P-310 | Bread | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
P-310ではBreadが返ります。F2にP-205と入力するとCarrotです。VLOOKUPなら、先にCode列を表の先頭に移す必要があります。
縦横の2方向の検索: 2つのMATCHを使うINDEX
INDEXは行番号と列番号を受け取ります。表全体を渡し、1つのMATCHに行を、もう1つのMATCHに列を探させます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | East | ||
| 7 | Month | Mar | ||
| 8 | Sales | 5,600 |
EastはA2:A5の3行目、MarはB1:D1の3列目なので、INDEXはB2:D5の3行3列目の5,600を返します。行のMATCHは最初の列を下方向に、列のMATCHは見出し行を横方向に探し、どちらの範囲も表B2:D5と位置がそろっています。
INDEX MATCHがVLOOKUPより優れている点
=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
どちらも価格を返します。違いが出るのはシートが変わったときです：
- 列の挿入。 CategoryとPriceの間に列を挿入すると、VLOOKUPは3列目を求め続けますが、そこは新しい空の列です。INDEX版では、Excelが
C2:C6をD2:D6に調整するので、そのまま動き続けます。 - 左側の検索。 上で見たとおり、VLOOKUPにはできず、INDEX MATCHにはできます。
Excel 2021とMicrosoft 365では、XLOOKUPが1つの関数で、もっと簡単な引数で両方をこなします。それでもExcel 2019以前で開く必要があるファイルにはINDEX MATCHが適していますし、INDEXの部分は単独でも役に立ちます。2つの条件で同時に検索する方法は複数条件での検索をご覧ください。
練習: 左側を検索する
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Price | Stock | Product | Look for | Price | |
| 2 | P-101 | $1.20 | 40 | Apple | Milk | ||
| 3 | P-102 | $1.50 | 25 | Pear | |||
| 4 | P-205 | $0.80 | 60 | Carrot | |||
| 5 | P-310 | $2.40 | 15 | Bread | |||
| 6 | P-412 | $1.10 | 30 | Milk |
やってみよう: 商品名は最後の列にあります。G2で、INDEXとMATCHを使ってF2の商品の価格を返してください。
練習: 縦横の2方向の検索
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Math | Science | Art |
| 2 | Ana | 78 | 85 | 92 |
| 3 | Ben | 64 | 71 | 88 |
| 4 | Cara | 95 | 89 | 73 |
| 5 | Dev | 82 | 67 | 79 |
| 6 | ||||
| 7 | Student | Cara | ||
| 8 | Subject | Science | ||
| 9 | Score |
やってみよう: B9で、B7の生徒のB8の教科の点数を返してください。
よくある質問
INDEXとMATCHの組み合わせはどう動きますか？
MATCHが列の中で値の位置を探し、INDEXが別の列のその位置の値を返します。=INDEX(C2:C6,MATCH("Pear",A2:A6,0))では、PearがA2:A6の2番目の項目なのでMATCHが2を返し、INDEXがC2:C6の2番目の項目を返します。
VLOOKUPではなくINDEX MATCHを使うのはなぜですか？
探す列より左の列を返せるうえ、表の中に列を挿入しても壊れないからです(古くなる列番号がありません)。Excel 2021とMicrosoft 365では、XLOOKUPが1つの関数で同じ利点を持っています。
MATCHの0はどういう意味ですか？
完全一致を求めるという意味です。指定しないとMATCHは照合の種類1、つまり列が昇順に並んでいることを前提にした近似一致を使い、並べ替えていないリストでは違う行の位置を返すことがあります。
INDEX MATCHで縦横の2方向の検索をするには？
INDEXに表全体と、行用と列用の2つのMATCHを渡します：=INDEX(B2:D5,MATCH("South",A2:A5,0),MATCH("Feb",B1:D1,0))は、Southの行とFebの列が交わる値を返します。
