まずは下の動画を見てね。
VLOOKUP関数のようにCOLUMN関数をを組み合わせることなくこのXLOOKUP関数ひとつで
IDを検索値にして、簡単に複数の列を抽出することが可能だよ。
LINE登録後にアンケートに回答すると無料でGETできる4つの特典
- 特典１：効率を上げるPCショートカット288選
- 特典２：超有料級限定Excel講義動画約480分
- 特典３：プロが教える99%の人が知らないExcel学習の秘訣講義
- 特典４：LINE限定ユースフルのオンライン講座割引クーポン
書式
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
| 検索値 | 検索する値は省略した場合、XLOOKUPは検索範囲に空白のセルを返します。 |
| 検索範囲 | 検索する配列または範囲 |
| 戻り範囲 | 返す配列または範囲 |
| [見つからない場合] | 有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。 有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。 |
| [一致モード] | 一致の種類を指定します |
| 0 | 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。 |
| -1 | 完全一致。 見つからない場合は、次の小さなアイテムが返されます。 |
| 1 | 完全一致。 見つからない場合は、次の大きなアイテムが返されます。 |
| 2 | *、?、および 〜 が特別な意味を持つワイルドカードの一致。 |
| [検索モード] | 使用する検索モードを指定します。 |
| 1 | 先頭の項目から検索を実行します。 これが既定の設定です。 |
| -1 | 末尾の項目から逆方向に検索を実行します。 |
| 2 | 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
| -2 | 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
VLOOKUP関数とはどう違う？
ここに商品マスタがあります。例えばA003の品番を元にして商品名を抽出する場合、
VLOOKUP関数だと、=VLOOKUP(検索値にIDのB３を選択し範囲を選択したら列番号は
左から数えて２列目なので、２と入力をして完全一致の FALSEを選択してEnterで確定します。
IDを入れると商品名が表示されますが、IDを入れないとエラーで返ってきます。このエラーを解消するために
IFERROR関数と組み合わせてID番号が見つからない場合、空白にするという数式を入れなければなりません。
=IFERROR(VLOOKUP(B3,B11:D18,2,FALSE),"")
では、XLOOKUP関数では？
では、XLOOKUP 関数ですが=XLOOKUP 検索値はIDのB７を選択します。
ここまでは同じです。カンマで区切ったら検索範囲にVLOOKUPでは全範囲選択しましたが、XLOOKUPでは
どこから検索をしたいかここでいうと、IDの列なのでB11:B18を範囲選択しカンマで区切ります。
戻り範囲はそれをどこから引っ張ってきたいのか、ここでいうと商品名なので範囲選択をして
カッコで閉じてEnterキーを押します。これだけで検索することができました。
VLOOKUP関数だと範囲で選びましたが、XLOOKUP関数だと１つづつ選べるようになっています。
わざわざ列番号が何列目にあるのかを数えて入力する必要もなく、また完全一致を意味するFALSEと入れる必要もありません。
XLOOKUPだと、デフォルトがFALSEになっているからです。
注意点は、XLOOKUPは必ず検索範囲と戻り範囲の行数を同じにしてあげることです。
例えば、検索範囲が3行、戻り範囲が4行になるとエラーになるので注意してください。
=XLOOKUP(B7,B11:B18,C11:C18)
IDが見つからない場合は？
IDが見つからない場合はどうなるかというと、もちろん何もI Dを指定しない場合はエラーが出ます。
VLOOKUPの時のようにIFERROR関数をわざわざ入れなくても、
範囲選択したC１１からC１８のあとにカンマを入れて第４引数に見つからない場合とあるので、
ダブルクォーテーションを２回入力してカッコで閉じて確定します。
そうすると、エラー表示を回避できるようになります。たったこれだけで
他の関数と組み合わせる必要はありません。
=XLOOKUP(B7,B11:B18,C11:C18,"")
検索値は？
次に、VLOOKUP関数では検索範囲は左しか検索ができないため
商品名、単価を検索対象に指定することは不可能です。そのため、例えば商品名を元に検索したい場合は
列を（商品名とID）入れ替える必要があります。
XLOOKUPの場合は、そうではなく左でなくても真ん中の商品名を元にしてIDを検索することが可能です。
入力してみると=XLOOKUP( 検索値に商品名のC3を指定して
どこから検索したいのか、商品名を範囲選択して戻り範囲にそれをどこから引っ張ってきたいのか、
IDを範囲選択して、カンマで区切りダブルクォーテーションを２回入れて確定をするとパソコンを元にして
IDが検索されました。
=XLOOKUP(C7,C11:C18,B11:B18,"")
複数のデータを一気に抽出には？
次に、複数のデータを一気に抽出する場合です。VLOOKUP関数だとCOLUMN関数を組み合わせて説明しましたが
XLOOKUPだと、これを一度で出来るのでとても便利です。
=XLOOKUP検索値はIDを選択してどこから検索をしたいか、ここでいうと、IDの列なので範囲選択をし
カンマを入れて、それをどこから引っ張ってきたいのか、さっきは戻り範囲に商品名だけを範囲選択しましたが、
複数列を範囲指定することでスピルの動作により複数列抽出することができます。
このためIDを変えると、それに合わせて一気に形成されます。スピルの動作により作業がぐーんと楽になります。
=XLOOKUP(B7,B11:B18,C11:E18,"")
どうでしたか？VLOOKUP関数の欠点をカバーしてくれるXLOOKUP関数！
便利だから使っていきたいね。
Excel2021、Excel for Microsoft365で使うことが可能だよ。
