「XLOOKUP」を2019、2016,2013などで使えるようにしてみた

2020年2月、Office365のアップデートで注目の新関数「XLOOKUP」が実装されました。

おそらくEXCEL関数の中では、SUMやIFなどと並んで最も多く使われていそうなVLOOKUPを拡張した関数で、今後のEXCELでもよく使われる関数となりそうです。

しかしながら、現在は使用できるのは「Office365」のみでありOffice2019、Office2016、Office 2013などの旧バージョンでは使用できません

そのことは以下ページにて記載されています。

https://techcommunity.microsoft.com/t5/excel-blog/announcing-xlookup/ba-p/811376

XLOOKUP and XMATCH are now available to Office 365 Subscribers in the Monthly Channel and will be available to users in the Semi-Annual Channel later this year.

そこで記事の前半ではXLOOKUPの便利さを記載し、記事後半では旧バージョンでXLOOKUPの動きを再現する方法を記載してみたいと思います。

「XLOOKUP」を使ってみよう

まだ最新のOffice365が手元にない場合には、まずはOffice365をアップデートする必要があります。

ファイル→アカウント→更新オプションから「今すぐ更新」をクリックして、アップデートを行いましょう。

アップデートが完了して、EXCELを再起動すると、XLOOKUPが関数として使用できるようになります。

XLOOKUPの引数は以下のようになっています。

引数省略指定する値備考
検索値不可検索する文字を指定するVLOOKUPの検索値と同じ
検索範囲不可検索する文字が存在する範囲を指定する。
戻り範囲不可返却したい文字が存在する範囲を指定する複数列指定可
見つからない場合検索して見つからなかった場合の返却値
一致モード完全一致か部分一致かを指定VLOOKUPと同じだが初期値は完全一致
検索モード検索順を指定初期値は先頭から

例として、下記の表から品名をキーに所有数を取得したいとします。

その場合の式は以下のようになります。

=XLOOKUP("りんご",B2:B4,A2:A4)

上記の式を見て分かる通り、XLOOKUPを使用すると検索列よりも左にある列を検索結果として返却することができます

このような使い方をしたいことはかなり多かったのですが、今まではINDEXやMATCHなどを組み合わせなければ取得できませんでした。

そして INDEXやMATCHを使用した書き方は、直感でわかりにくい書き方になってしまうため、 XLOOKUP でスマートに書けるようになったのは大変助かります。

また、検索範囲と戻す範囲を柔軟に決められるため、HLOOKUPの代わりにもなります。

ここがすごいぞ「XLOOKUP」

前述のとおり、XLOOKUPを使うことで、VLOOKUPではできなかった検索列の左側にある値を戻すことができます。

その他にも使用するメリットが盛りだくさんです。

見つからなかったときの値を指定できる

VOOKUPでは、検索値が見つからなかった場合にエラーとなってしまうため、エラーだったら別の値を返すという処理をする必要がありました。

例えば、以下のようにします。

=IFERROR(VLOOKUP("ぶどう",B11:D11,B10:D10,FALSE),"見つかりません")

IFEERORが登場したことで、昔のIF文で分岐させるやり方より相当簡単にかけるようになりましたが、ネストされているので可読性は少し低めです。

XLOOKUPの場合、 XLOOKUP自体に見つからなかったときの文字列を指定できるのでネストなしで記述することができます。

=XLOOKUP("ぶどう",B11:D11,B10:D10,"見つかりません")

検索順を指定することができる

上記の表の品名から値段を求める時、VLOOKUP関数では”150″が取れてきてしまい”200″を取ることができません。

2つ目の”200″取ってきたい場合は、COUNTIFなどを使用して範囲を絞り込む方法が有効です。

下記サイトの一番下の方にある方法ですね。

https://qiita.com/AquaMeria/items/a4ffeff03b81e7d03abb

