【Excel】VLOOKUP検索値が複数あった場合、下の値を表示したい

D列のコードをA列の中で検索し、
B列の値をE列に表示したいのですが、
VLOOKUPを使用した場合、検索値の最初の行が表示されます。
同じコードの場合、一番下の行の値を表示するにはどうしたら良いでしょうか。

No.1 回答者： tom04 回答日時： 2018/12/07 19:39

こんばんは！

画像の配置だとE2セルに
=INDEX(B$1:B$1000,MAX(IF((A$1:A$1000=D2)*(B$1:B$1000<>""),ROW(A$1:A$1000))))

配列数式なので、Ctrl＋Shift＋Enterで確定！（←必須★）し
フィルハンドルで下へコピーしてみてください。

※　エラー処理はしていません。

※　万一、データの最終行が空白の場合、
A列が対象行でB列にデータが入っている最終行のデータが表示されます。m(_ _)m

この回答へのお礼

ご回答ありがとうございます。

例の表に式を入れまして、うまく表示されました。
ありがとうございます。

ただ、実シートに入力しますと、答えが0と表示されるセルがあり、
これの原因がわかりませんでした。
B列には空白か値になっています。
「ゼロ値のセルにゼロを表示する」にはチェックが入っています。

後で教えていただきました LOOKUPでは0ではなく、
その下の値が表示され、希望の答えとなりました。

ありがとうございました。

お礼日時：2018/12/09 12:50

No.2 回答者： mike_g 回答日時： 2018/12/07 22:19

チト気になったので、確認させてください。

貴方は、前回
https://oshiete.goo.ne.jp/qa/10863837.html
で、 WindFallerさんのみならず、私の回答に対しても判で押したように
「何故か上手くいかない行があります」
と仰いました。
それは何行目でしたか？参考までにどういう結果になったので「上手くいかない」と発言されたのでしょうか？
イイカゲンなコメントでないと信じてお尋ねしています。よろしくお願いします。

この回答へのお礼

前回ご回答頂きました際には、大変申し訳ございませんでした。

教えていただきました式は問題なく、
例に挙げました表で思っていた値が表示されました。
ありがとうございました。

ただ、実際のシートに落とし込んでみると異なる値が表示されてしまう行が出てきてしまい、
原因追求してみましたが、私の知識ではこの原因を切り分けることができませんでした。
実シート中で関係するセルを、例の表と比較してみましたが違いがわかりませんでした。

上手くいかなかったのは、私がこの式を扱うことができなかったためであり、
どこが上手く行っていないのかさえ、説明ができない状態でした。
教えていただきました回答は正解でしたので、このまま他の回答を頂きましても、
同様のお返事をすることとなりそうでしたので、慌てて先にお返事いただきました方に
ベストアンサーとして閉じさせていただいた次第でございます。

質問に挙げさせていただきました表が、実シートの内容に即していないのではないかと思い、
実シートを一部簡単にして、もう一度質問してみました。

お許しくださいませ。

お礼日時：2018/12/09 11:45

No.3 ベストアンサー 回答者： ママチャリ 回答日時： 2018/12/08 07:47

添付画像の通りの並びが前提となってしまいますが、こんな感じでどうでしょう。

【E2セル】=INDEX(B:B,MATCH(D2,A:A,0)+COUNTIF(A:A,D2)-1)

この回答へのお礼

ご回答、ありがとうございます。

こちらの式で希望の答えが表示できました。
ありがとうございました。

配列数式にしますと、計算に大変時間がかかるようになりました。
今回は、こちらの式で進めようと思います。

お礼日時：2018/12/09 13:06

No.4 回答者： mike_g 回答日時： 2018/12/08 09:27

今回は「枝番」がないだけ、やや簡単な式になるだけです。

E2: =INDEX(B$1:B$8,MAX(IF(A$2:A$8=D2,ROW(A$2:A$8))))
【お断り】上式は必ず配列数式として入力のこと

さて、今回も「何故か上手くいかない行があります」になりますか、なりませんか？
なるなら、それは何処ですか？

この回答へのお礼

こちらのご回答の式も、例の表では、
きちんと希望の値が表示されました。
ありがとうございました。

tom04様へのお礼でも述べましたとおり、
こちらの式を実シートに入力しますと、
答えが0と表示されるセルがあり、
これの原因がわかりませんでした。
B列にはいずれも空白か値になっています。
「ゼロ値のセルにゼロを表示する」にはチェックが入っています。

例の表では、思っていた答えが表示されますので、
私の質問の仕方が間違っているのだと思います。
申し訳ございません。

お礼日時：2018/12/09 13:22

No.5 回答者： WindFaller 回答日時： 2018/12/09 00:33

こんばんは。

E2:
=LOOKUP(D2,$A$2:$B$8)

配列の確定は不要です。第二引数が、配列になっているからです。
でも、前回の回答の答えも生きていると思います。
エラーになる原因としては、ハイフン（-)の原因だとは思いますが、いろいろな種類が混じってる可能性が高いです。置換機能で統一することを望みます。

この回答へのお礼

ご回答ありがとうございます。

こちらの式も、希望の答えが表示できました。
ありがとうございます。

1000行程度の表ですが、配列数式を使用しますと、
再計算に大変時間がかかるようになってしまいますので、
こちらの式は助かりました。
ありがとうございました。

お礼日時：2018/12/09 14:22

No.6 回答者： grumpy_the_dwarf 回答日時： 2018/12/09 10:56

WindFallerさんの式がうまくいくためには、A列が文字コード順に並べ替えられていることが条件になります。

他の人たちの式が「配列数式」で確定する必要があるのは、if関数の中身に暗黙の配列を使っているからです。if関数を使わずに条件を記述しindex関数で配列を明示してやれば、メンテナンスが楽になりますね。たとえば

=index(B:B, max(index((A:A=D2)*isnuber(B:B)*row(A:A),0)))

みたいな感じです。

No.7 回答者： tom04 回答日時： 2018/12/09 17:52

No.1です。

解決済みのようなので、余計なお世話になるかもしれませんが・・・

＞「ゼロ値のセルにゼロを表示する」にはチェックが入っています。
前回は「空白以外」の条件にしていたので、「0」以外にすれば大丈夫だと思います。

=INDEX(B$1:B$1000,MAX(IF((A$1:A$1000=D2)*(B$1:B$1000<>0),ROW(A$1:A$1000))))

こんな感じで。

※　前回同様配列数式です。m(_ _)m

この回答へのお礼

ご回答ありがとうございます。

＞「0」以外にすれば・・・
そんなこともできるんですね。
希望の値を表示することができました。
ありがとうございました。

ただ、先程も述べましたとおり、
配列数式は、再計算に時間がかかるため、
他の方の回答の式を使わせていただきました。

いろいろとありがとうございました。
今後もよろしくお願いいたします。

お礼日時：2018/12/09 18:03