=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7)は、商品がE2でかつサイズがF2の行の価格を返します。各比較がすべての行を調べ、それらを掛け合わせると両方が真の行だけ1になり、XLOOKUPはその1を探します。Excel 2021かMicrosoft 365が必要です。下で紹介するINDEX MATCH版はどのバージョンでも動きます。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
TeaとLargeは5行目で交わるので、G2は$3.00を返します。JuiceとSmallを選ぶと、別の行からやはり$3.00が返ります。両方に一致する行がない場合に備えて4つ目の引数を加えることもできます：=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item")。
条件を掛け合わせるしくみ
A2:A7=E2はすべての商品をE2と比べ、6つのTRUEかFALSEを返します。そうしたリストを2つ掛け合わせるとTRUEが1、FALSEが0になり、両方で1の行だけが1になります。D列は1つの数式からスピルさせたそのリストです。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
1になっているのはD5だけです。F2かG2を変えると、1の位置が動きます。条件を1つ増やすごとに*(範囲=値)を1つ加えます。条件は等しいかどうかでなくてもよく、*(C2:C7<3)で「価格が3未満」を加えられます。すべての範囲は同じ行(A2:A7、B2:B7、C2:C7)にそろえる必要があります。戻り範囲の大きさが条件と違うと、XLOOKUPは#VALUE!を返します。
複数条件のINDEX MATCH
Excel 2019以前では、MATCHで同じ配列から1を探し、INDEXでその位置の価格を返します。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
CoffeeとLargeは配列の2番目の位置なので、INDEXは$3.50を返します。Excel 2019以前ではこれは配列数式で、Enterの代わりにCtrl+Shift+Enter(MacではCmd+Shift+Enter)を押すと、Excelは数式を波かっこで囲んで表示します。そこで普通にEnterを押すと、たいてい#N/Aか#VALUE!が返ります。Excel 365ではEnterだけで十分です。条件が1つの形はINDEXとMATCHのページにあります。
条件をつなげて1つのキーにする
もう1つの方法は、2つの条件をつなげて1つにすることです。VLOOKUPでは、つなげた値を表の先頭の作業列に置く必要があります(その方法はVLOOKUPのページにあります)。XLOOKUPなら数式の中で範囲をつなげられるので、作業列は要りません。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
A2:A7&"|"&B2:B7はJuice|Largeのような6つのキーを作り、XLOOKUPはその中からJuice|Largeを見つけて$4.00を返します。部分の間には区切り文字を入れてください。区切り文字がないと、「AB」と「C」をつなげたものと「A」と「BC」をつなげたものが同じ「ABC」になり、違う行を返すことがあります。
求める値が数値で、各組み合わせが1回しか出てこないなら、SUMIFSを使えば配列なしで同じ答えが出ます：=SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2)。何も一致しないとエラーではなく0を返すので、打ち間違いが隠れることがあります。
FILTERですべての一致を返す
XLOOKUPとINDEX MATCHは、最初に一致した行を返します。複数の行が一致し、そのすべてが欲しいときは、同じ条件でFILTERを使います。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
NorthかつPhoneの行は3つあるので、F2はその四半期と売上をF2:G4にスピルします。A3をSouthに変えると、リストは2行に減ります。一致する行がないとFILTERは#CALC!を返します。3つ目の引数に"None"のような文字列を指定すれば、代わりにそれを表示します。ほかのオプションはFILTERのページにあります。
練習: 3つの条件
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
やってみよう: G4に、G1の地域、G2の商品、G3の四半期に対応する売上を返してください。
よくある質問
XLOOKUPで複数の条件を使うには？
条件ごとの比較を掛け合わせ、1を検索します：=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7)。各比較は行ごとにTRUEかFALSEを返し、その積はすべてがTRUEの行だけ1になり、XLOOKUPはそうした最初の行を返します。
INDEX MATCHで2つの条件を使うには？
MATCHの中で同じように条件を掛け合わせます：=INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0))。Excel 2019以前では、Ctrl+Shift+Enter(MacではCmd+Shift+Enter)で確定します。
VLOOKUPで2つの条件を使えますか？
直接は使えません。2つの値をつなげる作業列を表の先頭に加え(=A2&"|"&B2など)、つなげた値を検索します：=VLOOKUP(E2&"|"&F2,helper_table,col,FALSE)。
2つの条件での検索をSUMIFSで代わりにできますか？
値が数値で、各組み合わせが1回しか出てこないならできます：=SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2)。一致する行がないと#N/Aではなく0を返し、組み合わせが2回出てくると値を足し合わせます。
ORの条件で検索するには？
条件を掛けるのではなく足します：(A2:A7="Tea")+(A2:A7="Juice")は、どちらかが真の行で1以上になります。0より大きい値を検索します。たとえば=XLOOKUP(TRUE,((A2:A7="Tea")+(A2:A7="Juice"))>0,C2:C7)です。
