HLOOKUPで複数の結果を取得する方法

質問

元データ

1 　2 　4 　2 　2 　2 　3 　3 　3 　2 　1
A　B　 C　D　E　 F　 G　 H　 I　 J　 K

から、以下のように、
１を入力すると、A と K が、
３を入力すると、G と H と I が右のセルに表示される、といったものを
作成しているのですが、なかなかうまくいきません・・・。

↓入力データ
1　　A　　　K
2　　B　　　D　　　E　　　F　　　J
3　　G　　　H　　　I
4　　C
　　↑　　　↑ここから右、HLOOKUPで引っ張れないデータ
　　↑HLOOKUPで引っ張れるデータ

HLOOKUP関数を駆使すれば、なんとかなりそうな気もするのですが・・・

どなたかご教授願えないでしょうか？？

回答No.9

noname#52504 · ベストアンサー

#3です。
ん～っと、私の式はデータや表示位置が違う場合もあらかじめ考慮していたつもりなんですが…。

私が提示した式は
　B5：=IF(COLUMN()-COLUMN($A5)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,MATCH(LARGE(($A$1:$K$1=$A5)/COLUMN($A$1:$K$1),COLUMN()-COLUMN($A5)),1/COLUMN($A$1:$K$1),0)))
でしたね
　・$A5　⇒　$E43
　・$A$1:$K$1　⇒　$D$1:$N$1
　・$A$2:$K$2　⇒　$D$4:$N$4
と置き換えるだけです。(Excel2003で動作確認済)

　F43：=IF(COLUMN()-COLUMN($E43)>COUNTIF($D$1:$N$1,$E43),"",INDEX($D$4:$N$4,MATCH(LARGE(($D$1:$N$1=$E43)/COLUMN($D$1:$N$1),COLUMN()-COLUMN($E43)),1/COLUMN($D$1:$N$1),0)))

他の方の式については、データや表示位置がA列からあることを前提にしていらっしゃるようなので、
直接数字を差し引きして調整する必要があるかと思います。

お礼

ありがとうございます！無事、出来ました！

※置き換えしていたのですが、今、式を見直してみたら
　置き換えに漏れがありました・・・。
　当方の初歩的なミスのせいで お手数をおかけしてしまい、
　申し訳ありませんでした。

回答No.11

maron--5

>￥＝A44を例にとると、「B,D,E,F,J」と表示させたい
★F44ではないでしょうか？
F44=IF(COLUMN(A1)>COUNTIF($D$1:$N$1,$E43),"",INDEX($D$4:$N$4,SMALL(INDEX(SUBSTITUTE(($D$1:$N$1=$E43)*1,0,9^9)*COLUMN($A:$K),),COLUMN(A1))))
★右と下にコピー

回答No.10

noname#204879

[ANo.8この回答への補足]に対するコメント、

１．セル F43 に次の配列数式を入力して、Shift+Ctrl+Enterで確定
　　（数式バー上では式の左端と右端にそれぞれ { と } が付加される）
　　=OFFSET($D$4,0,SMALL(IF($D$1:$N$1=$E43,COLUMN($A1:$K1),""),COLUMN(A1))-1)
２．同セルに次の[条件付き書式]を設定
　　　　数式が　　　　=ISERROR(F43)
　　　　フォント色　　白
３．セル F43 を右方にズズーッと（セル P43 まで）ドラッグ＆ペースト
４．範囲 F43:P43を下方にズズーッとドラッグ＆ペースト

回答No.8

noname#204879

元データ(Sheet1)
　 A　B　C　D　E　F　G　H　I　J　K
1　1　2　4　2　2　2　3　3　3　2　1
2　A　B　C　D　E　F　G　H　I　J　K

入力データ(Sheet2)
　 A　B　C　D　E　F　G　H　I　J　K　L
1　1　A　K
2　2　B　D　E　F　J
3　3　G　H　I
4　4　C

