XLOOKUPの使い方|VLOOKUPとの違いと、見つからない場合の表示を図解で解説

教員向け 出席番号から氏名を出すXLOOKUPの使い方 検索・参照関数

出席番号を入れたら、氏名と点数が出てくる表を作りたい。教員の仕事では、番号から、情報を引き出す場面があります。

ExcelのXLOOKUP(エックスルックアップ)関数は、検索値に合う行から、別の列の値を取り出します。この記事では、教員の仕事でよく出る例を使って、書き方を説明します。XLOOKUPが使えるのは、Excel 2021以降とMicrosoft 365です。

教員の仕事でXLOOKUP関数を使う場面|出席番号から氏名を出す

表の上のアルファベット(A、B、C…)が列、左の数字(1、2、3…)が行です。「E2」は、E列の2行目のセルのことです。

ABCDEF
1出席番号氏名算数入力する番号氏名
21青木722伊藤
32伊藤88
43上田60

F2に次の式を入れます。E2に番号を入れると、その番号の氏名が出ます。

=XLOOKUP(E2,A2:A4,B2:B4,"該当なし")

該当する番号がないときは、「該当なし」と出ます。

ここからは、XLOOKUP関数の書き方を順に見ていきます。

XLOOKUP関数の基本の書き方

XLOOKUP関数は、「検索範囲」から検索値を探し、「戻り範囲」の同じ位置にある値を返します。

  • 検索値:探したい値(コードや名前など)
  • 検索範囲:検索値を探す列(1列だけを指定します)
  • 戻り範囲:結果として取り出したい列
  • 見つからない場合:検索値がないときに表示する値(省略できます)
XLOOKUP関数で、商品コードから商品名を検索し、見つからない場合に未登録と表示する式を示した図
  1. 結果を表示したいセル(E2)を選び「=XLOOKUP(」と入力する
  2. 検索値(D2)を選び、カンマを入れる
  3. 検索範囲(A2:A5)をドラッグして選び、カンマを入れる
  4. 戻り範囲(B2:B5)をドラッグして選び、カンマを入れる
  5. 見つからないときの表示「”未登録”」を入力し、「)」で閉じて Enter を押す

見つからない場合の表示を指定する

VLOOKUPでは、探している値がないと「#N/A」が表示され、消すにはIFERRORを組み合わせる必要がありました。XLOOKUPは4つ目の引数で指定するだけです。

4つ目の引数を省略すると、VLOOKUPと同じように「#N/A」が表示されます。空白にしたいときは、「””」と入力します。

=XLOOKUP(D2,A2:A5,B2:B5,"")

検索値より左の列を取り出す

VLOOKUPは、検索値の列より右にある列しか取り出せません。XLOOKUPなら、検索範囲と戻り範囲を別々に指定するので、左側の列も取り出せます。

XLOOKUP関数で、右の列の商品名から左の列の商品コードを検索する式を示した図

VLOOKUPとの違い

比較項目VLOOKUPXLOOKUP
列の指定列番号(数える必要あり)戻り範囲を直接選ぶ
左の列の取得できないできる
検索方法の初期値近似一致(FALSEの指定が必要)完全一致
見つからない場合#N/Aになる(IFERRORが必要)4つ目の引数で指定できる
対応バージョンすべてMicrosoft 365・Excel 2021以降

XLOOKUPが使えないとき

Excel 2019以前では、XLOOKUPは使えません。式を入力すると「#NAME?」と表示されます。ファイルを共有する相手のバージョンが古い可能性があるときは、VLOOKUPやINDEX+MATCHを使うほうが安全です。

もう一歩進める|XLOOKUPの便利な引数

XLOOKUP関数には、基本の3つの引数のほかに、省略できる引数がいくつかあります。知っておくと、VLOOKUPでは難しかった検索ができるようになります。

一致モードで検索の方法を変える

4つ目の引数「一致モード」を指定すると、完全一致以外の探し方ができます。

  • 0:完全一致(省略したときの初期設定)
  • -1:完全一致。なければ次に小さい値
  • 1:完全一致。なければ次に大きい値
  • 2:ワイルドカード(*や?)を使った検索

成績の段階や料金表のように、「ちょうどの値がなければ、近い値で判定したい」ときに便利です。

検索の向きを変える

5つ目の引数「検索モード」では、検索の順番を変えられます。「-1」を指定すると、下から上に向かって探すため、同じ値が複数あるときに「最後に出てくるもの」を取り出せます。

複数の列を一度に返す

戻り値の範囲を2列以上にすると、結果が隣のセルにもあふれて表示されます(スピル)。たとえば、商品コードから商品名と単価の2つを、1つの式で同時に取り出せます。

スピルを使うには、結果があふれる先のセルが空いている必要があります。セルが埋まっていると#SPILL!エラーになるので、先に空けておきましょう。

横に並んだ表や複数条件での使い方

XLOOKUPは、縦に並んだ表だけでなく、横に並んだ表でも使えます。VLOOKUPの横版にあたるHLOOKUPを別に覚えなくても、同じ書き方で検索できます。

=XLOOKUP(A2,B1:G1,B2:G2)

1行目の見出しから探して、2行目の値を返す式です。

また、「商品名と色の2つが一致する行」のような複数条件の検索は、検索値と検索範囲を「&」でつなぐ方法で実現できます。たとえば検索値をA2&B2とし、検索範囲を商品列&色列とします。

大量のデータを検索するときの工夫

データが数万行あるときは、検索の範囲を必要な行だけに絞ると、計算が速くなります。列全体(A:A)を指定する方法は手軽ですが、行数が多いと再計算に時間がかかることがあります。

  • 範囲は、データがある行までを指定する(A2:A5000など)
  • 表を「テーブル」にして、列の名前で範囲を指定すると、データが増えても範囲が自動で広がる
  • 検索する列と、取り出す列は、同じ行数にそろえる

XLOOKUP関数を使うときの注意

  • 古いExcelでは使えません。学校のパソコンのバージョンを、先に確認します。
  • 古いExcelでは、VLOOKUPや、INDEXとMATCHを使います。

あわせて読みたい

コメント

タイトルとURLをコピーしました