Search in multi condition

[TH123]

Hi,

I have data base as below

Book1
	A	B	C
1	OrderNumber	ColorDescription	Approval date
2	4701740244	Multi	1-Jan
3	4701740244,4701740244	Yellow,Blue	3-Jan
Sheet1

And would like to get result following last column as below. Could you pls support if any Excel formula works in this case?

Book1
	F	G	H
1	OrderNumber	ColorDescription	Approval date - result s/b
2	4701740244	Blue	3-Jan
3	4701740244	Yellow	3-Jan
4	4701740244	Multi	1-Jan
Sheet1

 

[skillilea]

Powerquery will do this for you.


Quick Steps to create

1. Highlight your data
2. Go to Data/From Table/Range
3. Highlight your OrderNumber, split column by comma,
   1. Advanced option: Split Into Rows
4. Do the same for the Color Description
5. Close and load to a table and put it where ever you want

Here is the mCode

// Table4
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"OrderNumber", type text}}, "en-US"), {{"OrderNumber", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "OrderNumber"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"ColorDesc", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ColorDesc")
in
    #"Split Column by Delimiter1"

 

[TH123]

Powerquery will do this for you.


Quick Steps to create

1. Highlight your data
2. Go to Data/From Table/Range
3. Highlight your OrderNumber, split column by comma,
   1. Advanced option: Split Into Rows
4. Do the same for the Color Description
5. Close and load to a table and put it where ever you want

Here is the mCode

// Table4
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"OrderNumber", type text}}, "en-US"), {{"OrderNumber", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "OrderNumber"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"ColorDesc", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ColorDesc")
in
    #"Split Column by Delimiter1"

Thank you. Seems my question was not clear enough. I'd like to look up/ search from data based to exist chart and result s/b same as column H. Could you pls advise if any excel formula can help in this case?