１．Sheet2 のセル B1 に次の配列数式を入力して、Shift+Ctrl+Enterで確定
　　（数式バー上では式の左端と右端にそれぞれ { と } が付加される）
　　=OFFSET(Sheet1!$A$2,0,SMALL(IF(Sheet1!$A$1:$K$1=$A1,COLUMN($A1:$K1),""),COLUMN(A1))-1)
２．同セルに次の[条件付き書式]を設定
　　　　数式が　　　　=ISERROR(B1)
　　　　フォント色　　白
３．同セルを[コピー]して範囲 B1:L4 に[貼り付け]

補足

ご回答ありがとうございます！
これを、実際のデータ位置に置き換えて反映させてみようとしたのですが、
うまくいきませんでした・・・。

実際の元データの位置は、
D1～N1と、D4～N4で、
参照のためのデータを入力する位置は、
E43～、
データ表示位置は、
F43～
となります。
（以下参照）

　 A　B　C　D　E　F　G　H　I　J　K　L　M　N
1　@　@　@　1　2　4　2　2　2　3　3　3　2　1
2　@　@　@　@　@　@　@　@　@　@　@　@　@　@　
3　@　@　@　@　@　@　@　@　@　@　@　@　@　@
4　@　@　@　A　B　C　D　E　F　G　H　I　J　K
5
6
7
8
:
43　\　@　@　@　1　A　K
44　\　@　@　@　2　B　D　E　F　J
45　\　@　@　@　3　G　H　I
46　\　@　@　@　4　C
47　\　@　@　@　5
48　\　@　@　@　6
49　\　@　@　@　7
50　\　@　@　@　8
:

＠＝任意のデータ
￥＝A44を例にとると、「B,D,E,F,J」と表示させたい。

申し訳ありませんが、引き続きご教授願えないでしょうか？

回答No.7

ka_na_de

関数の質問でしたが、難しくて挫折しました。

ＶＢＡで作ってみましたので、ご参考までに

１）データが入力されると該当する値を横に表示する例
２）新規シートにデータを並べ替えてしまう例
　注）いずれも、元データはA1セルを基点に入力されている前提です。

１）の例　＜Ａ５セルに入力データを書き込む場合です。＞
'==============ワークシート・モジュールに記述===================
Private Sub Worksheet_Change(ByVal Target As Range)
　Dim c As Long

　If Target.Address <> "$A$5" Then Exit Sub

　Range(Target.Offset(, 1), Cells(Target.Row, Columns.Count)).ClearContents

　For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
　　If Cells(1, c).Value = Target.Value Then
　　　Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(, 1).Value = Cells(2, c).Value
　　End If
　Next c

End Sub

２）の例　（新規シートに並べ替えた一覧表を表示する例です。）
'=================標準モジュールに記述==========================
Sub test()
　Dim r As Long
　Dim LastRow_A As Long
　Dim LastCol_r As Long
　Dim LastCol_1 As Long

　Application.ScreenUpdating = False

　'データを新規シートに行列変換して貼り付け
　Range("A1").CurrentRegion.Copy
　Sheets.Add
　Range("A1").PasteSpecial Transpose:=True

　'番号順に並べ替え
　Columns("A:B").Sort Key1:=Range("A2"), Order1:=xlAscending

　'同一番号データを横方向へ移動（複数列にも対応）
　LastCol_1 = Cells(1, Columns.Count).End(xlToLeft).Column
　LastRow_A = Cells(Rows.Count, "A").End(xlUp).Row
　For r = LastRow_A To 2 Step -1
　　LastCol_r = Cells(r, Columns.Count).End(xlToLeft).Column
　　If Range("A" & r).Value = Range("A" & r - 1).Value Then
　　　 Range("A" & r).Resize(, LastCol_r - 1).Offset(, 1).Copy _
　　　　 Destination:=Cells(r - 1, LastCol_1 + 1)
　　　 Rows(r).Delete
　　End If
　Next r

