エクセルの VLOOKUPで2番目の結果を参照

質問

重複する値を含む表のVLOOKUPで、上から検索した結果の2番目を参照したいのですが。

Sheet１のA列に製品名、B列に型番が入っているような表で、初回の製品名（A列）には型番が仮称で入っています。
次に色んな仕様が決まったら、Sheet1の最下行に製品名と正式な型番が入ります。
次にSheet2 で製品名を入れると、Sheet1からVLOOKUPで正式な型番（2番目の結果）を参照するようにできないでしょうか?

さらには希望する順位の値が参照できると更にありがたいのですが。
2番目限定でも十分です。

回答No.2

Mathmi · ベストアンサー

一番手っ取り早いのは、sheet1に検索用の列を追加する事です。

自分がするなら
・shet1のA列を挿入。
・A1に=B1&COUNTIF(B$1:B1,B1)、下にオートフィル
・検索関数として=VLOOKUP([検索文字列及び番号],Sheet1!A:C,3,FALSE)
といった感じでしょうか。

この[検索文字列及び番号]は任意に変更して下さい。
例えばA1セルに製品名、B1セルに何番目を検索するかの番号が入力されていた場合、A1&B1にする、等です。

お礼

これは目からウロコで試してみようと思います。

補足

検索したい（入力した）セル内容に２を&で付けてた文字列を、元のデータのセル内容にCOUNTIFのNoを付記した表からVLOOKUPで検索することで任意の発生回数の値を参照できることを確認しました。

幸い対象のBookは当方が管理している表なので列の挿入が可能なのでこれで何とかできそうです。

COUNTIF関数の新しい使い方は非常に参考になりました。

回答No.8

msMike

[No.7補足]へのコメント、
添付図参照
「最新番目」でなく「2番目」とする改訂版です。

Sheet2!B1: =IFERROR(INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!A$2:A$8=A2,ROW(A$2:A$8)),2)-1),"非該当")
【お断り】上式は必ず配列数式として入力のこと

【お願ひ】回答者を戸惑わせない書き方を最初から。つまり、チコちゃんに叱られないような書き方を頼ンますネっ！

もう、出直さなくて結構です。

お礼

何度も丁寧にご回答いただき感謝です。

半日かかってどうにか見本通りに動くまでになりましたが、本チャンの表だとなぜかうまくいきません。
配列数式を使ったことがないので、この辺が実力かと。

ほかの方法で検討してみます。
お手数をおかけしてしまいました。

次回は図が添付できるようにしてから質問させていただきます。

回答No.7

msMike

[No.6]で「添付図参照」と言い乍ら、忘れていたものです。m(_._)m

お礼

質問内容が明確ではなくお手数をとらせてしまい申し訳ありません。
出直します、ごめんなさい。

補足

A列の各製品名の2番目の値を参照したい、が要求でした。
つまり製品１なら該当なし（2つ目の製品１が入力されていないので）、製品２は型番５、製品3は型番６、製品４は該当なし。
質問は悪くすみませんでした。

回答No.6

msMike

迅速な補足無しなので勝手乍ら、最新番目の順位の値が参照する方式をば。

添付図参照
Sheet2!B2: INDEX(Sheet1!B$2:B$8,MAX(IF(Sheet1!A$2:A$8=A2,ROW(A$2:A$8)))-1)
【お断り】上式は必ず配列数式として入力のこと

お礼

ご丁寧に何度もご回答感謝です。
VLOOKUPで何とかなると思っていましたが、残念ながら当方にはハードルが高い内容のようで出直します。

回答No.5

msMike

》 希望する順位の値が参照できると更にありがたい
「希望する順位の値」とはどういうことですか？

》 2番目限定でも十分です
貴方が先走って妥協する必要もないけど、2番目とか1番目とかを含むサンプル表を行列番号入りで提示されたい。最大何番目まで存在するの？

補足

Sheet1のA列にABCという製品名が何度か出てきて、複数の各ABCに対応する情報がB列に記載されていて、Sheet2でABCと入力した時に、VLOOKUPで検索したときに、Sheet1のB列の2番目のセル値を参照したいのです。

分かりにくい質問内容がますますわかりにくくなってしまったようですのでここでやめます。
説明下手でごめんなさい。

会社のPCではなぜか、表の添付がうまくいなないので皆様に迷惑をかけているようです。

回答No.4

imogasi

これは無理です。エクセルの、検索に関連したVLOOKUP（HLOOKUP,LOOKUP)、MATCH関数などは、最初に見つかったものしか検索（結果を返して）くれません。
　不便だなあと思うときがありますが、元のソフトからの継承や歴史的なこと等の事情があるのでしょう。もう１つ何番目を言うのを指定する引数（第５引数）を設けてもらえばよいのですが、MSで検討されるかどうかわからない。
VBAを勉強すれば、同等のことはできます。勉強してみて。

お礼

ご回答ありがとうございます。
やはりVLOOKUP単独ではどうしようもないということですね。
参考になりました。

回答No.3

nishi6

添付図のようなSheet1のデータで考えました。
2番目の型番の表示は、Sheet2のセルC2で、
=IFERROR(VLOOKUP(A2, INDIRECT("Sheet1!$A$"& (MATCH(A2,INDIRECT("Sheet1!$A$2:$A$" & COUNTA(Sheet1!A:A)),0)+2) & ":B" & COUNTA(Sheet1!A:A)),2,0),"なし")

とします。Sheet1のデータが増えても対応します。

このまま関数で、3番目、4番目の型番の表示を行うのは生産性が悪いので、Vlookup関数を拡張するユーザー定義関数を作りました。標準モジュールに貼りつけます。何番目まででも表示しますが、なければ「なし」を表示します。

使い方は、
=exVlookup(検索値,範囲,列番号,番目)　です。ほとんどVlookup関数と同じです。
添付図セルC8の例　：　=exVlookup($A8,Sheet1!$A$1:$B$9,2,2)

Function exVlookup(sch As Range, Rng As Range, dt As Integer, ord As Integer)
　Dim r As Integer　　 '// 行カウンタ
　Dim wk As Variant　　'// ワーク変数
　Dim cnt As Integer　 '// 一致カウンタ

　With Rng.Cells(1, 1)
　　For r = 1 To Rng.Rows.Count
　　　If .Offset(r, 0) = sch.Value Then
　　　　cnt = cnt + 1
　　　　If cnt = ord Then
　　　　　wk = .Offset(r, dt - 1)
　　　　　Exit For
　　　　End If
　　　End If
　　Next
　End With

　If wk = 0 Then
　　wk = "なし"
　End If
　exVlookup = wk
End Function

お礼

早々のご回答ありがとうございます。
2番目を参照する関数は当方では、色んな表で期待通りに動くまでに相当の時間がかかりそうで、難しすぎて使えそうもなく。

VBAは当方の知識では極力使わない方が安全、というレベルなので・・・
わざわざコードを作成してくださりありがとうございました。

回答No.1

bunjii

抽象的な質問なので具体的な回答は無理かと思います。
サンプルデータ（列記号と行番号も含む）を提示して、抽出結果も例示してください。
考え方としてはVLOOKUP関数に拘らず他の関数を組み合わせた数式で対応された方が良いでしょう。

お礼

いつも的確なアドバイスありがとうございます。
早々に色々なご回答をいただいており、確かに他の関数を組み合わせても解決できそうですが残念ながら当方の知識ではVLOOKUPしか使いこなせないと思われまず。
これから各ご回答の検証をやってみます。