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 (2023/12/06 09:37:45時点 Amazon調べ-詳細)

created by Rinker
翔泳社
¥2,310 (2023/12/06 13:23:23時点 Amazon調べ-詳細)

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

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

ピックアップ記事

  1. Amazonプライム・ビデオで今見ておくべき作品
  2. 【Amazon】「30日間無料体験」絶対おすすめ!アマゾン プライム会員。買い物…
  3. [iPhone] Safariでブラウズ中に表示される、「警告! あなたのApp…
  4. Scratch (スクラッチ)を ちょっとだけ勉強して、かんたんな作品『のっティ…
  5. 【Google】Chrome 拡張機能「Password Checkup」導入翌…

関連記事

  1. Excel連番 データが入力されたら連番をふる
  2. Photo by chuttersnap on Unsplash
  3. Word2010 クイックアクセスツールバー

    Access

    [OFF2010] 使ってますか?クイックアクセスツールバー

    こんにちは。Office2010にもようやくなれてきましたが、つい…

  4. Excel連番 1行おき

    Excel

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

    Excel連番チクルス 第5夜さてさて、今回は1行おき、ある…

LINE公式アカウント:OfficeTAKU

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

最近の記事

  1. AirPods Pro 第2世代

人気の記事

  1. Photo by Samuel Zeller on Unsplash
  1. WebService

    【Amazon】「30日間無料体験」絶対おすすめ!アマゾン プライム会員。買い物…
  2. Security

    【注意】Microsoft Defender を騙った広告「セキュリティ上の理由…
  3. Word

    【Word】「文字の均等割り付け」を上手に使って見栄えの良い文書に!
  4. 書籍

    集中して仕事がしたい、自分で時間をコントロールしたい、と思い、読んでためになった…
  5. Facebook「追悼アカウント管理人」

    Facebook

    【Facebook】フェイスブック「追悼アカウント管理人」の指定方法と各SNSの…
PAGE TOP