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

2020年２月、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の引数は以下のようになっています。

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

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

=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を使用することで、下から検索することが可能になるため、仮に２つ目を求める場合にはCOUNTIFなどを使用せずに書くことができます。

（ただ、３つあるうちの２つ目などは難しいため、そこまで汎用的に使えるわけではないですが）

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

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

これを実現する場合、 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などのバージョンでは使えないので、どうしてもの場合は自作関数などに頼る必要がある