ExcelでVLOOKUP関数はもういらない!?XLOOKUP関数を覚えよう!

2020-02-21

最近「Office365」の更新で、とうとう!ウワサの「XLOOKUP関数」が実装されました。

「見せてもらおうか!新しい関数の性能とやらを!」と心の中で言いながら、早速使用してみました。VLOOKUP関数の欠点を補うために「INDEX関数/MATCH関数」の合わせ技で対応していた方も、この「XLOOKUP関数」でその煩わしさから解放されるでしょう(^^)

そして、「これからVLOOKUP関数を覚えなきゃ・・・」という方は、間違いなくスタンダードになるであろう「XLOOKUP関数」も覚えておきましょう♪
この記事では「XLOOKUP関数」の動きなどをご紹介します。しばしお付き合いください。

スポンサーリンク

[初心者向け]そもそも、ほにゃららLOOKUPってどういう関数??

※ここはVLOOKUP関数を理解されている方は、読み飛ばしていただいて問題ありません※

関数というのは…というか、パソコンで行われていることは、大体「人間がやっていたことをパソコンで行っている」だけです。なので、VLOOKUP関数も同じです。イメージとしては「わからない言葉を辞書で調べる」のが分かりやすいと思います。

この場合の人間の行動として、

1,わからない言葉がみつかり、その言葉をそのまま覚える
2,辞書を開き、その言葉を見つける
3,その言葉の意味を読み理解する

という流れかと思います。

これをVLOOKUP関数で書くと・・・

という感じです。
VLOOKUP関数は、求人票などに「VLOOKUP関数を理解している」などという文言が見受けられるくらい必須の関数です。しかし、VLOOKUP関数は様々な「面倒くささ」がありました。その「面倒くささ」を取っ払ったのが「XLOOKUP関数」です。

VLOOKUP関数の詳細は別記事でご紹介しますが、ここでは雰囲気だけ感じてもらえれば大丈夫です。

XLOOKUP関数だけ覚えれば大丈夫!?いやいや・・・

XLOOKUP関数だけ覚えれば大丈夫!な時代はいずれきますが、今はそういう訳にはいきません。

XLOOKUP関数は「Office365で対応している関数」になるので、他のバージョンでは計算結果が表示されるだけで、実際には関数として機能しません><
VLOOKUP関数の「めんどくさい部分」を排除してくれて使いやすくなった関数なのですが、古いバージョンには対応していないのです。

次の画像を見てみてください。
まずは「Office365」で開いた場合の計算式です。

そして、こちらが「最新バージョンでないExcelで開いた時」の計算式です。