　Application.ScreenUpdating = True

End Sub

回答No.6

maron--5

◆こんな方法もありますよ（少し、式が短くなりました）
B5=IF(COLUMN(A1)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,SMALL(INDEX(SUBSTITUTE(($A$1:$K$1=$A5)*1,0,9^9)*COLUMN($A:$K),),COLUMN(A1))))
★右と下にコピー

補足

ご回答ありがとうございます！
これを、実際のデータ位置に置き換えて反映させてみようとしたのですが、
うまくいきませんでした・・・。

実際の元データの位置は、
D1～N1と、D4～N4で、
参照のためのデータを入力する位置は、
E43～、
データ表示位置は、
F43～
となります。
（以下参照）

　 A　B　C　D　E　F　G　H　I　J　K　L　M　N
1　@　@　@　1　2　4　2　2　2　3　3　3　2　1
2　@　@　@　@　@　@　@　@　@　@　@　@　@　@　
3　@　@　@　@　@　@　@　@　@　@　@　@　@　@
4　@　@　@　A　B　C　D　E　F　G　H　I　J　K
5
6
7
8
:
43　\　@　@　@　1　A　K
44　\　@　@　@　2　B　D　E　F　J
45　\　@　@　@　3　G　H　I
46　\　@　@　@　4　C
47　\　@　@　@　5
48　\　@　@　@　6
49　\　@　@　@　7
50　\　@　@　@　8
:

＠＝任意のデータ
￥＝A44を例にとると、「B,D,E,F,J」と表示させたい。

申し訳ありませんが、引き続きご教授願えないでしょうか？

回答No.5

maron--5

◆少し、式が長いですがこんな方法もありますよ
　　　 A　　　B　　　C　　　D　　　E　　　F　　　G　　　H　　　I　　　J　　　K
1　　　1　　　 2　　　4　　　 2　　　2　　 　2　　　3　　　3　　　3　　　2　　 　1
2　　　A　　　B　　　C　　　D　　　E　　　F　　　G　　　H　　　I　　　J　　　K
3
4
5　　　1　　　A　　　K
6　　　2　　　B　　　D　　　E　　　F　　　J
7　　　3　　　G　　　H　　　I
8　　　4　　　C

B5=IF(COLUMN(A1)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,SUMPRODUCT(LARGE(($A$1:$K$1=$A5)*(COLUMN($A$1:$K$1)),1+COUNTIF($A$1:$K$1,$A5)-COLUMN(A1)))))

回答No.4

telescope

=HLOOKUP(INDIRECT("A"&ROW()),A$1:A$2,2,0)
と入力してフィルハンドルを右方向にL列までドラッグします。
そのまま下方向にドラッグします。
該当のないセルには「#N/A」とエラー値が表示されます。

「編集」－「ジャンプ」で「セル選択」ボタンを押し、
[数式]を選択して、[エラー値]以外のチェックをはずします。
[エラー値]の入ったセルが選択されます。
右クリックで「削除」で「左方向にシフト」を選択して「OK」ボタンを押します。

コピーして「形式を選択して貼り付け」で[値]のみ貼り付けます。
先に[値]のみ貼り付けた場合は、
[定数]を選択して、[エラー値]以外のチェックをはずします。

ちょっと面倒ですが、HLOOKUP関数を使ってやるならこんな方法でできそうです。

回答No.3

noname#52504

HLOOKUPは"最初にHITしたもの"しか返しませんから、
２番目にHITしたもの、３番目にHITしたもの、というふうに返すのは、
HLOOKUPをどう駆使しても難しいと思われます。
#2さんがおっしゃるように、まずユニークなコードを生成してそれを使って引くのが大原則です。

