XLOOKUP関数の書き方
「検索値」に紐づく情報を、別のリストから検索・表示できる関数
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)
必須の入力項目
検索値、検索範囲、戻り範囲
任意の入力項目
見つからない場合、一致モード、検索モード
VLOOKUP関数と比べて便利なこと
・検索値が検索範囲の「一番左」になくてOK
・IFERROR関数を書く必要がない
・横方向の検索も可能
「Microsoft365」のバージョンのExcelから新たにXLOOKUP関数が追加されました。
・VLOOKUP関数
・HLOOKUP関数
・IFERROR関数
・INDEX関数
・MATCH関数
これらの関数の機能がまとめて一つになり、格段に使いやすくもなったまさに次世代の関数です。
この記事ではExcelCamp講師が研修で実際に説明している手順に沿って、解説いたします。
ExcelCamp講師 野沢先生「VLOOKUPとXLOOKUPどっちを使うか問題」ですが
旧バージョンとの互換性を気にしなくてよいのであればXLOOKUP一択です。
ぜひこの機会に書き方を覚えてください！
XLOOKUP関数の書き方
XLOOKUP関数の書き方
「検索値」に紐づく情報を、別のリストから検索・表示できる関数
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)
必須の入力項目
検索値：検索したい値をいれます(「バナナの原価」を調べたいなら「バナナ」が検索値)
検索範囲：検索値が含まれる別のリストの列を選択します(「バナナ」が書かれた列)
戻り範囲：表示させたい項目がの列を選択します(「原価」が書かれた列)
任意の入力項目
見つからない場合：エラーが出たときに表示させる文字を指定できます
一致モード：完全一致か、近似一致かを指定できます(空白なら「完全一致」)
検索モード：上から検索するか、下から検索するか選べます(空白なら「上から検索」)
たとえばXLOOKUP関数を使って、別のリストから「バナナの原価」を検索して表示させてみます。
ExcelCamp講師 野沢先生せっかくなのでXLOOKUP関数の機能紹介だけでなく、
なるべくすばやく書くコツも同時にお伝えします。
まず、=xlと半角で記入します。
すると関数の候補がXLOOKUP関数だけに絞られるので、Tabキーを押して関数名を自動入力しましょう。
関数名が一気に入力されました。
ExcelCamp講師 野沢先生各入力項目ごとに入力の仕方を解説していきます。
検索値を指定する
まずは「検索値」を指定します。
今回は「バナナの原価」を調べたいので「バナナ」が検索値です。←キーを一回押して「バナナ」と書かれたセルを選択します。
選択できたら,(半角カンマ)を打ってください。
ExcelCamp講師 野沢先生書いた関数を右にコピーしていく場合は
「列の絶対参照($B6)」を忘れずにつけてください。
F4キーを3回押すことでつけることができます。
※PCによってはFnキーを押しながらF4キー
検索範囲を指定する
次に検索範囲を指定します。
「バナナ」が検索値なので、別のリストで「バナナ」と書かれている列を1列だけ選択します。
ExcelCamp講師 野沢先生範囲を選んだらF4キーで「行列の絶対参照」を忘れずにつけましょう！
もしくはCtrlキーを押しながらSpaceキーを押して「列全体」を選択してもOKです。
最後に,(半角カンマ)を打ちます。
戻り範囲を指定する
次に「戻り範囲」の指定です。バナナの原価を調べる場合、「原価」が戻り範囲となります。
「原価」が書かれた列を一列だけ選択して、F4キーで行列の絶対参照をします。
もしくは列全体をCtrl&Speceで選択してください。
ExcelCamp講師 野沢先生ここまでがXLOOKUP関数の必須設定です。
このままEnterキーを押せば正しく結果が表示されますが、
・エラーが出たときの表示を変えたい
・こだわった検索をしたい
という方はこの後の項目も入力が必要です。
見つからない場合を指定する
「見つからない場合」はIFERROR関数に該当する機能で、「もし検索値が発見できなかったら何を表示するか」を自由に指定できます。
たとえば上の表で、「いちご」の情報は参照データに存在しないのでエラーが出てしまいます。
ExcelCamp講師 野沢先生エラーが出た際には、まず第一に根本解決を目指して「参照データにいちごを追加できないか」検討してください。
どうしても追加できない場合には、「–(半角ハイフン)」などでエラー表示を整えてあげるとリストが見やすくなります。
今回はエラーの場合「–(半角ハイフン)」を入力してみます。
このように“-“と書いてEnterキーを押します。
無事「#N/A」のエラー表示が消えて「-」に置き換わりました。
ExcelCamp講師 野沢先生「-」だけ左揃えになっているのが気になる方はAltキー、Hキー、Aキー、Rキーを順番に押して、列全体の文字配置を右側に整えてください。
一致モードを指定する
| 一致モード | 意味 |
|---|---|
| 0(入力を省略可) | 完全一致しないと表示しない(デフォルト設定) |
| 1 | 完全一致したらその値を表示 完全一致しなかったら、次に大きい項目を表示 |
| -1 | 完全一致したらその値を表示 完全一致しなかったら、次に小さい項目を表示 |
| 2 | *、?、~など、検索値にワイルドカードを使用するときに選択 |
一致モードには4種類ありますが、99%以上の実務操作では完全一致しか使わないので基本的に何も書かなくて大丈夫です。
検索モードを指定する
最後に検索モードですが、こちらも4種類あります。
| 検索モード | 意味 |
|---|---|
| 1(入力を省略可) | 検索範囲の上から下に向かって検索値を探す(デフォルト設定) |
| -1 | 検索範囲の下から上に向かって検索値を探す |
| 2 | 昇順に並んだ検索範囲に対してバイナリ検索 ※あらかじめ検索範囲を昇順に並び替えておく必要あり |
| -2 | 降順に並んだ検索範囲に対してバイナリ検索 ※あらかじめ検索範囲を降順に並び替えておく必要あり |
ExcelCamp講師 野沢先生「一致モード」同様、ほぼすべてのケースで検索モード「1」しか使いません。
入力は省略できるので「1」と書く必要もありませんね。
ExcelCamp講師 野沢先生なので、上の画像の数式で書かれている最後の「,,」は書かなくても大丈夫です。
XLOOKUP関数はVLOOKUP関数に比べて「何が」便利？
ExcelCampでは長年VLOOKUP関数の書き方を研修してきましたが、バージョンの互換性さえあればXLOOKUP関数に完全に移行するべきと考えています。
ExcelCamp講師 野沢先生それほどまでにXLOOKUP関数は、使いやすく便利です。
ここではVLOOKUP関数に比べて優れていると思う点を、いくつかご紹介します。
便利な点1：検索値が検索範囲の「一番左」になくてOK
VLOOKUP関数には、検索値が含まれる列は選択範囲の「一番左」になければいけないという絶対的ルールがありました。
ExcelCamp講師 野沢先生このルールのせいで、わざわざ検索値が一番左に来るように参照データを加工したり、INDEX関数とMATCH関数を駆使したりと作業に手間がかかっていました。
XLOOKUP関数であれば検索範囲と戻り範囲をわけて1列ずつ指定するため、どこの列にあろうが関係なく検索ができるようになりました。
便利な点2：IFERROR関数を書く必要がない
2点目はエラーのときの対処法です。
VLOOKUP関数では関数の中でエラー表示について指定ができず、IFERROR関数を別に書く必要がありました。
一方XLOOKUP関数は関数内に「見つからない場合」を指定する項目があるので、入力が断然ラクになりました
便利な点3：横方向の検索も可能
VLOOKUP関数は「Vertical(垂直に)＋LOOKUP(探す)」関数という意味なので、縦方向への検索にしか対応していません。
「横方向」の検索を行う際にはHLOOKUP関数(Horizon(水平に)+LOOKUP(探す))が必要でした。
ExcelCamp講師 野沢先生しかしXLOOKUP関数であれば、「検索範囲」「戻り値」が行であろうが列であろうが関係なく検索をしてくれます。
これ故にXLOOKUP関数はVLOOKUP関数の「完全上位互換」といわれています。
補足：「スピル」機能と併用してさらに便利に
同じくMicrosoft365から実装された「スピル」という機能を使うと、より便利にXLOOKUP関数を書くことができます。
ExcelCamp講師 野沢先生「スピル」は、本来1つのセルを指定すべき関数の入力箇所を範囲で指定することで、下のセルにも一気に関数の結果を入力できる機能です。
XLOOKUP関数の場合、検索値にスピルを使ってこのように書くことができます。
本来検索値は隣のセル(K6セル)だけを指定するものですが、あえて下の範囲全体を選択しています。
この状態でEnterキーを押すと、選択した範囲全てに関数の結果が表示されます。
すべてのExcelバージョンに対応しているわけではないので注意
XLOOKUP関数を使う際の懸念事項は、「すべてのExcelのバージョンに対応していない」という点のみです。
アイティメディア株式会社の2022年度調査によると、全Excelユーザーの内Microsoft365を導入している割合はまだまだ多くはありません。
※全文を読むには無料会員登録が必要です
ExcelCamp講師 野沢先生Excel利用者全員がMicrosoft365バージョンのExcelを使用している場合であれば、XLOOKUP関数に完全移行してしまって問題ないかと思います。
休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修
私たちが提供するExcel研修「上位1%を目指す！ExcelCamp」では、これまで延10,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。
※10点満点中の評価の平均値。
休日1日を研修に使うのは「やや重たい」というご意見もいただきます。
しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです。
実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。
そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。
参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。
「ExcelCamp」はExcel初心者・苦手な方のための研修です。
・たった1日でExcel操作が「激変」します。
・講義中に反復練習するから、その場でExcelが得意になります。
・延べ10,000名以上が参加し、満足度は9.71(10点満点)です。
動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中！
※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。
現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。
全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。
著書「神速Excel」も絶賛発売中
