Excelで名簿やカタログリストを扱っていると、 「別シートから値を引っ張りたい」「コードに対応する名前を表示したい」 といった「検索して値を取り出す作業」が発生します。
このときに、よく利用されるのが 「VLOOKUP関数」や「XLOOKUP関数」です。
VLOOKUP関数は古くから使われている関数ですが、 検索方向の制限や列番号指定など、初心者がつまずきやすいポイントが多くあります。
一方、XLOOKUP関数はそれらの弱点をすべて解消した新しい検索関数で、 縦・横どちらでも検索でき、エラー処理も簡単、構造変更にも強いというメリットがあります。
この記事では、 XLOOKUP関数を中心に使い方を解説し、 そのあとでVLOOKUP関数との違いや使い分けを解説します。
Excel初心者でも迷わず使えるようになる内容になっていますので、 ぜひ最後まで読んでみてください。
結論：XLOOKUP関数が基本、VLOOKUP関数/HLOOKUP関数は旧式
Excelで「検索して値を取り出す」作業をするなら、今はXLOOKUP関数が標準です。
Office 365やExcel 2021以降を使っているなら、まずXLOOKUP関数を使うのが正解です。
VLOOKUP関数/HLOOKUP関数は古い関数で、制限が多く、柔軟性に欠けます。
| 比較項目 | VLOOKUP関数 | HLOOKUP関数 | XLOOKUP関数 |
|---|---|---|---|
| 検索方向 | 縦方向 （左→右のみ） | 横方向 （上→下のみ） | 縦・横どちらもOK |
| 検索の柔軟性 | 左側に検索列が必要 | 上側に検索行が必要 | 検索範囲と返す範囲を自由に指定 |
| 列／行番号指定 | 列番号の指定が必要 | 行番号の指定が必要 | 不要 （返す範囲を直接指定） |
| 構造変更への強さ （検索対象リストの編集） | 列追加で壊れやすい | 行追加で壊れやすい | 強い （範囲指定のため壊れにくい） |
| エラー時の処理 | IFERRORと併用 | IFERRORと併用 | 第4引数で直接指定可能 |
| 一致モード | 完全一致／近似値 | 完全一致／近似値 | 完全一致がデフォルト＋柔軟な検索モード |
| 利用可能バージョン | Excel 2019以前も可 | Excel 2019以前も可 | Excel 2021 / Office 365 |
| 役割 | 縦方向検索の旧式関数 | 横方向検索の旧式関数 | VLOOKUP・HLOOKUPの完全上位互換 |
| 実務での使用頻度 | まだ多い（旧環境で使用） | あまり見ない | 現代の標準 |
XLOOKUPの基本的な使い方
XLOOKUP関数の構文
=XLOOKUP(検索値, 検索範囲, 返り範囲, [一致しなかった場合], [一致モード], [検索モード])
使用例１：縦方向検索
=XLOOKUP(G2,A:A,B:B,"該当なし")- A列の商品コードからG2に入力した「C201」を探し、Ｂ列の商品名を返す
- 見つからない場合は「該当なし」と表示
XLOOKUPではVLOOKUPではできなかった、検索範囲を軸にした左右両方の列を検索することができます。
例えば今回の表では
=XLOOKUP(G2,B:B,A:A,"該当なし")- B列の商品名からG2に入力した「USB-Cハブ」を探し、A列の商品コードを返す
- 見つからない場合は「該当なし」と表示
と検索する列の左側の範囲の値を返すことができます。
使用例２：横方向検索
=XLOOKUP(I2,B1:G1,B4:G4,"該当なし")- １行目（B1:G1）の日付からI2に入力した「9月2日」を探し、４行（B4:G4）の販売数を返す
- 見つからない場合は「該当なし」と表示
データテーブルにおける行列の追加・削除
XLOOKUP関数はVLOOKUP関数/HLOOKUP関数と異なり、列番号/行番号の指定が不要なため、行列の追加・削除で検索範囲を再度指定しなおす必要がありません。
例えば、以下の表でB列に新しく列を挿入すると、VLOOKUP関数では戻り範囲が変化した結果”０”を返します。
同じ表でXLOOKUP関数を使えば、新たに列を挿入しても、検索範囲や戻り範囲が自動で追従されます。
エラー時の処理（第４引数）
XLOOKUP関数における第4引数は 「見つからない場合に返す値」 を指定します。
検索値が見つからないとき、通常は #N/A が返ります。
第4引数を指定すると、好きな文字や値を返すことができます。
今回の例では「該当なし」としましたが、「空白セルを返す」もよく使う処理です。
| 表示したい内容 | 第4引数の書き方 |
|---|---|
| 該当なしと表示 | “該当なし” |
| 空白にする | “” |
| ハイフンにする | “-“ |
| 0を返す | 0 |
一致モード（第５引数）
第5引数は 「検索値と検索範囲をどう比較するか」 を指定する引数です。
一致モードには 4種類あります。
| 一致モード | 数値 | 動作 |
|---|---|---|
| 完全一致（デフォルト） | 0 | 完全一致する値を探す |
| 完全一致（次に小さい値） | -1 | 完全一致がなければ「次に小さい値」を返す |
| 完全一致（次に大きい値） | 1 | 完全一致がなければ「次に大きい値」を返す |
| ワイルドカード一致 | 2 | * や ? を使った部分一致検索 |
完全一致（0）※デフォルト
検索値と完全に一致する値を探します。
最初に示した例のように第５引数に何も指定がないと、この完全一致になります。
=XLOOKUP(G2,A:A,B:B,"該当なし",0)- A列の商品コードからG2に入力した「C201」を探し、Ｂ列の商品名を返す
- 見つからない場合は「該当なし」と表示
完全一致または次に小さい値（-1）
「完全一致（次に小さい値）」はVLOOKUPの近似値検索（TRUE）とほぼ同じ動作です。
完全一致する値がなければ、比較する検索値の次に小さい値を参照します。
IF関数などでも同様の処理はできますが、数値の範囲検索で使うことができます。
例えば 「テストの点数に応じた評価をつけたい」といった判定表で便利です。
=XLOOKUP(点数,判定基準点,対応する評価,,-1)
- 完全一致がなければ「次に小さい値」を探す
- 例えば得点が 83 点なら、83より小さく一番近い値=80点の行を参照する
どどん上の例では第４引数を指定しませんでしたが、「完全一致（次に小さい値）」では検索値に近い値を探しに行くことで、ほぼエラーになることがありません。
それでも返す値がない時は「#N/A」が表示されます。
完全一致または次に大きい値（1）
先ほどとは逆に、完全一致する値がなければ、比較する検索値の次に大きい値を参照します。
例えば 「荷物サイズに応じた送料計算」といった判定表で便利です。
=XLOOKUP(荷物のサイズ,判定基準となるサイズ,対応する送料,,1)
- 完全一致がなければ「次に大きい値」を探す
- 例えば得点が 83 点なら、83より大きく一番近い値=100サイズの行を参照する
ワイルドカード一致（2）｜部分一致検索
部分一致検索ができる自由度をより広げるモードです。
ワイルドカードの使用例：
| 記号 | 意味 | 使用例 |
|---|---|---|
| ? | 任意の一文字 | 洗?機 と書くと洗濯機や洗顔機などがヒットします。 ?の数は文字数と一致しているので、『洗濯乾燥機』をヒットさせようとすると 洗???機 とする必要があります。 逆にこの場合、『洗浄機』などの３文字パターンはヒットしません。 |
| * | 任意の文字列 （0文字以上） | 洗*機 と書くと『洗』『機』の間の文字数に関係なくヒットします。 この場合は『洗濯機』も『洗顔機』も『洗濯乾燥機』も全てヒットします。 |
| ~ | エスケープ （特殊文字を文字として扱う） | ワイルドカードを無効化して、文字として扱うことができます。 |
ワイルドカード使用時の実際の挙動
?（任意の一文字）
ワイルドカードを使うと、検索の結果複数の項目がヒットすることがありますが、XLOOKUP関数ではスピル機能（複数行を自動展開して返す機能）はありません。
そのため、検索値に一致する 最初の1件だけ を返す設計になっています。
以下の図の例で『洗?機』と入力すると『洗濯機』の２件と『洗顔機』の２件の合計４件該当しますが、
検索結果は最初の『洗濯機』の『アクアウォッシュ』のみ返されます。
*（任意の文字列（0文字以上））
以下の図の例で『洗*機』と入力すると『洗濯機』の２件、『洗濯乾燥機』の２件、『洗顔機』の２件の合計６件該当しますが、
検索結果は最初の『洗濯機』の『アクアウォッシュ』のみ返されます。
このとき『洗顔機』の２件を削除すると、次にヒットする『洗濯乾燥機』の『スマートウォッシュドライ』が返されます。
~ エスケープ（特殊文字を文字として扱う）
『?』や『*』などのワイルドカードを無効化して、文字として扱うことができます。
検索したい文字列の中に、ワイルドカードとして使用する記号が入っている場合に使用します。
以下の図の例で説明します。
商品コードの検索値を『A*1』としたとき、通常であれば任意の文字列（0文字以上）のワイルドカードが適用され、一番最初にヒットする、『A54321』の商品コードに相当する、『アクアウォッシュ』が返されます。
ここでエスケープを使って、『A~*1』と入力すると、『A*1』に完全一致する商品コードに相当する『クリーンフロー』が返されます。
どどん個人的に、ワイルドカードを利用する場面が多いのは、「ヒットさせたい文字列はうろ覚えだけど分かっている」状況です。
例えば『ワタナベイチロウ』さんを検索したいのに、
『渡邉一郎』だっけ『渡邊一朗』だっけとなった場合に、
『渡?一?』と入力すればヒットさせることができます。
その他にも、「氏名の間にスペースが入ってたっけ」「半角スペースだっけ全角スペースだっけ」となったときも
『渡*一?』と入力すればヒットさせることができます。
検索モード（第６引数）
第６引数の検索モードは、主に、
- 順方向（リストの上から下）に検索する：『1』
- 逆方向（リストの下から上）に検索する：『-1』
かを指定する引数になります。
第６引数を指定しなければ、順方向（上から下）に検索します。
XLOOKUP関数は、ここまで説明した通り、検索して最初にヒットした値を返します。
通常はこの順方向検索で十分ですが、
例えば、リストが時系列順（古い順）に並んでいて、直近のデータを検索したいときは逆方向検索を使います。
VLOOKUP関数/HLOOKUP関数の基本的な使い方
■ VLOOKUP関数の構文
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
■ 使用例
=VLOOKUP(G2,A1:E13,2,FALSE)- A列の商品コードからG2に入力した「C201」を探し、2列目（B列）の商品名を返す
- FALSEは「完全一致」
■ VLOOKUP関数の弱点
- 検索列は必ず一番左に置く必要がある
- 列番号を手入力するため、列追加で壊れやすい
- エラー処理はIFERROR関数と併用が必要
つまり、XLOOKUP関数はVLOOKUP関数とHLOOKUP関数の完全上位互換です。
■ ＨLOOKUP関数の構文
=HLOOKUP(検索値, 範囲, 行番号, 検索方法)
■ 使用例
=HLOOKUP(I2,B1:G4,4,FALSE)- １行目（B1:G1）の日付からI2に入力した「9月2日」を探し、４行目（B4:G4）の販売数を返す
- FALSEは「完全一致」
どどんVLOOKUPとHLOOKUPの違いは語源から考えると分かりやすかったりします。
VLOOKUPの V は Vertical（垂直） の頭文字で、 「縦方向に検索して値を返す」関数です。
HLOOKUPの H は Horizontal（水平） の頭文字で、 「横方向に検索して値を返す」関数です。
どちらも関数の本質は同じなのですが、検索方向が縦か横かで２つの関数が用意されているイメージです。
さいごに
- XLOOKUP関数が基本、VLOOKUP関数とHLOOKUP関数は旧式。
- XLOOKUP関数は縦・横どちらにも対応し、構造変更に強い。
- XLOOKUP関数はエラー処理・検索モード・柔軟性が圧倒的に優れている。
職場のOffice環境はセキュリティ対策の観点から、Office 365を利用しているところが多い傾向にあります。
そうであればまず、初心者の方は XLOOKUP 関数を使ってみてください。
もし過去から代々引き継いできた資料でVLOOKUP関数を使用している場合は、可能であればXLOOKUP関数に修正してあげましょう。
もし数式を勝手に編集するのは避けたいという状況であれば、VLOOKUP関数のエラーが出ないように行列の追加・削除などはしないようにしましょう。
みなさんのお仕事に少しでも役立てれば幸いです。ではまたﾉｼ
コメント