Lookup & Concatenate subheading with details

[MurdochQuill]

Hi all,

I have a large sheet already with many orders, and many variations on planet types inside as a table to look up. If I were to add the planet type next to the original data, it would have to match the "ORDER CODE". Here is an example of the lookup table on Sheet 2 to get the resulting Order/Type

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ORDER CODE 1	ORDER 1	ORDER CODE 2	ORDER 2	ORDER CODE 3	ORDER 3	ORDER CODE 4	ORDER 4	ORDER CODE 5	ORDER 5	ORDER CODE 6	ORDER 6
2
3	XXXT	Terrestrial 1	EEEG	Desert 2	OOO4	Rock 45	JHH7	Gas 354	LKKK56	Chthonian 54	NBV2	ICE546
4	XXXE	Terrestrial 32	EEEQ	Desert 3	OOO5	Rock 46	JHH8	Gas 355	LKKK57	Chthonian 55	NBV3	ICE547
5	XXXF	Terrestrial 33	EEEH	Desert 4	OOO6	Rock 47	JHH9	Gas 356	LKKK58	Chthonian 56	NBV4	ICE548
6	XXXF	Terrestrial 34	EEE2	Desert 5	OOO7	Rock 48	JHH10	Gas 357	LKKK59	Chthonian 57	NBV5	ICE549
7			EEE6	Desert 6	OOO8	Rock 49	JHH11	Gas 358	LKKK60	Chthonian 58	NBV6	ICE550
8			EEE8	Desert 7	OOO9	Rock 50	JHH12	Gas 359
9			EEE1	Desert 8	OOO10	Rock 51	JHH13	Gas 360
10			EEE7	Desert 9	OOO11	Rock 52
11					OOO12	Rock 53
12					OOO13	Rock 54
13
14
15
Sheet2

Col C can be added to the original data as an identifier, as my raw data output includes this code on each row which is handy.

Col E (in blue) is where I would like to do a lookup on the code table and Xlookup/Vlookup/match the ORDER name to the ORDER CODE (whichever is fastest I guess). This is the part I'm unsure how to work the formula on, and any help with the formula to get the type name would be excellent.

Book1
	B	C	D	E	F	G	H	I	J
1	INPUT DATA
2	ID	ORDER DATA	Data	Type		ORDER 1	ORDER 2	ORDER 3	ORDER 4
3	Earth	467XXXF	3425	ORDER 1) Terrestrial 33		YES	NO	NO	NO
4	Mars	547EEEG	456	ORDER 2) Desert 2		NO	YES	NO	NO
5	Jupiter	675JHH7	325	ORDER 4) Gas 354		NO	NO	NO	YES
6	Jupiter	567JHH9	245326	ORDER 4) Gas 356		NO	NO	NO	YES
7	Mars	678EEEH	567	ORDER 2) Desert 4		NO	YES	NO	NO
8	Mercury	OOO4436	3415	ORDER 3) Rock 45		NO	NO	YES	NO
9	Pluto	OOO536	456	ORDER 3) Rock 46		NO	NO	YES	NO
10	Pluto	OOO6546	4652	ORDER 3) Rock 47		NO	NO	YES	NO
11	Earth	XXXE3456	345	ORDER 1) Terrestrial 32		YES	NO	NO	NO
12	Mars	EEE73456	43	ORDER 2) Desert 9		NO	YES	NO	NO
Sheet1

Cell Formulas
Range	Formula
G3:G12	G3	=IF(SUM((Sheet2!$A$3:$A$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$A$3:$A$15&"*",$C3,0)))), "YES", "NO")
H3:H12	H3	=IF(SUM((Sheet2!$C$3:$C$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$C$3:$C$15&"*",$C3,0)))), "YES", "NO")
I3:I12	I3	=IF(SUM((Sheet2!$E$3:$E$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$E$3:$E$15&"*",$C3,0)))), "YES", "NO")
J3:J12	J3	=IF(SUM((Sheet2!$G$3:$G$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$G$3:$G$15&"*",$C3,0)))), "YES", "NO")

Any help would be great!

 

[jasonb75]

Noting that there are 2 correct results for the first row (Terrestrial33 and Terrestrial34) how do you determine that Terrestrial33 is the correct result, should it always be the first one or is there an error in the example?

 

[MurdochQuill]

Noting that there are 2 correct results for the first row (Terrestrial33 and Terrestrial34) how do you determine that Terrestrial33 is the correct result, should it always be the first one or is there an error in the example?

Order code on Sheet 2 is unique and has the name to the right.

So we will match the ORDER DATA col on sheet 1 with it, but there are many Ranges on sheet 2.

 

[jasonb75]

Order code on Sheet 2 is unique and has the name to the right.

XXXF was duplicated in the example which was why I checked first. This works with the example but it is a bit clunky, someone else may be able to shorten it a bit. If you don't have the LET function yet then it will make it longer as some sections will need to be duplicated.

MurdochQuill.xlsx
	A	B	C	D	E	F	G	H	I	J
