今回は、データ範囲から指定した値を検索し、最初に見つかった一致する値に対応する項目を返してくれる、便利な 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」のように、行の開始と終了を設定した場合は、他のセルに関数をコピーしたときに参照範囲がずれないように、絶対参照と相対参照を設定します。
・絶対参照：参照先のセルを固定
・相対参照：参照先のセルが数式に連動して変化
関数の中で指定しているセルの部分の「▼」をクリックすると設定窓が開きますので、チェックボックスにチェックを入れます。
範囲の開始と終了をそれぞれ設定することができ、チェックを入れると「$」のマークがつきます。
↓ 「単価」の列の下のセルに関数をコピーしても、参照範囲がずれません。
ここで1つめのポイントです。VLOOKUP関数では［列番号］となっている第3引数が、XLOOKUP関数では［戻り範囲］となっています。左から順に列番号を数える必要がなく、値を取り出すセル範囲を直接指定すればいいので、XLOOKUP関数のほうが分かりやすいのではないでしょうか。
ただし、注意点もあります。XLOOKUP関数では、［検索範囲］と［戻り範囲］で指定するセル範囲は、同じ高さにしてください。例えば「=XLOOKUP(A2,A6:A105,B6:B1000)」のような指定はNGです。