ExcelのVLOOKUP関数にサヨナラを ～圧倒的に便利な「XLOOKUP関数」をOffice 2021で利用しよう！

VLOOKUP関数は［列番号］の指定がややこしい

VLOOKUP関数の引数は、［検索値］［範囲］［列番号］［検索の型］

　VLOOKUP関数で悩ましいのが「引数（ひきすう）」の指定方法です。特に取得対象を指定する［列番号］は、数え間違えのほか、参照する表が変更されると［列番号］をその都度修正する必要がありました。

XLOOKUP関数なら範囲を直接指定できるので簡単

　その点、XLOOKUP関数は、検索の対象範囲と対応する値を取得する対象範囲をそれぞれセル範囲で指定可能です。まずはXLOOKUP関数の構文を見てみましょう。

XLOOKUP関数の引数は、［検索値］［検索範囲］［戻り範囲］［見つからない場合］［一致モード］［検索モード］の6つ。ただし、必須の引数は最初の3つのみなので安心して欲しい

XLOOKUP関数の引数が意味するところ

　XLOOKUP関数の引数は6つありますが、必須の引数は最初の3つ。4つめ以降の引数は省略可能です。それぞれの引数には以下の意味があります。

• ［検索値］：検索する値。直接またはセル範囲を指定する
• ［検索範囲］：検索値を検索する対象のセル範囲
• ［戻り範囲］：検索値に対応する値を取得するセル範囲
• ［見つからない場合］：検索値が見つからない場合、何を表示するかを指定する（任意）
• ［一致モード］：[検索値]と一致の判定基準を指定する。0、または省略で「完全一致」（任意）※
• ［検索モード］：検索方向を指定する。1、または省略で先頭から末尾（任意）※

※ オプション値については後述

　一般的な用途なら、最初の3つの引数［検索値］、［検索範囲］、［戻り範囲］のみの指定で問題ありません。具体例で動きを見てみましょう。

XLOOKUP関数なら［検索範囲］が左端になくてもOK

　［検索値］に指定するのは検索したい値です。引数の名前そのままですね。［検索範囲］に指定するのは［検索値］を検索する対象のセル範囲。VLOOKUP関数では検索対象のセル範囲を「左端」に固定しておく必要がありましたが、XLOOKUP関数では［検索範囲］は左端でなくてもOK。セル範囲で直接指定できます。

　以下の例では、氏名を［検索値］として、社員番号を取得します。XLOOKUP関数なら、「氏名」列より左側にある「社員番号」も検索可能です。

セルE2に入力した「氏名」を［検索値］として、対応する「社員番号」を取得する。[社員番号]列は、検索対象の「氏名」列の左側でも問題ない。セルF2に「=XLOOKUP(E2,B2:B31,A2:A31)」と入力する

該当する社員番号を取り出せた

　VLOOKUP関数のように［列番号］を数える必要がなく、セル範囲で指定するので、数式がわかりやすいのではないでしょうか。また、参照する表が変更されたとしても、XLOOKUP関数の数式は変化します。XLOOKUP関数で記述しておけば、引き継ぎファイルをメンテナンスする手間が省けますね。

「#VALUE!」エラーが出た場合はセル範囲の高さをチェック

　なお、［検索範囲］と［戻り範囲］のセル範囲の高さが異なるとエラーとなることに注意してください。試しに先ほど入力した数式を「=XLOOKUP(E2,B2:B31,A1:A31)」と変更すると、「#VALUE!」エラーとなります。

セルF2の数式を「=XLOOKUP(E2,B2:B31,A1:A31)」と変更してみる

［検索範囲］と［戻り範囲］のセル範囲の高さが異なると、#NALUE!エラーとなってしまう

「#VALUE!」エラーを避ける方法

　このエラーを避けるには、［検索範囲］と［戻り範囲］に列全体を指定してしまう手があります。数式を「=XLOOKUP(E2,B:B,A:A)」と修正してみます。

セルF2の数式を「=XLOOKUP(E2,B:B,A:A)」と修正した状態。［検索範囲］はB列全体、［戻り範囲］はA列全体が指定されている

該当する社員番号を取り出せた。［検索範囲］と［戻り範囲］の高さ違いによるエラーを避けられる

［検索値］が存在しない場合に「該当データなし」と表示させる

　ここでは、セルG2に入力した数式を「=XLOOKUP(F2,A2:A11,B2:D11,"該当データなし")」と修正し、セルF2には、商品テーブルに存在しない「C099」と指定してみます。

セルG2の数式を「=XLOOKUP(F2,A2:A11,B2:D11,"該当データなし")」と修正し、「商品No」には存在しない「C099」と指定する

4つめの引数[見つからない場合]に指定した「該当データなし」が表示された

［一致モード］はどの値を一致とするかを指定可能

　［一致モード］は［検索値］に対して、どの値を一致とするかの判定基準を指定します。オプション値は以下の通り。省略した場合は、0（完全一致）となります。

• 0：完全一致
• -1：完全一致、または次に小さい項目が一致する
• 1：完全一致、または次に大きい項目が一致する
• 2：ワイルドカードの文字と一致する

　「-1」と「1」は、近似一致として動作します。例えば、予算表などで指定した値を超えない、または超える値を一致と見なす時に利用します。「2」は「*宿」といったワイルドカードを含む文字列を［検索値］に指定し、「*宿」であれば、「新宿」や「原宿」が検索されます。参照する表の内容をある程度把握しているなら利用価値があるかもしれません。

［検索モード］で検索する方向を変更

　［検索モード］は［検索範囲］の検索方向を指定します。省略した場合は、1（先頭から末尾）となります。オプション値は以下の通りです。

• 1：先頭から末尾
• -1：末尾から先頭
• 2：バイナリ検索（昇順で並べ替え）
• -2：バイナリ検索（降順で並べ替え）

　［検索値］と一致する値が複数存在し、表の下の項目を一致させる時に「-1」とします。販売データなどでは検索の方向を指定することがあるかもしれません。「2」「－2」は高速検索する場合に利用します。このオプションを利用するには、あらかじめ［検索範囲］を昇順、または降順で並べ替えておく必要があります。数万件以上のデータがあるなら利用価値はあります。