Excel関数：XLOOKUPについて （アピリッツCREATORSナレッジベース）

今回のナレッジベースの作者
” 毛塚 孝 ”さん
プランナー。
ゲームサウンド、シナリオ、ゲームデータ作成を経験後、アピリッツに参加。
得意分野はExcelなど使ったマスターの最適化やデータ作成。
好きなゲームはボードゲーム。

XLOOKUP とは

XLOOKUP関数は、｢VLOOKUP」や｢HLOOKUP」の上位に当たる関数です。

｢VLOOKUP」や｢HLOOKUP」と同じ検索結果を求めることができ、さらに｢VLOOKUP｣ではできなかった、検索値より左のセルの値をとることも簡単にできます。

同様に、｢HLOOKUP｣ではできなかった検索値より上のセルの値をとることもできます。
設定項目も増え、工夫次第で色々な使い方ができます。
ここでは、基本的な使い方、各項目の設定のやり方やちょっとした応用を書いていきたいと思います。

ちなみに｢XLOOKUP｣はoffice365のサブスク版、office2021(買い切り)で使えます。
それ以前のExcelでは使用できないので、XLOOKUPを使う際は自分以外のExcelのバージョンに注意が必要です。

XLOOKUPの書き方

XLOOKUPでは6つの項目を設定します。後半3つは省略可能です。

=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合[省略可],一致モード[省略可],検索モード[省略可])

• 検索値：どんな内容で検索するか。
• 検索範囲：｢検索値｣のある範囲を指定。｢VLOOKUP」のように｢A1:E10｣のような範囲で指定せず、｢A1:A10｣や｢A:A｣のように列(まはた行)で指定する。
• 戻り範囲：取り出したい内容のある範囲を指定。
  • 　｢検索範囲｣と同じ幅を指定する。例えば検索範囲を｢A1:A10｣とした場合に「C」列に取り出したい値があるなら、｢C1:C10｣という形で指定する。
  • 　｢A:A｣ならば｢C:C｣と指定する。
  • ※｢検索範囲｣と｢戻り範囲｣は実際の数式では場所を固定するために、以下のように｢$｣を入れて指定箇所を固定すると数式をコピーしたときにずれなくなるので、入れましょう。手打ちで入れるほか、範囲指定時に｢F4｣を押すと固定できます。

$A$1:$A$10

• 見つからない場合[省略可]：｢検索値｣で検索した内容が見つからない場合の処理を指定。｢”検索内容がみつかりません”｣というように｢”｣で括った文章の指定や、数式を入れて見つからなかった場合の処理を入れられる。
• 一致モード[省略可]：以下の数値を設定することで、｢検索値｣に対しての検索のルールを指定できる。
  • 0：｢検索値｣と完全に同じ内容で検索。｢検索値｣が見つからない場合は｢＃N/A｣エラーになる。｢一致モード｣を省略した場合は、この設定になる。
  • -1：｢検索値｣と｢検索範囲｣が数値の場合に有効。｢検索値｣が見つからない場合に、｢検索範囲｣にある値で｢検索値｣の次に小さい値を｢検索値｣として検索する。
  • 1：｢検索値」と｢検索範囲｣が数値の場合に有効。｢検索値｣が見つからない場合に、｢検索範囲｣にある値で｢検索値｣の次に大きい値を｢検索値｣として検索する。
  • 2：｢検索値｣で｢*｣や｢?｣などのワイルドカードを使って検索できるようにする。この指定をしないとワイルドカードが使えない。
  • ▼ワイルドカードを使った指定の方法：ここでは｢*｣と｢?｣を説明
    • *：検索値に｢”*A”｣、｢”A*”｣、「”*A*”」といった形で指定することで、｢A｣の前後の｢*｣のある方向にいずれかの内容があっても、その内容を検索する。
    • ｢”*A”｣なら｢123A｣、｢あいA｣などの任意の内容の後ろにＡがつくものを｢検索値」としてを検索する。
    • ｢”A*”｣なら｢A456｣、｢Aかき｣などのAの後ろに任意の内容があるものを｢検索値｣として検索する。
    • 「”*A*”」なら｢あいうＡ123｣のように｢Ａ｣を含む内容を｢検索値｣として検索する。
    • ?：任意の値として指定する。｢”A?C”｣、｢”AB??E”｣などの形で指定し、「?」1つにつき1文字分を任意の内容があるものとして検索する。
    • ｢”A?C”｣なら｢ABC｣、｢AあC｣などを検索し、｢”AB??E”｣なら｢ABCDE｣、｢ABかきE｣などを検索する。
    • 「?」の文字数が合わない場合はエラーになる。
• 検索モード[省略可]：以下の数値を設定することで、｢検索範囲｣の検索の方向を指定できる。
  • 1：｢検索範囲｣を上から、または左から検索する。
  • -1：｢検索範囲｣を下から、または右から検索する。
  • 2：｢検索範囲｣がすべて数値で、それが昇順である場合、検索の速度が速い計算をしてくれる。データ量が相当多い場合に有効な設定。｢検索範囲｣が数値でないとエラーになる。
  • -2：｢検索範囲｣がすべて数値で、それが降順である場合、検索の速度が速い計算をしてくれる。データ量が相当多い場合に有効な設定。｢検索範囲｣が数値でないとエラーになる。
  • ※省略可能箇所で、一部を省略してその後ろを有効にしたい場合、例えば、｢見つからない場合｣と｢一致モード｣を省略して｢検索モード｣で下から検索する設定にしたい場合は以下の様に指定する。

