アピリッツのオンラインゲーム事業でクリエイター派遣を担っている「エンパワーメントサービス部（ES部）」。このES部を中心としたデータベース「CREATORSナレッジベース」からのおすそ分け記事です。今回はプランナーとしてマスターデータの管理を担当している毛塚 孝さんのナレッジです。（2022年1月 取材・作成）
今回のナレッジベースの作者
XLOOKUP とは XLOOKUP関数は、｢VLOOKUP」や｢HLOOKUP」の上位に当たる関数です。
｢VLOOKUP」や｢HLOOKUP」と同じ検索結果を求めることができ、さらに｢VLOOKUP｣ではできなかった、検索値より左のセルの値をとることも簡単にできます。
同様に、｢HLOOKUP｣ではできなかった検索値より上のセルの値をとることもできます。
ちなみに｢XLOOKUP｣はoffice365のサブスク版、office2021(買い切り)で使えます。
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の範囲が表示されます。
■ ｢見つからない場合[省略可]｣の使い方 ・エラーの場合に文字列を返す：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｣を指定することで、｢検索範囲｣を下から検索する。
=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6,,,-1) ・｢検索範囲｣に同じ内容が含まれているとき、｢検索モード｣で｢-1｣を指定することで、｢検索範囲｣を右から検索する。
=XLOOKUP(H3,$B$1:$F$1,$B$2:$F$2,,,-1)) ■ 違う列の内容を｢検索値｣としてそれぞれの列の内容を｢検索値｣として1つずつ指定してそれらがどちらもある｢戻り範囲｣の内容を求める方法 ・｢検索値｣と｢検索範囲｣を｢＆｣で繋いで設定することで、それらの｢検索値｣がすべてある｢戻り範囲｣の内容をとることができる。
=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))
