【XLOOKUP 関数】別表から特定のデータを検索して抽出する

Posted in Mac関連, Numbers

今回は、データ範囲から指定した値を検索し、最初に見つかった一致する値に対応する項目を返してくれる、便利な XLOOKUP 関数の使い方をご紹介していきます。

なお、当サイトでは Excel ではなく Numbers を使って解説しています。

目次

XLOOKUP 関数

XLOOKUP 関数は、縦方向に検索する VLOOKUP 関数と、横方向に検索する HLOOKUP 関数の機能をカバーしています。

VLOOKUP は検索値の対象範囲が左端に位置している必要がありますが、XLOOKUP にはそのような制約はありません。

XLOOKUP 関数の式の確認

XLOOKUP 関数で必要な引数は3つで、4つ目以降の引数は省略することが可能です。

= XLOOKUP ( 検索値, 検索範囲, 返す範囲 [, 見つからない場合, 一致タイプ, 検索タイプ] )

検索値直接 または セル範囲を指定
検索範囲検索値を検索する対象のセル範囲
返す範囲検索値に対応する値を取得するセル範囲
(見つからない場合)検索値が見つからない場合に表示する値を指定
(一致タイプ) 完全一致か、近似値も検索するかを指定
(検索タイプ)検索方向を指定

関数を書く

ここでは例として、商品コードを検索値として、単価が表示されるようにしてみましょう。

Step 1

「商品名」を表示させたいセルにカーソルを合わせ、「= XLOOKUP」と入力するか、「挿入」メニューから「新規数式」を選択します。

「参照」から XLOOKUP を選択するか、検索窓に「XLOOKUP」と入力しても探すことが出来ます。「関数を挿入」をクリックします。

Step 2

検索値は、マスタ表の中で重複するものがない項目を指定しましょう。検索範囲と戻り範囲の行数は必ず揃えるのもポイントです。

検索範囲」と「返す範囲」に、「A2:A7」のように範囲を指定しても良いですが、列ごと指定しますと、あとで縦のセルに関数をコピーする必要が出た場合に参照範囲がずれません。

= XLOOKUP ( E2, A, C )

入力結果

Enter を押して式を反映させたら、指定した条件の通りになっているか確認しましょう。

↓ 「単価」の列の下のセルに関数をコピーしても、参照範囲がずれません。

※ 検索項目が列の縦位置の場合も、式の設定方法は同じです。

絶対参照を設定する場合

「A2:A7」のように、行の開始と終了を設定した場合は、他のセルに関数をコピーしたときに参照範囲がずれないように、絶対参照と相対参照を設定します。

POINT

・絶対参照:参照先のセルを固定
・相対参照:参照先のセルが数式に連動して変化

関数の中で指定しているセルの部分の「▼」をクリックすると設定窓が開きますので、チェックボックスにチェックを入れます。

範囲の開始と終了をそれぞれ設定することができ、チェックを入れると「$」のマークがつきます。

↓ 「単価」の列の下のセルに関数をコピーしても、参照範囲がずれません。

ここで1つめのポイントです。VLOOKUP関数では[列番号]となっている第3引数が、XLOOKUP関数では[戻り範囲]となっています。左から順に列番号を数える必要がなく、値を取り出すセル範囲を直接指定すればいいので、XLOOKUP関数のほうが分かりやすいのではないでしょうか。

ただし、注意点もあります。XLOOKUP関数では、[検索範囲]と[戻り範囲]で指定するセル範囲は、同じ高さにしてください。例えば「=XLOOKUP(A2,A6:A105,B6:B1000)」のような指定はNGです。

Scroll to top