Googleスプレッドシートで指定した値に一致するデータを見つけたいときによく使用されるVLOOKUP関数、HLOOKUP関数があります。この関数はスプレッドシートだけでなく、Excelでも使用頻度が高い重要な関数です。
XLOOKUP関数はこのVLOOKUPの関数の類似の関数として2022年に新たに登場した関数です。
INDEX
スプレッドシートのXLOOKUP関数とは？VLOOKUP関数との違いは？
XLOOKUP関数はVLOOKUP関数と同じ場面で使える
XLOOKUP関数の特徴は以下の2つあります。
1つ目は、VLOOKUP・HLOOKUP関数と同じことができる、代わりに使うことができます。
今までは参照する表が縦に並んでいたらVLOOKUP、横に並んでいたらHLOOKUPという使い分けをしていましたが、XLOOKUP関数はこの関数だけで縦横どちらのパターンでも使用することができます。
2つ目の特徴は、VLOOKUP関数を使いたいけどデータの並び順により使えない場合にXLOOKUP関数なら検索値よりも左の列のデータを返すことができます。
VLOOKUP関数では検索値の右にあるデータした表示させることができませんでした。
便利な関数ではあるものの、データの並び順によっては使い勝手が悪い点がありました。
この部分が使いやすくなったのがXLOOKUP関数です。
このページでは、XLOOKUP関数とは何か、XLOOKUP関数の使い方をわかりやすく解説していきます。
XLOOKUP関数をVLOOKUP/HLOOKUP関数の代わりに使う方法
VLOOKUP関数と同じように使う方法
まずはXLOOKUP関数の基本的な数式の入れ方、VLOOKUPと同じように使う方法です。
できることは同じですが数式の入れ方がVLOOKUP関数とは少し異なります。
商品コードを入れたら対応する商品名を自動表示する
商品コード「1001」を入れたら、対応する商品名「いちご」を自動的に表示したい
上記の例では、商品名を表示させるセル「セルC4」にXLOOKUP関数で数式を入力します。
ここでは商品名を抽出するのに使用する商品一覧のリストが別シート（商品一覧シート）にあると想定した説明になります。
XLOOKUP関数を使用するには、3つの情報（=引数）を指定します。
- ［検索キー］検索をする時に使用するデータ（表からデータを探すための検索する値）
- ［検索範囲］データを探しに行く一覧表の上記指定した検索キーがあるセルの範囲。
列のすべてを選択します。（数式をコピーした際に範囲が変わらないように絶対参照で固定します） - ［結果の範囲］自動表示したいデータがあるセルの範囲を指定。
列のすべてを選択します。（数式をコピーした際に範囲が変わらないように絶対参照で固定します）
VLOOKUPでは一覧表の左から何列目を使うかという番号で指定しましたが、XLOOKUP関数は、自動で表示したいデータの列を範囲で指定します。
=XLOOKUP(B4,'商品一覧'!$A$4:$A$10,'商品一覧'!$B$4:$B$10)
[解説]
商品コード「1001」を入力すると、一覧表で「1001」に該当する商品の「いちご」が表示されるようにする。
・商品コードを入力する「セルB4」が［検索キー］になります。（セルB4）
・その商品コードが何かがわかるリストの商品コードが並んでいる列全体を［検索範囲］に指定します。（A列）
・答えとして表示させたい、商品名が並んでいる列全体を［結果の範囲］に指定します。（B列）
［検索キー］で指定するのは1つのセルです。（複数セルは選択しない）
商品コードを入れたら対応する単価を自動表示する
次に、単価（一覧表のC列）の値を表示させる数式の入れ方です。
単価を表示させるセル「セルD4」にXLOOKUP関数で数式を入力します。
商品コード「1001」を入れたら対応する商品の単価「￥450」を自動的に表示したい
=XLOOKUP(B4,'商品一覧'!$A$4:$A$10,'商品一覧'!$C$4:$C$10)
[解説]
商品コード「1001」を入力すると、一覧表で「1001」に該当する商品であるいちごの単価「￥450」が表示されるようにする。
・商品コードを入力する「セルB4」が［検索キー］になります。（セルB4）
・その商品コードが何かがわかるリストの商品コードが並んでいる列全体を［検索範囲］に指定します。（A列）
・答えとして表示させたい、単価が並んでいる列全体を［結果の範囲］に指定します。（C列）
数式を下の行にコピーすると指定したセルがコピーしたセルに合わせて移動します。
一覧表のデータの位置は変わらないのでコピーしてもセル範囲の位置を固定させる場合は絶対参照（セル範囲に$で指定）を使用してください。
HLOOKUP関数と同じように使う方法
HLOOKUP関数と同じように使う場合は以下のように範囲を横に指定します。
商品コードを入れたら対応する商品名を自動表示する
商品コード「1001」を入れたら対応する商品名「いちご」を自動的に表示したい
=XLOOKUP(B4,'商品一覧'!$B$3:$F$3,'商品一覧'!$B$4:$F$4)
[解説]
商品コード「1001」を入力すると、一覧表で「1001」に該当する商品名「いちご」が表示されるようにする。
・商品コードを入力する「セルB4」が［検索キー］になります。（セルB4）
・その商品コードが何かがわかるリストの商品コードが並んでいる行全体を［検索範囲］に指定します。（3行目）
・答えとして表示させたい、単価が並んでいる列全体を［結果の範囲］に指定します。（4行目）
商品コードを入れたら対応する単価を自動表示する
商品コード「1001」を入れたら対応する商品の単価「￥450」を自動的に表示したい
=XLOOKUP(B4,'商品一覧'!$B$3:$F$3,'商品一覧'!$B$5:$F$5)
XLOOKUP関数で［検索キー］の左や上にあるデータを使用する方法
VLOOKUP関数では［検索キー］が選択したデータ範囲の一番左の列にある必要があり、その右にある列のデータを返す関数でした。HLOOKUP関数では［検索キー］が一番上にありその下にあるデータしか返すことができませんでした。
そうするとデータの並び順によっては［検索キー］の左（上）のデータを使う、という時に使うことができなく少し不便な面がありました。
XLOOKUP関数ではデータ列の並び方を気にせず［検索キー］の左右どちらにあるデータでも使うことができます。
［検索キー］の左にあるデータを使用する方法
［検索キー］の左にあるデータを使用する方法を説明します。
商品名「いちご」を入れたら、商品コード「1001」を自動的に表示したい
=XLOOKUP(C4,'商品一覧'!$B$4:$B$10,'商品一覧'!$A$4:$A$10)
[解説]
商品名「いちご」を入力すると、一覧表で「いちご」に該当する商品コード「1001」が表示されるようにする。
・商品名を入力する「セルC4」が［検索キー］になります。（セルC4）
・その商品名を含むリストの商品名が並んでいる列全体を［検索範囲］に指定します。（B列）
・答えとして表示させたい、商品コードが並んでいる列全体を［結果の範囲］に指定します。（A列）
［検索キー］の上にあるデータを使用する方法
下記は、商品名「いちご」を入れたら商品コード「1001」を自動的に表示する例です。
=XLOOKUP(C4,'商品一覧'!$B$4:$F$4,'商品一覧'!$B$3:$F$3)
[解説]
商品名「いちご」を入力すると、一覧表で「いちご」に該当する商品コード「1001」が表示されるようにする。
・商品名を入力する「セルC4」が［検索キー］になります。（セルC4）
・その商品名を含むリストの商品名が並んでいる行全体を［検索範囲］に指定します。（4行目）
・答えとして表示させたい、商品コードが並んでいる行全体を［結果の範囲］に指定します。（3行目）
- 他のセルにコピーするときに範囲が変わってしまわないように範囲を選択の時は絶対参照（$付き）で指定します。
- 検索キーに一致するデータが範囲に2つ以上ある場合は、一番最初に見つかった値を返します。
- ただし［検索モード］オプションを指定することで値を上から検索するか下から検索するかを指定することができます。下から検索で指定すると複数データがあった場合に下から探して最初に見つかった値を返すことができます。
［検索モード］の指定方法はこの下にある引数の説明の表をご覧ください。
スプレッドシートのXLOOKUP - 概要
XLOOKUP（エックスルックアップ）は、テーブル内のデータを参照して検索するための関数です。
関数の構文
GoogleスプレッドシートのXLOOKUP関数の構文は次のとおりです。
=XLOOKUP(検索キー, 検索範囲,結果の範囲)
| 引数 | 説明 |
|---|---|
| 検索キー | 検索する値。 |
| 検索範囲 | ［検索キー］で指定したデータを探しに行く表の範囲を指定します。 指定した［検索キー］がある列の範囲を指定します。 |
| 結果の範囲 | 表示させたい結果がある列の範囲を指定します。［検索範囲］の指定セル数とここでのセルの数が一致するように指定します。 |
| 見つからない場合の値 | ※省略可 範囲に検索キーのデータが見つからない場合に何を表示させるかを指定します。 |
| 一致モード | ※省略可 完全一致か近似一致かを指定します。 省略または「0」を指定すると完全一致で検索します。 「1」は完全一致または検索キーより大きい値も一致とします。 「-1」は完全一致または検索キーより小さい値も一致とします。 |
| 検索モード | ※省略可 上から検索するか下から検索するかを指定できます。 省略または「1」で指定すると上からデータを検索します。 「-1」を指定すると下からデータを検索します。 ［検索キー］が複数存在した場合に「1」なら上の値が表示され、「-1」だと下の値が表示されます。 |
関数の読み方
読み方：エックスルックアップ