サンプルシートでは、2つめの商品がある場合のみVLOOKUPを実行するようにIF関数で制御
=IF(COUNTIF(E2:E13,A2)<2,"",VLOOKUPで検索)

XLOOKUPを使用することで、下から検索することが可能になるため、仮に2つ目を求める場合にはCOUNTIFなどを使用せずに書くことができます。

(ただ、3つあるうちの2つ目などは難しいため、そこまで汎用的に使えるわけではないですが)


異なる位置にある表を指定できる

どういうことかというと、下記の表のように場所が異なるものでも検索することができるということです。

これを実現する場合、 INDEXやMATCHなどを組み合わせなければ取得できなかったのですがXLOOKUPで簡単に取得することができます。

「こんな使い方する場合あるのか?」と言われそうですが、意外とあるかなあと思っています。DBなどの結果などでWorkSheetごとに別の表があるパターンです。

今まで実現するために頭を悩ませてしまいそうでしたが、XLOOKUPの柔軟性を使えば複雑なことも楽々実装できそうですね。

2007、2010、2013、2016 など旧バージョンでは使えない

私は、私用では Office365を使っているので問題ないですが、職場によってはまだ 2007、2010、2013、2016などのバージョンを使用している場合があるかと思います。

そんな時、「くっそー、なんでXLOOKUPが使えないんだよお」ってなりそうな気がしたので、自作関数で作ってみました。

Function XLOOKUP_JISAKU(検索値 As Variant, 検索範囲 As Range, 戻り範囲 As Range, Optional 見つからない場合 As String = "") As String
    Dim searchWord As String
    Dim targetRow As Long
    
    On Error GoTo errproc
    
    '検索値が単一セルでない場合はエラー
    If 検索値.Cells.Count <> 1 Then
        XLOOKUP_JISAKU = CVErr(xlErrValue)
        Exit Function
    End If
    
    searchWord = 検索値.Value
    
errproc:
    If searchWord = "" Then searchWord = 検索値
    
    On Error GoTo errproc2
    
    '検索範囲内に検索値があるか確認
    targetRow = 0
    For Each c In 検索範囲
        If c.Value = searchWord Then
             targetRow = c.Row
             Exit For
        End If
    Next c
    
    '検索範囲内に検索値がない場合はエラー
    If targetRow = 0 Then
        If XLOOKUP_JISAKU <> "" Then
            XLOOKUP_JISAKU = 見つからない場合
        Else
            XLOOKUP_JISAKU = CVErr(xlErrValue)
        End If
        Exit Function
    End If
    
    '返却値を検索
    XLOOKUP_JISAKU = ""
    For Each c In 戻り範囲
        If c.Row = targetRow Then
             XLOOKUP_JISAKU = XLOOKUP_JISAKU & IIf(XLOOKUP_JISAKU = "", c.Value, "," & c.Value)
        End If
    Next c
    
    If XLOOKUP_JISAKU = "" Then XLOOKUP_JISAKU = 見つからない場合
    
    Exit Function
    
errproc2:
    XLOOKUP_JISAKU = CVErr(xlErrValue)
    Exit Function
End Function

一致モードや検索モードは実装が面倒だったので、初期値の「完全一致」、「上から検索」で実装しました。

スピルに対応する書き方がわからなかったため、複数列を戻り値に設定値にした場合はカンマ区切りで返すようにしました。

使い方

結果

サンプル

https://vbaexcel.slavesystems.com/product/XLookUP%E8%87%AA%E4%BD%9C%E3%83%86%E3%82%B9%E3%83%88.xlsm

まとめ

  • XLOOKUPは非常に多機能であるため、今後VLOOKUPなどに置き換わり主流になると思う
  • 一方で2007、2010、2013、2016などのバージョンでは使えないので、どうしてもの場合は自作関数などに頼る必要がある

