=XLOOKUP(F2,A2:A6,C2:C6)は、A2:A6でF2の値を探し、C2:C6の同じ行の値を返します。既定では完全一致で探し、探す列はどこにあってもかまいません。Excel 2021かMicrosoft 365が必要です(Excel 2019以前ではINDEXとMATCHを使います)。F2に別の商品を入力してみてください。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Bread | $2.40 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
G2をクリックすると、検索範囲と戻り範囲がそれぞれ別の枠で示されます。C2:C6をB2:B6に変えると、G2はカテゴリーを返します。数える列番号がないので、AとCの間に列を挿入しても数式は壊れません。Excelが両方の範囲を移動してくれます。
XLOOKUP関数の構文
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| 引数 | 働き | 既定値 |
|---|---|---|
lookup_value(検索値) | 探す値。 | 必須 |
lookup_array(検索範囲) | 探す列(または行)。 | 必須 |
return_array(戻り範囲) | 値を返す列、行、ブロック。lookup_arrayと同じ高さ。 | 必須 |
if_not_found(見つからない場合) | 何も一致しないときに表示するもの。 | #N/A |
match_mode(一致モード) | 0完全一致、-1完全一致または次に小さい値、1完全一致または次に大きい値、2ワイルドカード。 | 0 |
search_mode(検索モード) | 1先頭から末尾、-1末尾から先頭、2と-2は並べ替えたデータでのバイナリ検索。 | 1 |
必須なのは最初の3つだけです。省略可能な引数を飛ばして後ろの引数を指定するには、カンマの間を空けます：=XLOOKUP(F2,A2:A6,C2:C6,,0,-1)はsearch_modeを指定し、if_not_foundは既定値のままにします。
複数の列をまとめて返す
XLOOKUPに複数列の幅の戻り範囲を渡すと、行全体が返ってきます。結果は数式の横のセルにスピルします。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Price | Stock |
| 2 | Apple | Fruit | $1.20 | 40 |
| 3 | Pear | Fruit | $1.50 | 25 |
| 4 | Carrot | Vegetable | $0.80 | 60 |
| 5 | Bread | Bakery | $2.40 | 15 |
| 6 | Milk | Dairy | $1.10 | 30 |
| 7 | Look for | Carrot | ||
| 8 | Result | Vegetable | $0.80 | 60 |
B8の1つの数式が、B8:D8をVegetable、$0.80、60で埋めます。C8に何かを入力すると、結果を置く場所がないのでB8は#SPILL!と表示します。それを消すと結果が戻ります。列を別の順番で返すには、戻り範囲をCHOOSECOLSで囲みます：=XLOOKUP(B7,A2:A6,CHOOSECOLS(B2:D6,3,1))はStock、Categoryの順に返します。
左側をXLOOKUPで検索し、一致しないときはメッセージを出す
探す列が最初にある必要はありません。ここではXLOOKUPがC列の価格を探し、A列の商品名を返しています。これはVLOOKUPにはできません。4つ目の引数で、その価格の商品がないときに表示するものを指定しています。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | Product | |
| 2 | Apple | Fruit | $1.20 | 40 | $2.40 | Bread | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
$2.40ではBreadが返ります。F2を3に変えると、G2は#N/Aの代わりに「No product」と表示します。4つ目の引数を""にすると、空に見えるセルになります。if_not_foundが対象にするのは「見つからない」だけです。高さの違う戻り範囲を指定すると#VALUE!のままで、それは見えるべきエラーです。
最後に一致した値を探す
XLOOKUPは上から最初に一致したものを返します。6つ目の引数search_modeを-1にすると下から探すので、最後に一致したものを返します。最新の注文、直近の価格、最後のステータスなどです。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | First | Last | |
| 2 | 2026-03-02 | Ben | 120 | Ben | 120 | 60 | |
| 3 | 2026-03-05 | Ana | 80 | ||||
| 4 | 2026-03-09 | Ben | 45 | ||||
| 5 | 2026-03-12 | Cara | 200 | ||||
| 6 | 2026-03-20 | Ben | 60 | ||||
| 7 | 2026-03-24 | Ana | 95 |
Benの最初の注文は120、最後は60です。E2をAnaに変えると、80と95になります。これは行が日付順に並んでいることが前提です。並んでいないなら、代わりにその顧客の最新の日付を検索します：=XLOOKUP(1,(B2:B7=E2)*(A2:A7=MAXIFS(A2:A7,B2:B7,E2)),C2:C7)。
近似一致: 次に小さい値、次に大きい値
match_modeを-1にすると、完全一致か、なければ次に小さい値を返します。手数料の段階、税率の区分、成績など、区分にはこの規則を使います。TRUEを指定したVLOOKUPと違い、表を並べ替えておく必要はありません。下の区分はわざと順不同にしてあります。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 5000 | 5% | Ana | 750 | 0% | |
| 3 | 0 | 0% | Ben | 4,200 | 3% | |
| 4 | 10000 | 8% | Cara | 5,000 | 5% | |
| 5 | 1000 | 3% | Dev | 12,500 | 8% |
Benの4,200は1,000と5,000の間なので、1,000の区分の3%になります。Caraの5,000は完全一致で5%です。match_modeを1にすると逆の動きで、完全一致か次に大きい値を返します。「入るいちばん小さい箱」や「次の配達枠」を求めるときに使います：=XLOOKUP(18,{5;12;25;50},{"S";"M";"L";"XL"},,1)はLを返します。
ワイルドカードを使ったXLOOKUP
match_modeを2にすると、*(任意の文字)と?(1文字)がワイルドカードになります。指定しないとXLOOKUPはその文字そのものを探します。これはVLOOKUP(完全一致でワイルドカードを受け付ける)とは逆で、ワイルドカードを使ったXLOOKUPが#N/Aやif_not_foundの文字列を返すよくある原因です：
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None") None: no product is named *coffee*
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None",2) 2.9, the price of Iced coffee
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
「coffee」で最初に見つかるのはIced coffeeで、$2.90です。6つ目の引数に-1を加えると、Coffee beansの$8.50が見つかります。Excelのほかの検索と同じく、大文字と小文字は区別しません。match_modeが2のときに本物のアスタリスクや疑問符を探すには、前にチルダを付けます："~*"。
縦横の2方向で検索するXLOOKUP
行全体を返すXLOOKUPを、もう1つのXLOOKUPの戻り範囲にできます。内側のXLOOKUPが地域で行を選び、外側のXLOOKUPがその行から月の列を選びます。
| 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 | South | ||
| 7 | Month | Feb | ||
| 8 | Sales | 3,600 |
XLOOKUP(B6,A2:A5,B2:D5)はSouthの行、3100、3600、3300を返します。外側のXLOOKUPはB1:D1でFebを見つけ、その行の対応する値3,600を取り出します。B6とB7で別の地域と月を選んでみてください。同じ検索をINDEXとMATCHで書く方法はINDEXとMATCHのページにあります。
古いExcelとGoogleスプレッドシートでのXLOOKUP
XLOOKUPはExcel 2021、Excel 2024、Microsoft 365、Excel for the web、モバイルアプリにあります。XLOOKUPを使ったファイルをExcel 2019以前で開くと、再計算された時点で数式が#NAME?と表示されます。どこでも動く必要があるファイルでは、すべてのバージョンが理解できるINDEXとMATCHで検索を書きます：
=XLOOKUP(F2, A2:A6, C2:C6, "Not found")
=IFNA(INDEX(C2:C6, MATCH(F2, A2:A6, 0)), "Not found")
Googleスプレッドシートには2022年から同じ引数のXLOOKUPがあります。違いを並べて比べるにはVLOOKUPとXLOOKUPの違いをご覧ください。商品とサイズ、名前と日付のように2つの列で同時に一致させる=XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6)の形は、複数条件での検索のページで説明しています。
練習: 価格か「Not found」を返す
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
やってみよう: G2で、F2の商品の価格を返してください。リストにないときは文字列Not foundを返します。
練習: 注文額に応じた割引
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order from | Discount | Order | Discount | |
| 2 | $0 | 0% | $320 | ||
| 3 | $100 | 5% | |||
| 4 | $250 | 10% | |||
| 5 | $500 | 15% |
やってみよう: 各割引は、その注文額以上に適用されます。E2で、XLOOKUPを使ってD2の注文額に対する割引を返してください。
よくある質問
ExcelでXLOOKUPはどう使いますか？
3つの引数を指定します。探すもの、探す列、返す列です。=XLOOKUP("Pear",A2:A6,C2:C6)はA2:A6でPearを探し、C2:C6の同じ行の値を返します。指定しなければ完全一致で探します。
XLOOKUPが使えるExcelのバージョンは？
Excel 2021、Excel 2024、Microsoft 365、Excel for the webです。Excel 2019以前では数式が#NAME?と表示されるので、=INDEX(C2:C6,MATCH(F2,A2:A6,0))を使います。GoogleスプレッドシートにもXLOOKUPがあります。
XLOOKUPで#N/Aの代わりに空白や文字列を返すには？
4つ目の引数if_not_found(見つからない場合)を使います：=XLOOKUP(F2,A2:A6,C2:C6,"Not found")。空に見えるセルにするなら""です。置き換えるのは見つからない場合だけで、それ以外のエラーは表示されます。
XLOOKUPで最後に一致した値を探すには？
6つ目の引数search_mode(検索モード)を-1にすると、下から上へ検索します：=XLOOKUP("Ben",B2:B7,C2:C7,,0,-1)は、Benの最初の金額ではなく最後の金額を返します。
XLOOKUPで複数の列を返せますか？
返せます。=XLOOKUP(F2,A2:A6,B2:D6)のように複数列の幅の範囲を返す範囲に指定すると、結果が横のセルにスピルします。スピル先のセルは空である必要があり、そうでないとExcelは#SPILL!と表示します。