計算式の先頭が「=_xlfn.XLOOKUP(…」となっていますよね?これは、そのバージョンのExcelが「いやいや、わしこんな数式知らんでぇ~」と言っているということになります><
そのまま数式を修正したりしなければ、計算結果が表示されたままなのですが、少しでも修正してしまうと・・・

計算されずにエラーが返されてしまいます><
VLOOKUP関数では面倒だったことなどが排除されたXLOOKUP関数ですが、最新バージョンのOffice365でない場合は計算式が機能しません。そのExcelファイルを使用される方たちのExcelのバージョンが異なる場合は、悲しいかな、なるべく使用を避けるのがよいと思います。

XLOOKUP関数を使ってみよう!

それでは、実際に使用してみましょう。

【F3】の顧客IDに対応する『氏名』を【A2:D7】の表より【G3】へ表示するです。

① セル【G3】へ「=xl」まで入力します。そこまで入力すると「XLOOKUP」が予測で表示されますので、ダブルクリックで選択します。

② ダブルクリックをすると「 XLOOKUPの全容 」がわかります。

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

これが全容となります。[]でくくられている部分は「省略可」となっています。ひとまず、ここでの説明では「省略」します。

③ 検索値である「F3」を入力します。入力後「,(カンマ)」を入力します。

④ 次にその検索値を探す場所「検索範囲」を指定します。今回は【A2:A7】を入力します。入力後「,(カンマ)」を入力します。

⑤ 次に「戻り範囲」、つまり「結果を戻してほしいセルがある場所」を指定します。今回は【B2:B7】を入力します。その後、カッコで閉じます。

⑥ 『 Enter 』キーにて確定すると、顧客ID「103」に対応する氏名「落花 カナヲ」が表示されました。

VLOOKUP関数を使用したことがある人ならわかりますが、「え~っと、ここの列を引っ張ってくるから・・ 1,2,3,……,15ね」という煩わしさがなくなりました。この「列数を数える行為」がどれだけめんどくさかったことか・・・。これがなくなっただけでも「XLOOKUP関数の登場は意味がある」のです!!

XLOOKUP関数の「省略可」部分の説明

さて、ここからは先ほどの説明で、それこそ省略した「省略可」の部分について説明していきます。

見つからない場合

見つからない場合というのは「検索値」が「検索範囲に存在しない」時のことを言います。
例えば、セル【F3】の顧客IDへ『110』を入力しEnterにて確定してみます。

計算結果が「#N/A」と表示されます。画像にあるように「検索範囲【A2:A7】に該当の顧客ID『110』が存在していないため、エラーを表示した」ということになります。このように「見つからない場合」を省略した場合は、Excelが指定しているエラー表示がされます。ただ、見つからない場合に「#N/A」と表示されるのは味気ない感じもします。なので、先ほど省略した「見つからない場合」へ引数を指定します。

先ほどの数式に「 "該当なし" 」を追加してみました。例のごとく文字を表示させたい場合は「"(ダブルクォーテーション)」で囲ってあげてくださいね。そして、Enterで確定すると

「該当なし」と指定したとおり表示されました。この「見つからない場合」は、VLOOKUP関数では指定できないため、IFERROR関数などとの合わせ技で以前は対応していました。
=IFERROR(VLOOKUP(F3,A2:D7,2),"該当なし")という感じです。しかし、これからは合わせ技をすることなく、エラー時の対応が可能となりました♪

一致モード

続いて「一致モード」です。これは、

0:完全一致
-1:完全一致または次に小さい項目
1:完全一致または次に大きい項目
2:ワイルドカード文字との一致

となります。一つずつ説明していきましょう。その前に、セル【A4】を「103」から「109」へセル【A7】を「105」から「107」へ変更します<(_ _)>

完全一致:0

完全一致は「その検索値と同じだったら、それに対応する結果を返す」ということです。この「一致モード」を省略した場合は「完全一致」が選択されています。
まぁ、正直、実務ではこれ以外使うことはないのかなぁ…とは思いますがね。なので、ほとんどの場合「省略」でいいと思います。

完全一致または次に小さい項目:-1

続いて「 -1 」を指定してみます。そして、セル【F3】の顧客IDを「106」と入力します。「 -1 」は、完全一致または次に小さい項目とあります。セル範囲【A2:A7】には、106は存在していません。なので、106に近い小さい顧客IDを見つけ結果を返すことをしています。106の手前は104ですよね?そのため「高田 すみ」が結果として表示されています。

完全一致または次に大きい項目:1

続いて「 1 」を指定してみます。「 1 」は、完全一致または次に大きい項目とあります。セル範囲【A2:A7】には106は存在していません。なので、106に近い大きい顧客IDを見つけ結果を返すことをしています。106の次は107ですよね?そのため「我妻 伊之助」が結果として表示されています。

ワイルドカード文字との一致:2

ワイルドカードをご存知ですか?「*A*」とすると「1A3」や「A123」など、途中にAが存在するものを探すという感じで使用できる「*(アスタリスク)」のことです。VLOOKUP関数では、このワイルドカード検索(あいまい検索)を行うときは、数式が長くなり面倒でしたが、XLOOKUP関数では設定することが簡単になりました。

まず、セル【G3】の数式を =XLOOKUP(F3,B2:B7,D2:D7,"該当なし",2) に修正します。検索値【F3】を検索範囲【B2:B7】で探し、戻り範囲【D2:D7】より結果を表示するというものです。

この数式に変更し、セル【F3】を『 *カナヲ 』とすると・・・

検索範囲【B2:B7】を、上から順番に「 *(ほにゃらら)カナヲ 」を探します。見つけたので、戻り範囲【D2:D7】にある「24」を表示させました。*(アスタリスク)が入力されていない場合は「あいまい検索ではない」ので、結果は…

「該当なし」と表示されます。あいまい検索出ない場合は「完全一致」となります。

検索モード

続いて「検索モード」の説明です。その前に、セル【A6】を「104」から「103」へ変更します<(_ _)>
顧客ID「103」が2個存在するデータとなりました。
さて、この「検索モードは・・・

1:先頭から末尾へ検索
-1:末尾から先頭へ検索
2:バイナリ検索(昇順で並べ替え)
-2:バイナリ検索(降順で並べ替え)

となります。一つずつ説明していきましょう。

先頭から末尾へ検索:1

検索範囲【A2:A7】を先頭から順に検索し、最初に出てきた顧客ID「103」に対応する氏名「落花 カナヲ」が表示される。

末尾から先頭へ検索:-1

検索範囲【A2:A7】を末尾から順に検索し、最初に出てきた顧客ID「103」に対応する氏名「高田 すみ」が表示される。

バイナリ検索(昇順で並べ替え):2

「バイナリ検索」についてのお話を別途ご用意しますので、ひとまずは置いておいてください。
実務上、そこまで利用頻度は高くないので、また別の機会に・・・

バイナリ検索(降順で並べ替え):-2

こちらも「バイナリ検索」についてのお話を別途ご用意しますので、ひとまずは置いておいてください。
実務上、そこまで利用頻度は高くないので、また別の機会に・・・

まとめ

XLOOKUP関数は、間違いなく今後の主流となるLOOKUP関数です。それは前述したように、VLOOKUP関数では面倒だった部分が改善されたからです。しかし、前述したように「Office365」でしか動かない関数となります。会社の状況などを確認して、バージョンなどが同じなら、積極的にXLOOKUP関数を使用していきましょう!VLOOKUP関数はもういらない!!!