次のようなバッグの一覧があります。中に入る書類の最大寸法と型番が書いてあります。
そしてバッグに入れたい財布の寸法の一覧表があります。
理想の答え欄のように、それぞれの財布が入るできるだけ小さいバッグを用意したいのです。
簡単ですよね、おなじみのVLOOKUP関数の不一致を使います。
では、セルD2に次の計算式を入れ、コピーしてみます。
=VLOOKUP(D2,$A$2:$B$12,2,TRUE)
なんということでしょう。値がずれてしまっています。
今回求めたいものは、その寸法が入る一番初めのもの、つまり、数値「以上」で一番近いもの。対してVLOOKUP関数の不一致は、数値「以下」で一番近いものを探します。
「以上」と「以下」の違いがあり、「以上」はVLOOKUP関数はおろか、他の関数でも、執筆時点で思いつきません。
そこで次のように考えます。
まず、一致しているものはそのまま出していいので、VLOOKUP関数の一致モードで値を求めます。とりあえず不一致はエラーで問題なしです。
=VLOOKUP(D2,$A$2:$B$12,2,FALSE)
で、不一致だった場合、#N/Aエラーが出ますが、その場合は違う方法で求めます。
やってることはVLOOKUP関数に近いので、それらしい関数、となるとINDEX関数とMATCH関数の組みわせかなと。
で、単純にMATCH関数のそれ以下一致とINDEX関数を組み合わせます。
=INDEX($B$2:$B$12,MATCH(D2,$A$2:$A$12,1))
当然ながらVLOOKUP関数と同じ答え。
じゃあ、MATCH関数のそれ以上一致を使ってみようかと。
=INDEX($B$2:$B$12,MATCH(D2,$A$2:$A$12,-1))
全部#N/Aエラー。それ以上一致は、元表が降順で並んでいればできたりはします。
でも元表を変えるのっていやですよね。私はそう思います。
MATCH関数のそれ以下一致とINDEX関数の組み合わせのケースをもう一度見直してみると。
この表は、VLOOKUP関数の一致で求まる、一致している値を抜いたものです。
お気づきになりました？
非常に惜しくて、ひとつ下のものが求まってるのです。
じゃあ、MATCH関数で求めた順番の位置をひとつ下にずれるように1を足せばいいかなと。
=INDEX($B$2:$B$12,MATCH(D2,$A$2:$A$12,1)+1)
はい、一致しました。最後の1000は、そんなバッグはないよということでエラーになっています。
では、ここまでの計算式をまとめてみればいいのかなと思いました。
まず、一致のVLOOKUP関数で一致しているものを求めます。
もしVLOOKUP関数で一致しなくてエラーになったら、INDEX関数とそれ以下のMATCH関数に＋１したものの組み合わせ。
それもエラーなら寸法オーバーなので「OVER」と表示。
これをIFERROR関数でくみあげていくと、わかりやすいようにインデントしています。
=IFERROR(
VLOOKUP(D2,$A$2:$B$12,2,FALSE)
,
IFERROR(
INDEX($B$2:$B$12,MATCH(D2,$A$2:$A$12,1)+1)
,
“OVER”
)
)
となります。そのまま1行にすると次の計算式です。
=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,FALSE),IFERROR(INDEX($B$2:$B$12,MATCH(D2,$A$2:$A$12,1)+1),”OVER”))
数値以上の一番近い値を出すVLOOKUP関数があれば一番解決なのですけど、いまのところ、これが最善策のようです。
コメント