スプレッドシート（spreadsheet）のXLOOKUP関数の使い方と例です。
XLOOKUPの基本の使い方をやさしく解説
| これだけ覚えて |
|---|
| =XLOOKUP(①探す値, ②検索する範囲 , ③範囲に対応した取り出したい値のある範囲 , ④見つからなかった時の処理） |
XLOOKUP関数は、引数に指定できる値が6個ありますが、基本的に使うのは前半の４つの引数だけです。この値の設定の仕方をまずは覚えましょう。
商品名を検索して、対応する値段を返す
| 商品Aの値段を表から探す |
|---|
 |
| 実際に入力する式 |
| =XLOOKUP(E2,B2:B7,C2:C7,”該当なし”) |
設定する手順
- 探す値を設定する。表の場合は商品Aの入った「E2」を設定
- 検索する範囲を設定。商品名の列から探したいので「B2:B7」を設定B2:B7
- 取り出したい値のある範囲。商品Aと一致する行の値段が知りたいのでC列の、先ほど設定したB2:B7に対応する「C2:C7」を設定
- 値が見つからなかった時は、該当なしと表示したいので「”該当なし”」を設定
1つ関数をつくればオートフィルを使って一気に関数を当てはめられる
１度関数を作ればこのように複数の商品の値段についても一気に調べることができます。
※画像では、参照する範囲がずれないように「=XLOOKUP(E2,B:B,C:C,”該当なし”)」として列を参照しています。詳しくは応用テクニックの方で参照範囲をずれないようにする方法をいくつか解説しています。
XLOOKUPの書式の解説
書式（構文）
| 書式と説明 | 関数の分類 |
|---|---|
| =XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード]) ▼範囲または配列を検索し、最初に見つかった一致に対応する項目を返す。一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返す。 | 検索・参照 |
| 検索値 | 検索する値。値でもセルの参照でも可 | 必須 |
|---|---|---|
| 検索範囲 | 検索値を検索する範囲 | 必須 |
| 戻り配列 | 検索値に対応する値がある範囲 | 必須 |
| 見つからない場合 | 検索値が見つからない場合に返す値 ※設定せずに見つからないと#N/Aを返す | 任意 |
| 一致モード | 検索の方向 0または省略: 完全一致。検索値が検索範囲内の値と完全に一致する場合に返します。 -1: 正確または次に小さい項目。検索値が見つからない場合、次に小さい値を返します。 1: 正確または次に大きい項目。検索値が見つからない場合、次に大きい値を返します。 2: ワイルドカード文字（* または ?）を使用した部分一致。このモードでは、検索値内のワイルドカード文字を使用して、部分的な一致を検索できます。 | 任意 |
| 検索モード | 検索のモード 0または省略: 最初から最後への標準検索。検索範囲を最初から順に検索し、最初に見つかった一致を返します。 -1: 最後から最初への逆順検索。検索範囲を最後から逆順に検索します。これは、最新のデータから古いデータへと検索したい場合に便利です。 1: 二分探索（昇順）。検索範囲が昇順に並んでいる場合に使用します。二分探索は大きなデータセットで効率的ですが、データが正しく並んでいないと正確な結果が得られません。 2: 二分探索（降順）。検索範囲が降順に並んでいる場合に使用します。こちらも昇順の二分探索と同様に、データセットが正しい順序であることが前提です。 | 任意 |
| 使用時のポイント |
|---|
| 左方向の列も参照できる 列を追加しても参照を自動で修正してくれる 見つからない時の処理を書ける |
XLOOKUPを使用するメリット
左方向の例を参照できる
=XLOOKUP(E2, B2:B7, A2:A7,"該当なし")
こちらは、商品IDを探す例です。XLOOKUP関数では左方向の列も指定できるので、非常に便利な関数となっています。
※VLOOKUP関数では右方向の列しか検索できません
列を追加しても元のセルを参照してくれる
出荷先の列を追加していますが、XLOOKUP関数の値は、元のセルを参照するように変更されます。
=XLOOKUP(F2, C2:C7, A2:A7,"該当なし")XLOOKUPの応用テクニック
オートフィルで範囲がズレない用にする
【列を参照させる例】
=XLOOKUP(F2, C:C, A:A,"該当なし")【範囲を絶対参照させる例】
=XLOOKUP(F2, C$2:C$7, A$2:A$7,"該当なし")
列や絶対参照を指定すれば、オートフィルした際に、参照範囲がズレるといったことがなくなります。
列を参照するか、絶対参照で行うかは場合によって使い分けましょう。
見つからない場合に関数を入れる
XLOOKUPで値が見つからなかった場合には、関数をいれることもできます。
見つからなかった場合には別の列を参照する例
 シート1 |  シート2 |
=XLOOKUP(E8,B:B,C:C,XLOOKUP(E8,'シート2'!B:B,'シート2'!C:C))シート1で値が見つからなかった場合は、シート2からXLOOKUP関数を使って値を探し、入力する例です。
商品Gは、シート1内に無いので、シート2から値段を探しています。
条件に一致したときだけXLOOKUPする
すでに入力済のデータ以外を探す例
=IF(len(F3)>0,F3,XLOOKUP(E3,B:B,C:C,"該当なし"))IF文を使い、F列の値段がある場合はそのままF列の値を入れ、F列の値が空白の場合はXLOOKUP関数を使って値段を調べるようにしています。調査中のデータを更新する際に便利です。
特定の文字のみにXLOOKUPを行う例
=if(COUNTIF(E2,"*りんご*"),XLOOKUP(E2,B:B,C:C,""),"")こちらは、「りんご」と名の付く商品だけに値段調査を行う例です、if文を組み合わせることで、様々な条件を組み合わせてデータを完成させることができます。
コメント