Lookup Data with Ignoring Blank rows

[SunnyAlv]

it has been explained through pictures, how to fiks this problem ? thanks

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E	F	G	H
1	Brand	ID	Name		Brand	ID	Name
2	Honda	123			Honda	123	Indah
3	Honda	123	Indah		Wuling	456	Agifi
4	Wuling	456	Agifi		Hyundai	978	Anisa
5	Wuling	456
6	Wuling
7	Hyundai
8	Hyundai	978
9	Hyundai	978	Anisa
10
11
12
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($B$2:$C$9,AGGREGATE(15,6,(ROW($B$2:$B$9)-ROW($B$2)+1)/(($B$2:$B$9<>"")*($A$2:$A$9=$E2)),1),MATCH(F$1,$B$1:$C$1,0))
G2:G4	G2	=INDEX($B$2:$C$9,AGGREGATE(15,6,(ROW($B$2:$B$9)-ROW($B$2)+1)/(($C$2:$C$9<>"")*($A$2:$A$9=$E2)),1),MATCH(G$1,$B$1:$C$1,0))

 

[Skybluekid]

Try the below

Book1.xlsm
	A	B	C	D	E	F	G
1	Brand	ID	Name		Brand	ID	Name
2	Honda	123			Honda	123	Indah
3	Honda	123	Indah		Wuling	456	Agifi
4	Wuling	456	Agifi		Hyundai	978	Anisa
5	Wuling	456
6	Wuling
7	Hyundai
8	Hyundai	978
9	Hyundai	978	Anisa
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(A2:A9)
F2:F4	F2	=INDEX($B$2:$B$9,AGGREGATE(15,6,ROW($B$2:$B$9)-ROW($B$1)/(($A$2:$A$9=E2)*($B$2:$B$9<>"")),1))
G2:G4	G2	=INDEX($C$2:$C$9,AGGREGATE(15,6,ROW($C$2:$C$9)-ROW($B$1)/(($A$2:$A$9=E2)*($C$2:$C$9<>"")),1))
Dynamic array formulas.

 

[Peter_SSs]

Based on that sample data, possibly this?

23 05 29.xlsm
	A	B	C	D	E	F	G
1	Brand	ID	Name		Brand	ID	Name
2	Honda	123			Honda	123	Indah
3	Honda	123	Indah		Wuling	456	Agifi
4	Wuling	456	Agifi		Hyundai	978	Anisa
5	Wuling	456
6	Wuling
7	Hyundai
8	Hyundai	978
9	Hyundai	978	Anisa
SunnyAlv

Cell Formulas
Range	Formula
F2:G4	F2	=CONCAT(UNIQUE(FILTER(B$2:B$9,$A$2:$A$9=$E2)))

 

[Skybluekid]

An alternative is

Book1.xlsm
	A	B	C	D	E	F	G
1	Brand	ID	Name		Brand	ID	Name
2	Honda	123			Honda	123	Indah
3	Honda	123	Indah		Wuling	456	Agifi
4	Wuling	456	Agifi		Hyundai	978	Anisa
5	Wuling	456
6	Wuling
7	Hyundai
8	Hyundai	978
9	Hyundai	978	Anisa
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(A2:A9)
F2:F4	F2	=UNIQUE(FILTER($B$2:$B$9,(($A$2:$A$9=E2)*($B$2:$B$9<>""))))
G2:G4	G2	=UNIQUE(FILTER($C$2:$C$9,(($A$2:$A$9=E2)*($C$2:$C$9<>""))))
Dynamic array formulas.

 

[Peter_SSs]

Again, based on the sample data provided, the whole result might be obtained with this

23 05 29.xlsm
	A	B	C	D	E	F	G
1	Brand	ID	Name		Brand	ID	Name
2	Honda	123			Honda	123	Indah
3	Honda	123	Indah		Wuling	456	Agifi
4	Wuling	456	Agifi		Hyundai	978	Anisa
5	Wuling	456
6	Wuling
7	Hyundai
8	Hyundai	978
9	Hyundai	978	Anisa
SunnyAlv

Cell Formulas
Range	Formula
E1:G4	E1	=FILTER(A1:C9,(B1:B9<>"")*(C1:C9<>""))
Dynamic array formulas.