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

質問

先日ここで同じ質問をさせてもらって、その時にCOUNTIFを使う方法を教えていただいてこの方法なら何番目の値でも簡単に指定できると思い実際に試してみたのですが当初は2つ目までしか考慮していなかったのですが、シートによっては同じ値（A123）が2回以上入力されるケースもあることがわかりました。

そこで＜表ー1＞のC8にABC123と入力されたら左の列にC8＆COUNTIF関数で何番目のABC123かを表示させて、この末尾2のセルの3列目（X123）を＜表ー2＞の該当する品名の横に表示させたいのです。
添付の場合A123の横にX123と入るようにしたいのです。
A123が2回しか出てこないなら、VLOOKUPで簡単に検索できそうなのですが、それ以上出てくることもあるのでハタと困ってしまいました。

ちなみに、VLOOKUP（F3＆COUNTIF(　),B：D,3）のような使い方はできないのでしょうか?
上記の使い方ができなければ、表ー2にも補助カラムを追加してB列と同じ内容にすることは可能です。

回答No.1

nishi6 · ベストアンサー

添付図のようなセル関係で、セルG8が対象セルとして、
セルG8：=IFERROR(VLOOKUP(F8&"2",$B$8:$D$23,3,0),"-")
後は下へコピーします。

＞VLOOKUP（F3＆COUNTIF(　),B：D,3）のような使い方はできない
＞のでしょうか?
前回回答したユーザー―定義関数がこれに当たります。
ワークシート関数のみでは、希望されている、「F3＆COUNTIF(　)」がシート上にないため使えないわけです。そのため、一番左の列に補助列を作るわけです。

また、補助列で２つのキーを結合すると、別のキーなのに同じキーになってしまう場合があります、（例えば、AB1の1番目とABの11番目など）。この質問ではないんでしょうね。実務では、全件、このようなことが起きないことを保証する必要があるのでキーの単純な結合は行いませんでした。どうしても必要なときは、間に絶対現れない文字を挟んだりしました。

＞表ー2にも補助カラムを追加してB列と同じ内容にすることは可能です
ユーザー定義関数なら、補助列を作る必要がないわけです。

補助列を作っていくと、徐々に目的が分からなくなっていく例ですね.補助列も良し悪しです。ご参考に。

お礼

最初に完全正解版をご回答いただいていたのに当方の実力のために2度手間をおかけしてしまい申し訳ありませんでした。

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

補足

一発で希望通りの結果が得られました。
色々試行した中にご回答の式もあったはずですが何故か期待通りの結果が得られなかったので検索値に＆は羽化得ないのでは、と思ってしまいました。
ご回答でうまくいったのでIFRERRORをとってみたのですがやはりチャンと動きます。
原因不明ながらおかげさまで解決しました．

回答No.4

bunjii

回答No.2の追加です。
質問の添付画像を勝手解釈で2つのSheetに分けた状態にさせて頂きます。
Sheet1のA:Cに元データを作成します。
VLOOKUP関数の比較値はSheet2のA列と数式を設定するセルの列番号を連結した値（文字列）とします。
範囲はSheet1の$A:$Cとして抽出する列位置は3番目のC列とします。
Sheet2のA列には品名（A123等）でB列に2番目、C列に3番目、D列には4番目のようにSheet1のB列に現れる品名の順番に応じた型番（X123等）を抽出します。
Sheet2のB1に次の数式を設定します。
=IFERROR(VLOOKUP($A1&COLUMN(),Sheet1!$A:$C,3,FALSE),"")
VLOOKUP関数の4番目の引数（FALSE）は検索値と完全一致を指定しますので省略すると目的に合いません。
B1セルを右と下へ必要数コピーすれば目的の型番が得られるはずです。
Excel 2013で検証した結果を画像で添付します。

お礼

本当にご丁寧なご回答ありがとうございました。

補足

ご推察通りの内容の質問でした。

一発でご回答通りの結果が得られました。
最初は2番目に出現した時のC列（にしてましたっけ?)だけの参照のつもりだったのですが、3番目以降の値が必要なケースもあり得るのでまさに一歩先を行くご回答でした。

回答No.3

Mathmi

ごめんなさい。どのような作業をしたいのかが分かりづらいです。
また「C8にABC123と入力されたら」などありますが、添付図のどのセルがC8なのか分からないので推測するしかありません。

前回のものも含め、3パターンほど挙げます。

前回の質問も合わせて考えると
・sheet1のA列に検索用の関数が入っている(countif(B$1:B2,B2)など)
・sheet1のB列には製品名が入っている。
・sheet1のC列には型番が入っている。
・sheet2のA列に、検索したい製品名が入っている。
という状態で

・B列の中でn番目の特定の製品名の行にあるC列の型番を取り出したい。（nは固定）
→前回の回答通り、[=vlookup(製品名&n,sheet1!$A:$C,3,false)]のような関数で可能です。

・B列の中で最後の特定の製品名の行にあるC列の型番を取り出したい。
→[countif(sheet1!$B:$B,製品名)]のような関数で、その製品名の最後が何番目かを取得できます。
[=vlookup(製品名&countif(sheet1!$B:$B,製品名),sheet1!$A:$C,3,false)]のようになります。

・表2の特定の列に特定の製品名が複数存在する。表2の1番目の行には表1の1番目のC列の値を、表2の2番目の行には表1の2番目のC列の値を……と取り出したい。
→検索用の列と同様、[countif($A:$A,A2)]のような関数で、表2の何番目の特定の製品名かを取得できます。
[=vlookup(製品名&countif($A:$A,A2),sheet1!$A:$C,3,false)]のような関数で可能です。

＞補助列で２つのキーを結合すると、別のキーなのに同じキーになってしまう場合があります
確かに、これは明記しておくべき項目ですね。no.1の方、ありがとうございます。

お礼

本当にご丁寧な説明（回答）ありがとうございます。
質問を書いてしまってから表をわかりやすい?ように列を変えたのが原因で混乱を招いてしまいました。

ご確認の内容がまさに当方の要求事項です。
ご回答者様各位にとっては、ご正解回答よりこの推理のほうが難しかったようで反省しています。

＃No1さんのご回答で正解がわかったら、上記のご回答の意味がよく理解できました。

回答No.2

bunjii

>添付の場合A123の横にX123と入るようにしたいのです。
添付画像には＜表ー１＞、＜表－２＞共行番号と列記号が表示されていないので適切な数式を検証できません。

>ちなみに、VLOOKUP（F3＆COUNTIF(　),B：D,3）のような使い方はできないのでしょうか?
＜表ー１＞の「A1231」と表示されているセルはB1でしょうか？
また、数式が設定されていると思いますが、その数式を提示してください。
例　=COUNTIF(C$1:C1,C1)
＜表ー２＞の最初の行にある「A123」はF1セルでしょうか？
提示の数式と添付画像のセル番地に矛盾があるように思いますので質問の主旨を読み取れません。

＜表ー１＞の2列目と＜表ー２＞の1列目が同じ順番のときは数式を使う必要がないと思います。（必要な範囲を選んでコピー＆ペーストで良い）

お礼

そういえば今回は表を添付することに意識が行ってしまい列行が入っていないことに気づきませんでした。
また、本来別のシートの表の話なので列を見やすいように細工した際にセル番地がずれてしまいました。
また、この表は下に伸びていくデータ表なのでコピペでは対応が難しいと思いました。
入力値の2番目を見つけるのが似た文字列なので大変です。
説明不足ですみませんでした。