検索方向を指定できる
VLOOKUP関数、HLOOKUP関数では、それぞれ「上から」「左から」検索を行いますが、XLOOKUP関数では「下から」「右から」という方向で検索を行うことができます。検索方向は検索値と対応するデータが1つだけであれば、問題ではありません。しかし、同じデータが複数存在する場合、検索方向を正しく指定しないと誤った結果が表示されてしまいます。
たとえば、以下のような商品リストで2024年の価格の下にさらに2025年が追記されている場合を考えます。
たとえば、「商品D」という名称は、2024年と2025年の2つ存在します。検索方向を何も指定しなければ、検索範囲の上から順に検索していきます。
=XLOOKUP(G3,$B$3:$B$12,$C$3:$C$12)
その結果、表示される価格は2024年の価格になります。
ただし、一般的には最新の価格が表示される方が望ましいです。そこで、下から検索するように方向を指定します。「検索モード」に「-1」を指定すれば、検索範囲の下からデータを検索することができます。
=XLOOKUP(G3,$B$3:$B$12,$C$3:$C$12,,,-1)
これで2025年の価格を表示できるようになります。
理想的にはデータの重複はない方が望ましいですが、追加・更新が頻繁に行われるようなデータについてはこの機能を活用し、正確なデータを表示させることができます。
なお、重複する検索値に対応するデータをすべて表示させるには、FILTER関数を使う方法もあります。
複数の列・行を表示できる
XLOOKUP関数では、戻り範囲に複数の列・行を選択することで、複数の列・行のデータを表示させることができます。
たとえば、以下のような商品リストから価格・製造元・保証期間の3列のデータを表示させます。
XLOOKUP関数の戻り範囲に商品リストの価格・製造元・保証期間の3つの列を選択します。
=XLOOKUP(H3,$B$3:$B$12,$C$3:$E$12)
これで3つのデータを一気に表示させることができます。
この数式はExcelのスピルという機能を活用しており、セルI3の数式を修正すれば、その結果が他の列にも反映されるため、修正を効率的に行うことができます。
複数の条件で検索できる
XLOOKUP関数では、「○○かつ××」というように複数の条件を指定して検索することができます。
たとえば、以下のような商品リストで商品名が同じでも「プレミアム」「スタンダード」「ライト」というように種類が異なる場合があります。そのような場合、「商品Aかつプレミアム」というように複数の条件で検索する必要があります。
XLOOKUP関数で複数条件を検索するには、検索値を「1」として、検索範囲には「(条件範囲1=条件1)*(条件範囲2=条件2)」とします。Excelでは、「条件範囲＝条件」の数式で条件が一致する場合、「1（TRUE）」、不一致の場合「0（FALSE）」と扱われます。この性質を活かし、すべての条件に一致する場合「1*1=1」となり、検索値と一致します。
=XLOOKUP(1,($B$3:$B$11=G3)*($C$3:$C$11=H3),$D$3:$D$11)
これで、商品名・種類の両方が一致する商品の価格が表示されます。
なお、VLOOKUP関数で同じように複数の条件で検索する場合、商品リストに商品名と種類を「&」で接続した列を作成する必要があります。さらに検索値も「商品名&種類」というように指定する必要があります。
=VLOOKUP(H3&I3,$D$3:$E$11,2,FALSE)
これでは、列を追加する手間が発生し、表に余計な列が入ってしまいます。XLOOKUP関数では元の表を加工することなく、複数の条件で検索することができます。
クロス表から検索できる
上記の通り、XLOOKUP関数では垂直・水平の両方向の検索が可能です。これを活用して、以下のような縦軸に商品名、横軸に会社を並べたクロス表から検索することができます。
たとえば、商品Cのメロン社の価格「\4,050」を検索します。商品名を検索するためのXLOOKUP関数の戻り範囲に、会社を検索するXLOOKUP関数を追加しています（ネスト）。商品名はA列の商品名から検索、会社名は2行目の会社名から検索し、その両方に一致する価格を表示させます。
=XLOOKUP(G3,$A$3:$A$12,XLOOKUP(H3,$B$2:$D$2,$B$3:$D$12))
これで商品Cのメロン社の価格「\4,050」を正しく表示させることができます。
数式を下にコピーすれば、他の商品の価格も正しく表示されます。
VLOOKUP関数でもMATCH関数を組み合わせることで、クロス表からの検索が可能です。MATCH関数で2行目を検索し、会社名が何列目にあるかを特定しています。しかし、2つの関数を使用する必要があり、指定方法も少しわかりづらいという欠点があります。
=VLOOKUP(G3,$A$3:$D$12,MATCH(H3,$A$2:$D$2,0),FALSE)
| MATCH(検索値, 検索範囲, [照合の種類]) |
| 範囲内のセルと行を指定してその値を取得する。照合の種類は「1」以下、「0」完全一致、「-1」以上から選択することができる。 |
それに対してXLOOKUP関数は1つの関数で直感的に、縦方向、横方向の範囲を指定できるので、簡単でミスも少ないかと思います。
XLOOKUP関数を利用する上での注意点
上記の通り、XLOOKUP関数はVLOOKUP関数、HLOOKUP関数よりも柔軟な検索ができるという点で非常に利便性が高いです。一方で、Microsoft 365、Excel2021以降のバージョンでしか使えません。人によっては古いバージョンのExcelでファイルを開く可能性もあるため、ファイルを共有する相手の環境を確認した上で利用する必要があります。
個人的な見解としては、原則どのようなバージョンでも機能するVLOOKUP関数を使用し、XLOOKUP関数でないと難しいデータにのみ使用するのが良いかと思います。
コメント