今回の記事では
VLOOKUP関数を使って下から検索する方法って無いの？
順番はそのままで下から検索出来たら便利なのに
VLOOKUP関数を使わなくても良いからシンプルに下から検索したい
とにかく分かりやすい検索方法があれば教えてほしい
こういった疑問に答えます
✔VLOOKUP関数で下から検索する方法のテーマ
①VLOOKUP関数で下から検索するにはCOUNTIF関数と組み合わせるのが正解です
②XLOOKUP関数を使えばVLOOKUP関数より簡単に下から検索することが出来ます
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「VLOOKUP関数で下から検索する方法」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
VLOOKUP関数で下から検索するにはCOUNTIF関数と組み合わせるのが正解です
VLOOKUP関数で下から検索したい場合は、COUNTIF関数で連番を振ると実現出来ます
なぜなら、VLOOKUP関数は上から順に調べていく関数なので普通の方法では実現出来ないからです
では、具体的な方法を実際に見ていきましょう
まずは、普通の方法で検索した場合を基にしていきます
なお、今回の資料の内容は「ドッペルギャンガーアウトドア」様より一部を引用させていただきました
こんな風に「カテゴリー名」を入力することで「商品名」を表示させたい時はB5のセルに「=VLOOKUP(A5,A11:B25,2,0)」と入力すると表示されます
通常はコレで問題無いんですが、一番下のカテゴリーの商品名を反映したい時があります
例えば「時系列順で並んでいるので並べ替えはしたくない」「記録する順番が大切なのでどうしても並びは変えられない」など、どうしても固定しなければいけないこともあると思います
でもやりたいことは、下から順番に見ていった最初の商品名を表示したい訳です
では、どうしたら順番を変えずに上下を反対にできるのか考えてみましょう
その答えがCOUNTIF関数を活用する方法です
まずは答えから見てみましょう
案外ごちゃごちゃしていますが、ここまで設定することが出来れば「下から検索したことと一緒の結果を表示する」ということが出来ます
次は順番に設定方法を見ていきましょう
B11セルに設定してあるCOUNTIF関数が今回のポイントです
「=COUNTIF(C11:$C$25,C11)」という式は日本語にすると「=COUNTIF(C11のセル～C25のセルの範囲で,C11のセルの内容が全部で〇〇あります)」
という意味になります
この設定の場合「C11のセル～C25のセルには全部で５つテントという項目」があります
では、この式をコピーするとどうなるでしょう
こんな風に「検索する対象の範囲」が１行分減っていきます
これが25行目までくると「=COUNTIF(C25:$C$25,C25)」となって「C25のセルの内容はC25のセル範囲の中にいくつありますか？」となって答えは1になります
こんな風に検索できる範囲を自動的に変えることで、降順(大きい順)でカテゴリー別に連番を振ることが出来ます
関数を使った連番については、「SUBTOTAL関数でサクッと自動連番 覚えておけばこりゃ便利」という記事でも解説しているので参考にしてみて下さい
コレで下準備は出来ました
次は「１」という数字に注目してみましょう
連番を振った結果「１」になっているということは、「他に同じモノが無い」つまり「コレ以上無い=最新」と同じことです
そして「１」が表示されるタイミングは同じカテゴリーでも一番下になります
「同じカテゴリーで連番が１」だったら「商品名を表示」という条件にすれば、下から検索したのと一緒の意味になります
ここまで来たらもうひと工夫する必要があります
それは、この２つの条件を満たすための「ユニークな検索値」を作ることです
ユニークな値を作ることが出来れば、VLOOKUP関数でも簡単に検索することができます
設定方法はすごく簡単なのでやり方を見てみましょう
こんな風に「B11のセルとC11のセルを＆でくっつける」これだけです
こうすればユニークな検索値ができるのでこの値を基にVLOOKUP関数で商品名を表示させればOKですよね
ユニークな値については、「VLOOKUP関数で複数条件検索するにはユニークな値を作る」という記事でも紹介しているので参考にしてみて下さい
次は仕上げです
「C5のセルにユニークな値で検索できる」VLOOKUP関数の設定をしてみましょう
やり方はすごく簡単です
C5セルに「=VLOOKUP(A5&B5,A11:D25,4,0)」と入力します
これは、検索番号とカテゴリーをくっつけてユニークな値にし「テント商品一覧表」の中を検索することで商品名を表示するという方法です
この方法は、下から検索した訳ではなく「条件を上手く作った結果」一番下の商品名を表示できる仕組みを作ったというのが正解です
こんな風に発想を変えて工夫することでやりたいことが出来るようになります
繰り返しになりますが、VLOOKUP関数で下から検索したい場合は、COUNTIF関数で連番を振ると実現出来ます
XLOOKUP関数を使えばVLOOKUP関数より簡単に下から検索することが出来ます
XLOOKUP関数を使うことでVLOOKUP関数よりも簡単に同じ結果を表示することが出来ます
なぜなら、XLOOKUP関数の方が融通がきくように改良されたVLOOKUP関数の進化した関数だからです
では実際にXLOOKUP関数を使って同じことをしてみましょう
まずは結論から見ていきましょう
こんな風に「XLOOKUP関数」を使えば、VLOOKUP関数を使うよりも遥かに簡単でシンプルに下から検索することが出来ちゃいます
では、XLOOKUP関数の使い方を見ていきましょう
「=XLOOKUP(A5,A11:A25,B11:B25,“見つかりません”,0,-1)」
コレを日本語にすると
「=XLOOKUP(A5セルの値を検索してね,表示する範囲はB11セル～B25セルでA5セルの値と一致したモノを,検索した結果見つからなかったら「見つかりません」と表示してね,完全に一致する値で,下から上方向に向かって)」
といった感じでしょうか
文章にするとすごく長くて複雑ですが、検索する範囲と表示する範囲・検索の方向などを指定出来るので、VLOOKUP関数を使いこなせれば意外と簡単に使えます
動きを図にするとこんな感じです
見ての通り下から検索して一致したら任意の値を返してくれています
なんだか、VLOOKUP関数とIF関数のいいとこ取りをしたような関数ですね
この関数であれば、今回の目的をサクッと解決してくれるので好きな方法を使ってみて下さい
但し、どんな関数でも同じですが他の人が古いOfficeを使っている場合はこの方法は使えないので注意して下さい
繰り返しになりますが、XLOOKUP関数を使うことでVLOOKUP関数よりも簡単に同じ結果を表示することが出来ます
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す