12 件のコメント

  • x lookupが使えなくなり困っていましたところに、自作の凄い関数を見つけて驚きました!
    上記の関数が上級すぎて私には使い方を見ても理解が追いつきませんでした。
    お忙しいところ恐縮ですが使い方をもう少し詳しくご教授いただけないでしょうか?

  • 検索して辿り着きました。
    素晴らしいコードありがとうございます!

    1つ質問ですが、「上から検索」ではなく、「下から検索」にしたい場合は、
    どのように改造すれば良いのでしょうか?

    よろしくお願い致します。

    • 下から検索にしてみました。
      スペースが消されるので見にくいですが、「検索範囲内に検索値があるか確認」の部分を変更しています。

      Function XLOOKUP_JISAKU(検索値 As Variant, 検索範囲 As Range, 戻り範囲 As Range, Optional 見つからない場合 As String = “”) As String
      Dim searchWord As String
      Dim targetRow As Long

      On Error GoTo errproc

      ‘検索値が単一セルでない場合はエラー
      If 検索値.Cells.Count <> 1 Then
      XLOOKUP_JISAKU = CVErr(xlErrValue)
      Exit Function
      End If

      searchWord = 検索値.Value

      errproc:
      If searchWord = “” Then searchWord = 検索値

      On Error GoTo errproc2

      ‘検索範囲内に検索値があるか確認
      targetRow = 0
      If (検索範囲.Count > 1) Then
      For i = 検索範囲.Count – 1 To 0 Step -1
      If 検索範囲(i).Value = searchWord Then
      targetRow = 検索範囲(i).Row
      Exit For
      End If
      Next i
      ElseIf 検索範囲.Value = searchWord Then
      targetRow = 検索範囲.Row
      End If

      ‘検索範囲内に検索値がない場合はエラー
      If targetRow = 0 Then
      If 見つからない場合 <> “” Then
      XLOOKUP_JISAKU = 見つからない場合
      Else
      XLOOKUP_JISAKU = CVErr(xlErrValue)
      End If
      Exit Function
      End If

      ‘返却値を検索
      XLOOKUP_JISAKU = “”
      For Each c In 戻り範囲
      If c.Row = targetRow Then
      XLOOKUP_JISAKU = XLOOKUP_JISAKU & IIf(XLOOKUP_JISAKU = “”, c.Value, “,” & c.Value)
      End If
      Next c

      If XLOOKUP_JISAKU = “” Then XLOOKUP_JISAKU = 見つからない場合

      Exit Function

      errproc2:
      XLOOKUP_JISAKU = CVErr(xlErrValue)
      Exit Function
      End Function

  • 投稿時にイコールは消えちゃうんですね;;

    「If XLOOKUP_JISAKU =“” Then」の部分ですが、
    「If 見つからない場合 =“” Then」ではないですかね?

  • 上の投稿2つ分間違いなので消しておいてください;;

    「If XLOOKUP_JISAKU <>“” Then」の部分ですが、
    「If 見つからない場合 <>“” Then」ではないですかね?

    • すみません。ブログ最近確認していなかったもので、返信が完全に遅れてしまいました。
      おっしゃる通りコードのミスでした!
      コメントの修正をさせていただきました。ありがとうございます。

    • ダウンロード数が少ないファイルだと安全性が確認できないと出ると思いますが特に危険な内容ではないです。
      (中身は、この記事で記載した関数を使えるようにしたエクセルファイルです。)

  • 助かりました。
    XLOOKUPの動きを理解するにも使えますね。
    またXLOOKUPは横方向にもできる優れものです。
    その場合は24行目をc.Columnにすれば動きます。カウンターを使えば両方向で動きますね。

    • 自分横方向(HLOOKUP)はあまり使う機会がなかったので、XLOOKUPが横も対応していると気が付いてなかったです。
      ありがとうございます!
      検索範囲が横なのか縦なのか調べて切り替えるようにすればRowとColumnを切り替えるようにすれば汎用的に作れそうですね。

  • コメントを残す

    メールアドレスが公開されることはありません。

    日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)