例えば、元のデータがA1から１行目と２行目に、「入力データ」の部分がA5から下方に入っているとして、
まず、
　A3:=COUNTIF($A1:A1,A1)&"-"&A1
として右方にフィル。これが「Ｎ番目の□」というユニークなコードになります。
次に、
　B5：=IF(COUNTIF($A$1:$K$1,$A5)<COLUMN()-COLUMN($A$5),"",INDEX($A$2:$K$2,MATCH(COLUMN()-COLUMN($A5)&"-"&$A5,$A$3:$K$3,0)))
として、下方・右方にフィルすればご要望のような結果が返ります。

略解
　・自セルの列位置Ｎ【COLUMN()-COLUMN($A5)】を取得する。
　・第１行について、検索値と等しい値がＮより少なければ空白を返す。
　・Ｎと検索値を組み合わせて検索用のコードを生成する。
　・そのコードを第３行から探して位置Ｍを取得する。
　・第２行について、Ｍ番目の値を返す。

一発でやろうとするならば、
　B5：=IF(COLUMN()-COLUMN($A5)>COUNTIF($A$1:$K$1,$A5),"",INDEX($A$2:$K$2,MATCH(LARGE(($A$1:$K$1=$A5)/COLUMN($A$1:$K$1),COLUMN()-COLUMN($A5)),1/COLUMN($A$1:$K$1),0)))
を配列数式として入力し、下方・右方にフィル。
※通常の数式は、数式を入力した後Enterキーで確定しますが、
　これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

略解
　・第１行について、検索値と等しければ列番号の逆数、でなければ０を返した配列を生成する。
　・それらのうち、Ｎ番目に大きい値の位置Ｍを検索する。
　後は上記と一緒です。

補足

ご回答ありがとうございます！
これを、実際のデータ位置に置き換えて反映させてみようとしたのですが、
うまくいきませんでした・・・。

実際の元データの位置は、
D1～N1と、D4～N4で、
参照のためのデータを入力する位置は、
E43～、
データ表示位置は、
F43～
となります。
（以下参照）

　 A　B　C　D　E　F　G　H　I　J　K　L　M　N
1　@　@　@　1　2　4　2　2　2　3　3　3　2　1
2　@　@　@　@　@　@　@　@　@　@　@　@　@　@　
3　@　@　@　@　@　@　@　@　@　@　@　@　@　@
4　@　@　@　A　B　C　D　E　F　G　H　I　J　K
5
6
7
8
:
43　\　@　@　@　1　A　K
44　\　@　@　@　2　B　D　E　F　J
45　\　@　@　@　3　G　H　I
46　\　@　@　@　4　C
47　\　@　@　@　5
48　\　@　@　@　6
49　\　@　@　@　7
50　\　@　@　@　8
:

＠＝任意のデータ
￥＝A44を例にとると、「B,D,E,F,J」と表示させたい。

申し訳ありませんが、引き続きご教授願えないでしょうか？

回答No.2

shintaro-2

>HLOOKUP関数を駆使すれば、なんとかなりそうな気もするのですが・・・

　思想として、コード入力をすると表から対応するものをもってくるというのは、

　コードがユニークであることが前提だと思うのです。
　ですので、もとのデータが、例えば３のセルの下に、GHIと3文字入っていないと引っ張れないのではないでしょうか？

　もし、この表で解決するとしたら
　3列目は参照する元の表を、2列目のデータをピックアップしたところから右側にずらすという操作が必要ではないでしょうか？

回答No.1

whiteline507

まず、横のデータを持ってくるのであればHLOOKUPじゃなくてVLOOKUPですね。
で、2と入力するとBDEFJと出したいのであれば
=VLOOKUP(A6,A1:C4,2,0)&VLOOKUP(A6,A1:C4,3,0)&VLOOKUP(A6,A1:C4,4,0)& ....
というようにVLOOKUPの3つめの数字を１つづつ増やしていって
&で繋げればよいかと思います。

もちろんBDEFJと最大何個までと分かっている場合だけですが。
延々と続く可能性があるのであれば無理っぽいですね。