Lookup Data from Group

[muhammad susanto]

hi all..
i have table below, how to lookup with this condition (lookup in group)

Book1
	F	G	H	I
3	Unit code	GROUP	CLUE
4	KP01	HUMAN	GROUP1
5	KP02	FORESTRY	GROUP1
6	KW01	ANIMAL	GROUP2
7	KW01.1	CAT	GROUP3
8	KW01.2	DOG	GROUP3
9	KW02	ELECTRONICS	GROUP2
10	KW02.1	GADGET	GROUP3
11	KW02.2	MOBILE PHONE	GROUP3
12	KW02.3	NOTEBOOK	GROUP3
13
14	expcted result (my target)
15		GROUP1	GROUP2	GROUP3
16	KP02	FORESTRY
17	KW02.2		ELECTRONICS	MOBILE PHONE
18	KW01.2		ANIMAL	DOG
19	etc..
Sheet1

my target in cell G16 to I18

any help, thank in advance.

.sst

 

[muhammad susanto]

I'm using Excel 2010 & 2016.

 

[sandy666]

XL2010 require Power Query add-in
XL2016 (and all above) has Power Query built-in
so
no pivot and no vba

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    PVT = Table.Pivot(Source, List.Distinct(Source[CLUE]), "CLUE", "GROUP"),
    FillD = Table.FillDown(PVT,{"GROUP2"}),
    Filter = Table.SelectRows(FillD, each ([Unit code] <> "KP01" and [Unit code] <> "KW01" and [Unit code] <> "KW02"))
in
    Filter

 

[muhammad susanto]

Hi Sandy, thank but im not familiar use Power Query,.. It can done use usually formula?

 

[sandy666]

probably it's possible but you'll need to wait for someone else who give you formula solution

im not familiar use Power Query

maybe time to learn?

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in

 

[muhammad susanto]

hi sandy, thank you..i hope someone give me alternative solution

 

[sandy666]

I hope so
Have a nice day