Excel

【Excel】XLOOKUP関数(完全一致検索編):VLOOKUP関数の進化系(Excel 2019・365)


こんにちは。
@OfficeTAKUです。

ご存知のことと思いますが、Excelのバージョンが上がる度にExcelの関数も増えています。
今回は Excel 365 、Excel 2019 から追加された関数、XLOOKUP関数についてです。

XLOOKUP関数かなり進化しています。
まずは、従来のVLOOKUP関数の代わりとして使う方法。
今回は完全一致検索の場合です。

XLOOKUP 関数 – Office サポート

【Excel環境】
サブスクリプション:Microsoft 365 for business

  • Microsoft Excel for Mac バージョン16.46
  • Microsoft Excel for Microsoft 365 MSO 16.0

XLOOKUP関数

【XLOOKUP関数】
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
(大かっこ[]の引数はオプション・省略可)

引数 説明
検索値 従来と同じ検索したい値を設定
検索範囲 検索する範囲or配列
戻り範囲 結果として返したい範囲or配列
[見つからない場合] 検索値に一致するものが見つからない場合に表示したい文字列を入力
[一致モード] 0 – 完全一致:これがデフォルト(既定値)。
-1 – 近似値:見つからない場合は、検索値未満の最大値が返されます(VLOOKUP関数で近似値検索の時に指定したTRUEに該当)。
1 – 近似値:見つからない場合は、検索値より大きい最小値が返されます。
2 – *、?、および 〜 が特別な意味を持つワイルドカードの一致。
1 – 先頭の項目から検索を実行。これがデフォルト(既定値)。
-1 – 末尾の項目から逆方向に検索を実行。
2 – 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行。並べ替えられていない場合、無効な結果が返されます。
-2 – 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行。並べ替えられていない場合、無効な結果が返されます。

XLOOKUP関数でコードを検索し商品名・単価を表示させる

下図のような売上表を作成してみましょう。
左の「売上表」のコード欄にテキストのコードを入力すると「テキスト名」と「単価」が表示される、というものです。
1画面に収めるために商品一覧である「コードテーブル」を別シートにせず隣に作っています。

【数式:セルC4、セルD4】
=XLOOKUP(B4,$H$4:$H$14,$I$4:$J$14,””)

セルC4に上記の数式を入力すると自動的に セルD4 にも数式が入力されます。
これは「スピル(spill)」という機能が働くからです。感覚としてはフラッシュフィルににているかな。
「spill」という単語は「こぼす」とか「流す」といったいみになります。
「スピル」についてはまた別の機会に。

関数の引数ダイアログボックス(イメージ):Windows

関数の引数ダイアログボックスの入力例
注意:画像合成してあります。実際はダイアログボックス内でスクロールして下さい。

数式パレット作業ウインドウ(イメージ):Mac

数式パレットの入力例
注意:画像合成してあります。実際は数式パレット内でスクロールして下さい。

ポイントとしては、

  • 検索範囲
    コードテーブルのコードが入力されているセル範囲H4:H14(コピーのために絶対参照)
  • 戻り範囲
    今回の場合は「テキスト名」と「単価」が入力されているセル範囲 I4:J14 を選択しています。
    これは、「スピル」という機能を利用して「単価」の列に自動的に数式を入力するためです。
  • [見つからない場合]
    コードが未入力の時には空白を表示したいので “” (ダブルコーテーション)を2つ入れています。
    場合に応じて、「該当なし」とか「見つかりません」などという風にメッセージを入力しても構いません。
    VLOOKUP関数であれば、IF関数を利用したりしていましたが、それが必要なくなりました。便利です。
    省略するとエラー[#N/A]が表示されてしまいますので注意が必要です。
  • [一致モード][検索モード]
    今回の場合は省略です。

完成例

XLOOKUP関数とVLOOKUP関数での数式比較

XLOOKUP関数で作成した数式と従来のVLOOKUP関数を利用した数式を比較してみましょう。
未入力あるいは検索値に該当するものがない場合(エラーの時)は空白を表示する、という設定です。

【数式:セルC4】

=XLOOKUP(B4,$H$4:$H$14,$I$4:$J$14,””)

=IF(ISERROR(VLOOKUP(B4,$H$4:$J$14,2,FALSE)),””,VLOOKUP(B4,$H$4:$J$14,2,FALSE))

まとめ

Excel 365,2019で追加された XLOOKUP 関数

従来のVLOOKUP関数を利用するよりもスッキリと便利に使えます。

XLOOKUP関数はまだまだいろいろな使い方ができます。
一つずつできることを増やしていきましょう。

この関数も下位バージョンでは利用できません。
会社や学校で、Excel2016以前が使用されている状況、ファイルをやり取りする必要がある環境では使わない方がいいですね。

created by Rinker
富士通エフ・オー・エム
¥2,310 (2021/05/19 00:22:59時点 Amazon調べ-詳細)

created by Rinker
翔泳社
¥2,310 (2021/05/19 07:33:58時点 Amazon調べ-詳細)

【Excel】IFS関数:ネストしなくてよいIF関数の進化系(Excel 2019・365)前のページ

【Excel】XLOOKUP関数(近似値検索編):VLOOKUP関数の進化系(Excel 2019・365)次のページ

ピックアップ記事

  1. [WordPress]ロリポップサーバーで不要になったWordPressサイトを…
  2. サイゼリヤも採用!割れにくいワイングラス・デカンタは透明性の高いトライタン樹脂性…
  3. 災害時に役立つTwitterアカウント、情報サイト等まとめ
  4. [Facebook] プレビュー機能の脆弱性でトークン流出!アカウントの乗っ取り…
  5. 【iPhone】音声読み上げを利用する方法

関連記事

  1. Excel

    [Excel] 数式や項目名以外のセルの値を一気に削除する

    こんにちは。@OfficeTAKUです。Excelの表を再…

  2. Excel2013[オプション]ダイアログボックス

    Access

    [OFF 2013] マクロが使えるように[開発]タブを表示する

    こんにちは。@OfficeTAKUです。IEの騒ぎもひと段落し…

  3. Excel add-in
  4. Excel連番 データが入力されたら連番をふる
  5. Excel

    【Excel】XLOOKUP関数(近似値検索編):VLOOKUP関数の進化系(Excel 2019・…

    こんにちは。@OfficeTAKUです。ご存知のことと…

LINE公式アカウント:OfficeTAKU

OfficeTAKU Line公式アカウントバナー

最近の記事

  1. ロジクール MX KEYS(MAC用)

人気の記事

  1. ロジクール MX KEYS(MAC用)
  1. WebService

    【Instagram】インスタグラム追悼アカウント リクエスト
  2. Web

    2018年7月 の人気記事はこれだ!月間 アクセス数 ベスト10
  3. タウンページ2018.9『北陸とらいあんぐる』

    電子書籍

    『北陸とらいあんぐる』が表紙に描かれたNTTタウンページが昨日届いたので無料で読…
  4. Lenovo Mirage Camera with Daydream

    Hard・Acc

    [レビュー] 手軽にVR動画を撮影できる Lenovo Mirage Camer…
  5. Watch

    【レビュー】Apple Watch Series 3 がやってきた!開封の儀と3…
PAGE TOP