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/01/23 17:38:01時点 Amazon調べ-詳細)

created by Rinker
翔泳社
¥2,310 (2021/01/23 18:28:46時点 Amazon調べ-詳細)

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

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

ピックアップ記事

  1. Apple Music や Amazon Musice でクラシック音楽の楽曲・…
  2. 3.11復興とは?問うドキュメンタリー:メディアが語らない真実「被災地の水産加工…
  3. [レビュー] レノボ 「TAB4 8 Plus」 画面が美しく動画もマンガも満喫…
  4. [WordPress]ロリポップサーバーで不要になったWordPressサイトを…
  5. [Amazon] アマゾンで「NHKこどもパーク」開始!月額390円で「ワンワン…

関連記事

  1. Excel連番 1行おき

    Excel

    [Excel] エクセル連番チクルス Vol.5 一行おきに連番を作成する

    こんにちは。暑さと数字に弱い @OfficeTAKUです。…

  2. Excel

    [Excel] 計算結果の「0(ゼロ)」を表示したくない時の2つの方法

    こんにちは。@OfficeTAKUです。エクセルでつくった…

  3. Photo by Samuel Zeller on Unsplash

    Excel

    [Excel] エクセルで目標日までのカウントダウンを表示する

    こんにちは。@OfficeTAKUです。Excelでは日付…

  4. Excel

    [Office 2007] マクロを利用する:[開発]タブをリボンに追加

    Office 2007 の Word、Excel などでマクロを利用す…

LINE公式アカウント:OfficeTAKU

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

最近の記事

人気の記事

  1. Surface Mobile Mouse
  1. Security

    【iPhone】 メッセージに「タカヒロ」Ta-KA-1208…からの迷惑メール…
  2. Windows

    Surface Go予約開始 !でも、 搭載されているOS Windows 10…
  3. WebService

    医療情報管理アプリ NOBORI が凄い!CT画像も検査結果もスマホですぐに確認…
  4. hipstersound.com

    WebService

    Hipstersound:集中して勉強や仕事ができる環境音を提供してくれるおすす…
  5. Security

    [Instagram] インスタグラムで勝手にレイバンサングラスの投稿がされたら…
PAGE TOP