1		INPUT DATA
2		ID	ORDER DATA	Data	Type		ORDER 1	ORDER 2	ORDER 3	ORDER 4
3		Earth	467XXXF	3425	ORDER 1) Terrestrial 33		YES	NO	NO	NO
4		Mars	547EEEG	456	ORDER 2) Desert 2		NO	YES	NO	NO
5		Jupiter	675JHH7	325	ORDER 4) Gas 354		NO	NO	NO	YES
6		Jupiter	567JHH9	245326	ORDER 4) Gas 356		NO	NO	NO	YES
7		Mars	678EEEH	567	ORDER 2) Desert 4		NO	YES	NO	NO
8		Mercury	OOO4436	3415	ORDER 3) Rock 45		NO	NO	YES	NO
9		Pluto	OOO536	456	ORDER 3) Rock 46		NO	NO	YES	NO
10		Pluto	OOO6546	4652	ORDER 3) Rock 47		NO	NO	YES	NO
11		Earth	XXXE3456	345	ORDER 1) Terrestrial 32		YES	NO	NO	NO
12		Mars	EEE73456	43	ORDER 2) Desert 9		NO	YES	NO	NO
Sheet1

Cell Formulas
Range	Formula
G3:G12	G3	=IF(SUM((Sheet2!$A$3:$A$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$A$3:$A$15&"*",$C3,0)))), "YES", "NO")
H3:H12	H3	=IF(SUM((Sheet2!$C$3:$C$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$C$3:$C$15&"*",$C3,0)))), "YES", "NO")
I3:I12	I3	=IF(SUM((Sheet2!$E$3:$E$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$E$3:$E$15&"*",$C3,0)))), "YES", "NO")
J3:J12	J3	=IF(SUM((Sheet2!$G$3:$G$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$G$3:$G$15&"*",$C3,0)))), "YES", "NO")
E3:E12	E3	=LET(ord,CONCAT(IF(G3:J3="Yes",$G$2:$J$2,"")),col,MATCH(SUBSTITUTE(ord," "," CODE "),Sheet2!$A$1:$L$1,0),data,INDEX(Sheet2!$A$3:$L$12,0,col)&"",ord&") "&LOOKUP(2,1/ISNUMBER(SEARCH(data,C3))/(data<>""),INDEX(Sheet2!$A$3:$L$12,0,col+1)))

 

[MurdochQuill]

XXXF was duplicated in the example which was why I checked first. This works with the example but it is a bit clunky, someone else may be able to shorten it a bit. If you don't have the LET function yet then it will make it longer as some sections will need to be duplicated.

MurdochQuill.xlsx
	A	B	C	D	E	F	G	H	I	J
1		INPUT DATA
2		ID	ORDER DATA	Data	Type		ORDER 1	ORDER 2	ORDER 3	ORDER 4
3		Earth	467XXXF	3425	ORDER 1) Terrestrial 33		YES	NO	NO	NO
4		Mars	547EEEG	456	ORDER 2) Desert 2		NO	YES	NO	NO
5		Jupiter	675JHH7	325	ORDER 4) Gas 354		NO	NO	NO	YES
6		Jupiter	567JHH9	245326	ORDER 4) Gas 356		NO	NO	NO	YES
7		Mars	678EEEH	567	ORDER 2) Desert 4		NO	YES	NO	NO
8		Mercury	OOO4436	3415	ORDER 3) Rock 45		NO	NO	YES	NO
9		Pluto	OOO536	456	ORDER 3) Rock 46		NO	NO	YES	NO
10		Pluto	OOO6546	4652	ORDER 3) Rock 47		NO	NO	YES	NO
11		Earth	XXXE3456	345	ORDER 1) Terrestrial 32		YES	NO	NO	NO
12		Mars	EEE73456	43	ORDER 2) Desert 9		NO	YES	NO	NO
Sheet1

Cell Formulas
Range	Formula
G3:G12	G3	=IF(SUM((Sheet2!$A$3:$A$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$A$3:$A$15&"*",$C3,0)))), "YES", "NO")
H3:H12	H3	=IF(SUM((Sheet2!$C$3:$C$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$C$3:$C$15&"*",$C3,0)))), "YES", "NO")
I3:I12	I3	=IF(SUM((Sheet2!$E$3:$E$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$E$3:$E$15&"*",$C3,0)))), "YES", "NO")
J3:J12	J3	=IF(SUM((Sheet2!$G$3:$G$15<>"")*(ISNUMBER(MATCH("*"&Sheet2!$G$3:$G$15&"*",$C3,0)))), "YES", "NO")
E3:E12	E3	=LET(ord,CONCAT(IF(G3:J3="Yes",$G$2:$J$2,"")),col,MATCH(SUBSTITUTE(ord," "," CODE "),Sheet2!$A$1:$L$1,0),data,INDEX(Sheet2!$A$3:$L$12,0,col)&"",ord&") "&LOOKUP(2,1/ISNUMBER(SEARCH(data,C3))/(data<>""),INDEX(Sheet2!$A$3:$L$12,0,col+1)))

Ah my bad, sorry! But thanks for the help