枠内空欄に文字を入力

質問

B列の番号付氏名をD列番号にマッチする氏名をE列に入力したいのですが。
現在A列にB列氏名の先頭２文字の番号（空白を含む）を下記の式ー１（A2)に入力し下方にオートフィルでA11まで入力してE列には式ー２(E2)を入れて下方にオートフィルでE11まで入力した結果です。同じ氏名番号を持つ、4後藤及び6清原は入力できずE列に氏名番号がない３番、７番のセルE4とE8がISERROR関数により空白になります。4後藤及び6清原もこの空白になった場所に番号違いでも強制的に入力し全員の氏名を枠内に収めたいのですが。（空白にはどちらが前後になっても構いません）
どなたか関数の追記、訂正を宜しくお願いします。またVBA関数もお解りになる方宜しくお願いします。Office　2013を使用してます。
式－１(セル"A2")　　=LEFT(B2,2)　　
式－２(セル"E2")　　=IF(ISERROR(VLOOKUP(D2,$A$2:$B$11,2,0)),"",VLOOKUP(D2,$A$2:$B$11,2,0))

回答No.3

bunjii · ベストアンサー

VLOOKUP関数で抽出しようとしていることが誤りです。
A列の番号と行番号の合成値を使って小さい順にB列の氏名を抽出する方法がベターではないでしょうか？
E2=INDEX(B:B,MOD(SMALL(INDEX(A$2:A$11*1000+ROW(A$2:A$11),0),D2),1000))
E2セルを下へE11までオートフィルコピーすれば目的通りになります。

回答No.4

msMike

[No.2補足]へのコメント、
》 配列数式は｛｝で囲むではないのでしょうか
違います！

式は必ず配列数式として入力のこと、の意味
此処では式を例えば =SUM((A1:A10)*(B1:B10)) としておく
１．該当式をコピーして指定のセルに入力(でも、まだ Enterキー
￣￣を叩かない！)
２．Ctrl+Shift+Enter を「エイヤッ！」と叩き付け
３．[数式バー]を眺めて、下記のように、入力した式が ｛ ｝で囲
￣￣まれていることを確認
￣￣ {=SUM((A1:A10)*(B1:B10))} ←配列数式！

つまり、入力する貴方が{ }で囲むのではなく、ステップ２を実行すると自動的に{ }で囲まれる、ということです。

お礼

コメント回答も有難うございます。勉強になりました。

回答No.2

msMike

１．次の各セルにその右に示す式を入力
￣￣ G2: =COUNTIF(A$2:A2,A2)
￣￣ H2: =VLOOKUP(D2,A$2:B$11,2,FALSE)
￣￣ I2: =COUNTIF(H$2:H2,#N/A)
２．範囲 G2:I2 を下方にオートフィル
３．次式を入力したセル E2 を下方にオートフィル
￣￣ =IFERROR(H2,INDEX(B$1:B$11,SMALL(IF(G$2:G$11>1,ROW(A$2:A$11),""),I2)))
￣￣【お断り】上式は必ず配列数式として入力のこと

お礼

有難うございます。

補足

有難うございます。上記の式を入力してG,H,I列は上図のようになったのですがE列に配列数式{=IFERROR(H2,INDEX(B$1:B$11,SMALL(IF(G$2:G$11>1,ROW(A$2:A$11),""),I2)))}を入力してもE列すべて#NAME?となります。配列数式は｛｝で囲むではないのでしょうか。また現在パソコン環境がOffice　2003だからでしょうか。すみませんが宜しくお願いします。

回答No.1

aokii

関数は正しいようです。
先頭２文字の番号は同じ番号にならないようにしてみてはいかがでしょう。

お礼

ご回答有難うございます。

補足

有難うございます。グループごとに氏名番号が与えられておりグループ別に同番号があります。今回の枠内に氏名を入れるのは各グループ混合チームとなりますので同番号が発生します。番号を変えれば問題ないのですがそれができない状況となってます。誠に恐れ入ります。