=XLOOKUP(A1,A1:A10,C1:C10,,,-1)

XLOOKUP書き方色々

■縦方向に指定した｢戻り範囲($B$2:$B$6)｣の、｢検索値｣のある行の内容とる場合

=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6)

■ 横方向に指定した｢戻り範囲($B$4:$F$4)｣の、｢検索値｣のある列の内容とる場合

=XLOOKUP(H3,$B$1:$F$1,$B$4:$F$4)

■ ｢検索値1｣で指定した縦の｢検索範囲｣に対して｢検索値2｣で指定した横の｢検索範囲｣の交わる箇所を求める

=XLOOKUP(H3,$A$2:$A$6,XLOOKUP(I3,$B$1:$F$1,$B$2:$F$6))

｢XLOOKUP｣を2重に使用することで、列と行の内容を指定して表の位置を求めることができます。

後半の｢XLOOKUP｣の書き方が、上記までの書き方と少し違います。

XLOOKUP(I3,$B$1:$F$1,$B$2:$F$6)

後半では｢検索値：C｣で｢C｣の列を指定し、以下のように表の範囲を指定すると、この数式は｢D2:D6｣の範囲を持ちます。

検索範囲：$B$2:$F$6

XLOOKUP(I3,$B$1:$F$1,$B$2:$F$6)

を独立してセルに入れると、上記画像のようにCの範囲が表示されます。
ちなみに数式は｢D9｣のセルに入れていますが、その下のセルに自動で値が入ります。

■ ｢見つからない場合[省略可]｣の使い方

・エラーの場合に文字列を返す：A列に｢検索値｣が無い場合に｢対象の値は存在しません。｣と表示する。

=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6,"対象の値は存在しません。")

・エラーの場合に数式を返す：｢IF｣関数を用いて、A列に｢検索値｣が無い場合に、5より大きい値の時は｢入力の数値を5以下にしてください｣を、1より小さい時に｢入力の数値を１以上にしてください｣を表示する。

=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6,IF(H3>5,"入力の数値を5以下にしてください","入力の数値を１以上にしてください"))

※上記数式では｢1より小さい場合｣を記載していないが、そもそも前の式で検索値1～5が引っ掛かり、IFで5より大きい数値の場合を指定しているので、1～5と5より大きい値ではない場合が1より小さい値になるので、記載がなくても求められる。

■ ｢一致モード[省略可]｣の使い方：｢検索範囲｣が数値の場合に有効

・｢検索値｣が見つからない場合に次に小さい箇所を求める。

下記の場合、｢3.5｣が検索範囲に無いので次に小さい｢3｣の箇所を求めている。

=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6,,-1)

｢検索値｣が見つからない場合に次に大きい箇所を求める。

下記の場合、｢4.5｣が検索範囲に無いので次に大きい｢5｣の箇所を求めている。

=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6,,1)

■ ｢一致モード[省略可]｣で｢2｣を指定した場合のワイルドカードの書き方

｢検索値｣で｢”*き*”｣と指定して｢検索範囲｣に｢き｣が含まれる箇所を求める。

=XLOOKUP("*き*",$A$2:$A$6,$B$2:$B$6,,2)

｢検索値｣を他のセルを指定し、｢検索範囲｣でそれが含まれる箇所を求める。

=XLOOKUP("*"&H3&"*",$A$2:$A$6,$B$2:$B$6,,2)

｢検索値｣で｢”く?こ”｣と指定して｢検索範囲｣に｢く｣と｢こ｣の間に任意の位置文字のある箇所を求める。

=XLOOKUP("く?こ",$A$2:$A$6,$B$2:$B$6,,2)

■ ｢検索モード｣の使い方

・｢検索範囲｣に同じ内容が含まれているとき、｢検索モード｣で｢2｣を指定することで、｢検索範囲｣を下から検索する。
　下記の場合は、｢H3｣のセルに指定した｢2｣を｢検索値｣として、｢検索範囲｣を下から検索しているので、｢B5｣セルの箇所が求められている。

=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6,,,-1)

・｢検索範囲｣に同じ内容が含まれているとき、｢検索モード｣で｢-1｣を指定することで、｢検索範囲｣を右から検索する。
　下記の場合は、｢H3｣のセルに指定した｢B｣を｢検索値｣として、｢検索範囲｣を右から検索しているので、｢E1｣セルの箇所が求められている。

=XLOOKUP(H3,$B$1:$F$1,$B$2:$F$2,,,-1))

■ 違う列の内容を｢検索値｣としてそれぞれの列の内容を｢検索値｣として1つずつ指定してそれらがどちらもある｢戻り範囲｣の内容を求める方法

・｢検索値｣と｢検索範囲｣を｢＆｣で繋いで設定することで、それらの｢検索値｣がすべてある｢戻り範囲｣の内容をとることができる。
以下の場合は｢検索値1(4)｣と｢検索値2(う)｣がどちらもある｢戻り範囲｣の｢C5｣セルの箇所が求められている。

=XLOOKUP(H3&I3,$A$2:$A$6&$B$2:$B$6,$C$2:$C$6)

・上記に加え、横の｢検索範囲｣も指定する場合

=XLOOKUP(H3&I3,$A$2:$A$6&$B$2:$B$6,XLOOKUP(J3,$C$1:$F$1,$C$2:$F$6))

・｢検索値｣2つ+横の｢検索範囲｣も指定する場合

=INDEX($C$2:$F$6,MATCH(H3&I3,$A$2:$A$6&$B$2:$B$6,0),MATCH(J3,$C$1:$F$1,0))