XLOOKUPはVLOOKUPのできることをすべてこなし、間違える余地が少なくなっています。既定で完全一致になり、返す列を番号ではなく範囲で受け取り、左側も検索でき、「見つからない場合」の引数を持っています。ファイルをXLOOKUPのないExcel 2019以前で動かす必要があるときは、VLOOKUPを使います。下のシートでは、同じ検索を2通りで実行しています。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Pear | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | XLOOKUP | $1.50 | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
どちらも$1.50を返します。それぞれの数式をクリックしてください。VLOOKUPは表A2:D6全体を枠で示してその中で3列数え、XLOOKUPは探す列と返す列だけを枠で示します。
VLOOKUPとXLOOKUPの比較
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Excelのバージョン | すべて | Excel 2021、2024、Microsoft 365、Web版 |
| 既定の一致 | 近似一致(4つ目の引数を省略した場合) | 完全一致 |
| 返す列 | 表の中で数えた番号 | 範囲 |
| 表の中に列を挿入したとき | 違う列を返す | そのまま動く |
| 左側の検索 | できない | できる |
| 値が見つからない | #N/A、IFNAで囲む | 4つ目の引数、"Not found" |
| 最後の一致 | できない | search_modeを-1 |
| 複数の列をまとめて | 数式1つにつき1列(Microsoft 365では列番号に{2,3}) | 複数列の戻り範囲でスピル |
| 近似一致 | 次に小さい値、データの並べ替えが必要 | 次に小さい値か次に大きい値、順不同でよい |
| ワイルドカード | FALSEで有効 | match_modeが2のときだけ |
| 横方向の検索 | HLOOKUPが必要 | 同じ関数 |
どちらも大文字と小文字を区別しません。完全一致では、XLOOKUPに下から探すよう指定しない限り、どちらも上から最初の一致を返します。
見つからない場合: IFNAと4つ目の引数
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Kiwi | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | #N/A | |
| 3 | Pear | Fruit | $1.50 | 25 | with IFNA | Not found | |
| 4 | Carrot | Vegetable | $0.80 | 60 | XLOOKUP | Not found | |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A 探している値が検索範囲にありません。普通のVLOOKUPは#N/Aと表示します。VLOOKUPで文字列を表示するにはIFNAで囲む必要があり、XLOOKUPはそれを4つ目の引数で行います。G1にMilkと入力すると、3つとも$1.10になります。
左側の検索と列の移動
VLOOKUPの構造上の2つの制限は、列番号から来ています。探す列の右側にしか数えられず、表が変わっても番号は変わりません。CategoryとPriceの間に列を挿入しても、=VLOOKUP(G1,A2:D6,3,FALSE)は3列目、つまり新しい列を返し続けます。XLOOKUPは返す列を範囲で参照するので、Excelがほかの参照と同じように調整し、探す列もどこにあってもかまいません。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | $0.80 | |
| 2 | Apple | Fruit | $1.20 | 40 | XLOOKUP | Carrot | |
| 3 | Pear | Fruit | $1.50 | 25 | INDEX MATCH | Carrot | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
どちらもCarrotを返します。名前が価格の左側にあるので、VLOOKUP版はありません。古いExcelでの答えはG3のINDEX MATCHで、列を挿入しても壊れません。詳しくはINDEXとMATCHのページで説明しています。
近似一致を両方で
区分の検索では、VLOOKUPはTRUEを使い、最初の列を昇順に並べておく必要があります。XLOOKUPはmatch_modeの-1を使い、並べ替えは不要です。match_modeを1にすれば次に大きい値も求められますが、これはVLOOKUPにはできません。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sales from | Rate | Sales | 4,200 | |
| 2 | 0 | 0% | VLOOKUP | 3% | |
| 3 | 1000 | 3% | XLOOKUP | 3% | |
| 4 | 5000 | 5% | |||
| 5 | 10000 | 8% |
4,200ではどちらも3%を返します。E1を10000に変えると、どちらも8%を返します。
VLOOKUPを使い続けるべきとき
- Excel 2019、2016以前を使う人とファイルを共有する。 そこではXLOOKUPは#NAME?になります。どこでも動くもう1つの選択肢はINDEX MATCHです。
- ブックに正しく動いているVLOOKUPがすでに何百もある。 書き直してもほとんど得るものはないので、新しい数式にXLOOKUPを使ってください。
速さはどちらかを選ぶ理由になりません。普通のシートではどちらも一瞬で、並べ替えたとても大きなリストでは、XLOOKUPのバイナリ検索(search_modeが2)もTRUEを指定したVLOOKUPも高速です。
Googleスプレッドシートは、どちらの関数も同じ引数で使えます。
練習: VLOOKUPを書き換える
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | 40 | Stock | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
やってみよう: =VLOOKUP(G1,A2:D6,4,FALSE)はG1の商品の在庫を返します。G2に、同じ検索をXLOOKUPで書いてください。
よくある質問
XLOOKUPはVLOOKUPより優れていますか？
Excel 2021かMicrosoft 365で新しく作るなら、優れています。既定で完全一致になり、列が動くと壊れる列番号がなく、左側も検索でき、見つからない場合の文字列も組み込まれています。VLOOKUPのほうがよいのは、XLOOKUPが#NAME?になるExcel 2019以前でファイルを動かす必要があるときだけです。
XLOOKUPはVLOOKUPより速いですか？
普通のシートでは気づくほどの差はなく、どちらも数千行を一瞬で検索します。並べ替えたとても大きなデータでは、XLOOKUPのバイナリ検索モード(search_modeが2)のほうが線形検索より速く、TRUEを指定したVLOOKUPもバイナリで検索します。
XLOOKUPとINDEX MATCHの違いは何ですか？
どちらも同じ検索ができます。XLOOKUPは1つの関数で引数が簡単で、見つからない場合の引数もあります。INDEX MATCHはどのバージョンのExcelでも動きます。=XLOOKUP(F2,A2:A6,C2:C6)と=INDEX(C2:C6,MATCH(F2,A2:A6,0))は同じ値を返します。
VLOOKUPをXLOOKUPに書き換えるには？
検索値はそのままにし、表を最初の列と返していた列に分け、列番号とFALSEを外します：=VLOOKUP(F2,A2:D6,3,FALSE)は=XLOOKUP(F2,A2:A6,C2:C6)になります。
