XLOOKUP関数を試してみました。
なにか新しい関数ができたと、話には聞いていました。
その説明の中で「次の大きなアイテムが返されます」という記述。
なんとなくすっきりしない表現で、ずっと何だろうって思ってました。
この疑問を解決すべく、じゃぁブログにあげるか!というわけで資料を作ってみました。
XLOOKUP関数の動作について紹介しています。
沢山あった方がわかりやすいと思うので、23の使用例を作ってみました。
もちろん「大きなアイテムがー」もあります(笑
良かったら見てください。
構文
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
引数 | 必須/省略可 | 説明 |
---|---|---|
検索値 | 必須 | 検索する値を指定します |
検索範囲 | 必須 | 検索対象の配列または範囲を指定します |
戻り範囲 | 必須 | 戻り値の配列または範囲を指定します |
見つからない場合 | 省略可 | 検索の結果、見つからない場合に、XLOOKUP関数が返す値を指定します。 |
一致モード | 省略可 | 検索範囲内の値に対する検索値の一致方法を指定します 省略時は、完全一致になります |
検索モード | 省略可 | 使用する検索モードを指定します 省略時は、先頭から末尾へ検索します |
XLOOKUPの基本
例1 縦方向の検索
都道府県(E3)を指定して、対応するコード(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”千葉県”(E3)を、検索範囲(B3:B6)の先頭から検索します。
- 「先頭から2番目」の”千葉県”(B4)が見つかります。
- ②と同じ順番の、戻り範囲(C3:C6)内の「先頭から2番目」のC4を返します。
=XLOOKUP(E3,B3:B6,C3:C6)
- 検索モード省略のため、1(先頭から検索)
例2 横方向の検索
都道府県(H3)を指定して、対応するコード(I3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”千葉県”(H3)を、検索範囲(C2:F2)の先頭(左)から検索します。
- 「先頭(左)から2番目」の”千葉県”(D2)が見つかります。
- ②と同じ順番の、戻り範囲(C3:F3)内の「先頭(左)から2番目」のD3を返します。
=XLOOKUP(H3,C2:F2,C3:F3)
- 検索モード省略のため、1(先頭から検索)
例3 検索値が見つからない場合
都道府県(E3)を指定して、対応するコード(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”北海道”(E3)を、検索範囲(B3:B6)の先頭から検索します。
- 見つからないため、#N/Aを返します。
=XLOOKUP(E3,B3:B6,C3:C6)
- 一致モード省略のため、0(完全一致。見つからない場合は、#N/A が返されます)
- 検索モード省略のため、1(先頭から検索)
例4 「見つからない場合」の指定
都道府県(E3)を指定して、対応するコード(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”北海道”(E3)を、検索範囲(B3:B6)の先頭から検索します。
- 見つからないため、「見つからない場合」指定の”該当都道府県無し”を返します。
=XLOOKUP(E3,B3:B6,C3:C6,”該当都道府県無し”)
- 「見つからなかった場合」指定:”該当都道府県無し”
- 検索モード省略のため、1(先頭から検索)
検索モードの指定
例5 先頭から検索した場合
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値65(E3)を、検索範囲(C3:C6)の先頭から検索します。
- 「先頭から2番目」の65(C4)が見つかります。
- ②と同じ順番の、戻り範囲(B3:B6)内の「先頭から2番目」のB4を返します。
=XLOOKUP(E3,C3:C6,B3:B6,,,1)
- 検索モード:1(先頭から検索)
例6 末尾から検索した場合
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値65(E3)を、検索範囲(C3:C6)の末尾から検索します。
- 「末尾から2番目」の65(C5)が見つかります。
- ②と同じ順番の、戻り範囲(B3:B6)内の「末尾から2番目」のB5を返します。
=XLOOKUP(E3,C3:C6,B3:B6,,,-1)
- 検索モード:-1(末尾から検索)
一致モードと検索モードの指定
例7 次の大きなアイテムが返される場合(先頭から検索)
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値60(E3)を、検索範囲(C3:C6)の先頭から検索します。
- 見つからないため、検索値60(E3)の次の大きな値を、検索範囲(C3:C6)の先頭から検索します。
- 「先頭から3番目」の64(C5)が見つかります。
- ③と同じ順番の、戻り範囲(B3:B6)内の「先頭から3番目」のB5を返します。
=XLOOKUP(E3,C3:C6,B3:B6,,1,1)
- 一致モード:1(完全一致。見つからない場合は、次の大きなアイテムが返されます)
- 検索モード:1(先頭から検索)
例8 次の小さなアイテムが返される場合(先頭から検索)
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値70(E3)を、検索範囲(C3:C6)の先頭から検索します。
- 見つからないため、検索値70(E3)の次の小さな値を、検索範囲(C3:C6)の先頭から検索します。
- 「先頭から3番目」の66(C5)が見つかります。
- ③と同じ順番の、戻り範囲(B3:B6)内の「先頭から3番目」のB5を返します。
=XLOOKUP(E3,C3:C6,B3:B6,,-1,1)
- 一致モード:-1(完全一致。見つからない場合は、次の小さなアイテムが返されます)
- 検索モード:1(先頭から検索)
例9 次の大きなアイテムが返される場合(末尾から検索)
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値60(E3)を、検索範囲(C3:C6)の末尾から検索します。
- 見つからないため、検索値60の次の大きな値を、検索範囲(C3:C6)の末尾から、検索します。
- 「末尾から3番目」の64(C4)が見つかります。
- ③と同じ順番の、戻り範囲(B3:B6)内の「末尾から3番目」のB4を返します。
=XLOOKUP(E3,C3:C6,B3:B6,,1,-1)
- 一致モード:1(完全一致。見つからない場合は、次の大きなアイテムが返されます)
- 検索モード:-1(末尾から検索)
例10 次の小さなアイテムが返される場合(末尾から検索)
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値70(E3)を、検索範囲(C3:C6)の末尾から検索します。
- 見つからないため、検索値70の次に小さな値を、検索範囲(C3:C6)の末尾から検索します。
- 「末尾から3番目」の66(C4)が見つかります。
- ③と同じ順番の、戻り範囲(B3:B6)内の「末尾から3番目」のB4を返します。
=XLOOKUP(E3,C3:C6,B3:B6,,-1,-1)
- 一致モード:-1(完全一致。見つからない場合は、次の小さなアイテムが返されます)
- 検索モード:-1(末尾から検索)
例11 次の大きなアイテムも見つからない場合(先頭から検索)
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値80(E3)を、検索範囲(C3:C6)の先頭から検索します。
- 見つからないため、検索値80の次の大きな値を、検索範囲(C3:C6)の先頭から検索します。
- 見つからないため、#N/Aを返します。
=XLOOKUP(E3,C3:C6,B3:B6,,1,1)
- 一致モード:1(完全一致。見つからない場合は、次の大きなアイテムが返されます)
- 検索モード:1(先頭から検索)
例4のように「見つからない場合」を指定すれば、#N/Aではなく、その指定した値が返されます。
例12 次の小さなアイテムも見つからない場合(先頭から検索)
平均点(E3)を指定して、対応する名前(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値60(E2)を、検索範囲(C3:C6)の先頭から検索します。
- 見つからないため、検索値60の次の小さな値を、検索範囲(C3:C6)の先頭から検索します。
- 見つからないため、#N/Aを返します。
=XLOOKUP(E3,C3:C6,B3:B6,,-1,1)
- 一致モード:-1(完全一致。見つからない場合は、次の小さなアイテムが返されます)
- 検索モード:1(先頭から検索)
例4のように「見つからない場合」を指定すれば、#N/Aではなく、その指定した値が返されます。
例13 よく使われる?「次の大きなアイテム」が返される場合の一例
所得(E3)を指定して、税率(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値4,000,000(E3)を、検索範囲(B3:B6)の先頭から検索します。
- 見つからないため、検索値”4,000,000″の次の大きな値を、検索範囲(B3:B6)の先頭から検索します。
- 「先頭から3番目」の”7,000,000″(B5)が見つかります。
- ③と同じ順番の、戻り範囲(C3:C6)内の「先頭から3番目」のC5を返します。
=XLOOKUP(E3,B3:B6,C3:C6,,1)
- 一致モード:1(完全一致。見つからない場合は、次の大きなアイテムが返されます)
- 検索モード省略のため、1(先頭から検索)
指定した範囲の合計
例14 先頭と末尾から合計を求める
期間(E3,F3)を指定して、その期間の降水量の合計(G3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
(1)左のXLOOKUP XLOOKUP(E3,B3:B14,C3:C14)
- 検索値4(E3)を、検索範囲(B3:B14)の先頭から検索します。
- 「先頭から4番目」の4(B6)が見つかります。
- ②と同じ順番の、戻り範囲(C3:C14)内の「先頭から4番目」のC6を返します。
(2)右のXLOOKUP XLOOKUP(F3,B3:B14,C3:C14)
- 検索値6(F3)を、検索範囲(B3:B14)の先頭から検索します。
- 「先頭から6番目」の6(B8)が見つかります。
- ②と同じ順番の、戻り範囲(C3:C14)内の「先頭から6番目」のC8を返します。
(3)XLOOKUP関数の結果、SUM関数にはそれぞれC6とC8を返します。
(4)SUM関数の結果、=SUM(C6:C8)=628を返します。
=SUM(XLOOKUP(E3,B3:B14,C3:C14):XLOOKUP(F3,B3:B14,C3:C14))
縦方向と横方向の同時検索
例15 XLOOKUPを入れ子にして縦/横方向を同時に検索
都道府県と月(H14,I14)を指定して、その降水量(J14)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
(1)内側のXLOOKUP XLOOKUP(I14,B3:B14,C3:F14)
- 検索値4(I14)を、検索範囲(B3:B14)の先頭から検索します。
- 「先頭から8番目」の8(B10)が見つかります。
- ②と同じ順番の、戻り範囲(C3:F14)内の「先頭から8番目(の行)」のC10:F10を返します。
(2)外側のXLOOKUP XLOOKUP(H14,C2:F2,XLOOKUP(I14,B3:B14,C3:F14))
- 検索値”千葉県”(H14)を、検索範囲(C2:F2)の先頭から検索します。
- 「先頭(左)から2番目」の”千葉県”のD2が見つかります。
- ②と同じ順番の、戻り範囲(XLOOKUP(I14,B3:B14,C3:F14)=(C10:F10))内の「先頭(左)から2番目」のD10を返します。
=XLOOKUP(H14,C2:F2,XLOOKUP(I14,B3:B14,C3:F14))
- 一致モード省略のため、0(完全一致。見つからない場合は、#N/A が返されます)
- 検索モード省略のため、1(先頭から検索)
スピルの利用
例16 結果を複数返す方法(横方向)
科目(G3)を指定して、各クラスの社会の平均点(H3,I3,J3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”社会”(G3)を、検索範囲(B3:B6)の先頭から検索します。
- 「先頭から2番目」の”社会”(B4)が見つかります。
- ②と同じ順番の、戻り配列(C3:E6)の「先頭から2番目(の行)」のC4:E4を返します。
- 返されるC4:E4は、H3:J3の範囲に自動的にスピルされます。
=XLOOKUP(G3,B3:B6,C3:E6)
- 検索モード省略のため、1(先頭から検索)
I3、J3のセルは空にしておいてください。空でないと#SPILL!のエラーが出ます。
例17 結果を複数返す方法(縦方向)
組(H2)を指定して、各教科の平均点(H3,H4,H5,H6)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”B組”(H2)を、検索範囲(C2:E2)の先頭(左)から、検索します。
- 「先頭(左)から2番目」の”B組”(D2)が見つかります。
- ②と同じ順番の、戻り配列(C3:E6)の「先頭(左)から2番目(の列)」のD3:D6を返します。
- 返されるD3:D6は、H3:H6の範囲に自動的にスピルされます。
=XLOOKUP(H2,C2:E2,C3:E6)
- 検索モード省略のため、1(先頭から検索)
※H4、H5、H6のセルは空にしておいてください。空でないと#SPILL!のエラーが出ます。
配列定数の利用
例18 スピル範囲を参照する方法1
都道府県(E3)を指定して、対応するコード(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”千葉県”(E3)を、検索範囲(B3#→B3:B6)の先頭から検索します。
- 「先頭から2番目」の”千葉県”(B4)が見つかります。
- ②と同じ順番の、戻り範囲(C3#→C3:C6)内の「先頭から2番目」のC4を返します。
=XLOOKUP(E3,B3#,C3#)
- B3#:B3の配列定数を指します。スピル範囲はB3:B6になります。
- C3#:C3の配列定数を指します。スピル範囲はC3:C6になります。
- #記号:スピル範囲演算子です。スピル範囲の参照に使われます。
- 検索モード省略のため、1(先頭から検索)
B3の配列定数
={“埼玉県”;”千葉県”;”東京都”;”神奈川県”}
※B3に値を設定する前に、B4、B5、B6のセルは空にしておいてください。空でないと#SPILL!のエラーが出ます。
※縦方向の場合は、「;(セミコロン)」で区切ってください。
C3の配列定数
={11;12;13;14}
※C3に値を設定する前に、C4、C5、C6のセルは空にしておいてください。空でないと#SPILL!のエラーが出ます。
※縦方向の場合は、「;(セミコロン)」で区切ってください。
例19 スピル範囲を参照する方法2
都道府県(H3)を指定して、対応するコード(I3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”千葉県”(H3)を、検索範囲(C2#→C2:F2)の先頭から検索します。
- 「先頭から2番目」の”千葉県”(D2)が見つかります。
- ②と同じ順番の、戻り範囲(C3#→C3:F3)内の「先頭から2番目」のD3を返します。
=XLOOKUP(H3,C2#,C3#)
- C2#:C2の配列定数を指します。スピル範囲はC2:F2になります。
- C3#:C3の配列定数を指します。スピル範囲はC3:F3になります。
- #記号:スピル範囲演算子です。スピル範囲の参照に使われます。
- 検索モード省略のため、1(先頭から検索)
C2の配列定数
={“埼玉県”,”千葉県”,”東京都”,”神奈川県”}
※C2に値を設定する前に、D2、E2、F2のセルは、空にしておいてください。空でないと#SPILL!のエラーが出ます。
※横方向の場合は、「,(コンマ)」で区切ってください。
C3の配列定数
={11,12,13,14}
※C3に値を設定する前に、D3、E3、F3のセルは、空にしておいてください。空でないと#SPILL!のエラーが出ます。
※横方向の場合は、「,(コンマ)」で区切ってください。
例20 例18表18に群馬県を追加した場合
都道府県(E3)を指定して、対応するコード(F3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
例18と比べるとわかりますが、XLOOKUPの指定に変更はありません。
配列の変更のみです。スピル範囲演算子を使うと保守性が高いですね(名前の定義でも同じですが)。
- 検索値”千葉県”(E3)を、検索範囲(B3#→B3:B7)の先頭から、検索します。
- 「先頭から3番目」の”千葉県”(B5)が見つかります。
- ②と同じ順番の、戻り範囲(C3#→C3:C7)内の「先頭から3番目」のC5を返します。
=XLOOKUP(E3,B3#,C3#)
- B3#:B3の配列定数を指します。スピル範囲はB3:B7になります。
- C3#:C3の配列定数を指します。スピル範囲はC3:C7になります。
- #記号:スピル範囲演算子です。スピル範囲の参照に使われます。
- 検索モード省略のため、1(先頭から検索)
B3の配列定数 ‥群馬県を追加しました。
={“群馬県“;”埼玉県”;”千葉県”;”東京都”;”神奈川県”}
※B3に値を設定する前に、B4、B5、B6、B7のセルは空にしておいてください。空でないと#SPILL!のエラーが出ます。
※縦方向の場合は、「;(セミコロン)」で区切ってください。
C3の配列定数 ‥10を追加しました。
={10;11;12;13;14}
※C3に値を設定する前に、C4、C5、C6、C7のセルは空にしておいてください。空でないと#SPILL!のエラーが出ます。
※縦方向の場合は、「;(セミコロン)」で区切ってください。
範囲の拡張
例21 検索範囲と戻り範囲の拡張
都道府県(F3)を指定して、対応するコード(G3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”秋田県”(F3)を、検索範囲(C3:C6:C10:C13)の先頭から、検索します。
- 「先頭から6番目」の”秋田県”(C11)が見つかります。
- ②と同じ順番の、戻り範囲(D3:D6:D10:D13)内の「先頭から6番目」のD11を返します。
=XLOOKUP(F3,(C3:C6:C10:C13),(D3:D6:D10:D13))
- 検索モード省略のため、1(先頭から検索)
この例のように、表の形式は同じだけれど、複数の表に分かれているときは、範囲をつなげて指定すると便利です。
範囲の連結
例22 検索値の連結と検索範囲の連結
都道府県(F3)、市町村(F4)を指定して、対応するコード(G3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”千葉県浦安市”(F3&F4)を、検索範囲(B3:B6&C3:C6)の先頭から検索します。
- 「先頭から2番目」の”千葉県浦安市”(B4&C4)が見つかります。
- ②と同じ順番の、戻り範囲(D3:D6)内の「先頭から2番目」のD4を返します。
=XLOOKUP(F3&F4,B3:B6&C3:C6,D3:D6)
- 検索モード省略のため、1(先頭から検索)
例23 検索範囲の連結
都道府県(F3)を指定して、対応するコード(G3)を求めます。
構文
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
- 検索値”千葉県浦安市”(F3)を、検索範囲(B3:B6&C3:C6)の先頭から検索します。
- 「先頭から2番目」の”千葉県浦安市”(B4&C4)が見つかります。
- ②と同じ順番の、戻り範囲(D3:D6)内の「先頭から2番目」のD4を返します。
=XLOOKUP(F3,B3:B6&C3:C6,D3:D6)
- 検索モード省略のため、1(先頭から検索)
まとめ
以上、簡単ではありますがExcelのXLOOKUP関数の動作についてご紹介いたしました。
※ワイルドカード検索等、私があまり使わないものは割愛しました。
先代のVLOOKUP関数、HLOOKUP関数に比べて、使いやすくなりましたね。
まず、引数の指定が、楽になりました。
検索値はここでで、検索範囲はここで、、戻り範囲はここで、という風に、直感的に見たものをすぐにマウスで指定できます。
列数は何列目だっけ?などの思考停止がないのが嬉しいです。
次は、見つからない場合の指定ができることかな。
あの#N/Aを見なくて済むのは、地味に嬉しいです。
今回簡単にXLOOKUPを使ってみましたが、他にもまだまだ色々な使い道がありそうです。
皆さんも、是非試してみてください。
※当ウェブサイトは、マイクロソフト株式会社様とは、一切関係がございません。
※本記事で使っているアプリケーション(バージョン):Microsoft® Excel for Microsoft 365 MSO (16.0.14026.20202) 64 ビット
2021.6.12 癸卯