Advanced vlookup?

[Spaztic]

Hello, new to the forum and needing some help!

I have attached an image to help explain what I'm attempting to do.

• For the values shown in columns A through E, I want to look up what colors are assigned to each of them (columns J through O) and return the results in column G
• For any values shown in columns A through E that do not show up in the table (columns J through O), column G should be left blank
• NOTE: There may be more than 1 color for each value so column G may show more than 1 (e.g. Red, Yellow)

I really appreciate any help you can provide me!

 

[GlennUK]

Put this array formula (entered using Ctrl+Shift+Enter instead of Enter ... which puts curly brackets around the formula if successfully entered), in cell G2, and copy down:

=IF(ISNA(MATCH(TEXTJOIN("",TRUE,A2:F2),$J$2:$J$5,0)),"",TEXTJOIN(",",TRUE,IF(OFFSET($K$1,MATCH(TEXTJOIN("",TRUE,A2:F2),$J$2:$J$5,0),0,1,5)="x",$K$1:$O$1,"")))

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming that columns A:E & J are text values (eg B4 and J2 are both "1.2" not the number 1.2) then you could try this

23 08 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1							Color(s)				Red	Yellow	Blue	Green	Orange
2	1									1.2	x
3		1.1								1.2.1		x		x
4		1.2					Red			1.2.2.1					x
5			1.2.1				Yellow, Green			2.2	x
6			1.2.2							2.2.1			x	x
7				1.2.2.1			Orange			2.2.2.1		x			x
8	2									2.2.2.2	x
9		2.1								2.2.2.2.1				x
10		2.2
11			2.2.1				Blue, Green
12			2.2.2
13				2.2.2.1			Yellow, Orange
14				2.2.2.2			Red
15					2.2.2.2.1		Green
Which colours

Cell Formulas
Range	Formula
G2:G15	G2	=IFNA(TEXTJOIN(", ",1,FILTER(K$1:O$1,INDEX(K$2:O$9,MATCH(CONCAT(A2:E2),J$2:J$9,0),0)="x","")),"")

An alternative is to put this formula in G2 and it will populate all the rows from that single formula without needing to copy down the column.

23 08 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1							Color(s)				Red	Yellow	Blue	Green	Orange
2	1									1.2	x
3		1.1								1.2.1		x		x
4		1.2					Red			1.2.2.1					x
5			1.2.1				Yellow, Green			2.2	x
6			1.2.2							2.2.1			x	x
7				1.2.2.1			Orange			2.2.2.1		x			x
8	2									2.2.2.2	x
9		2.1								2.2.2.2.1				x
10		2.2
11			2.2.1				Blue, Green
12			2.2.2
13				2.2.2.1			Yellow, Orange
14				2.2.2.2			Red
15					2.2.2.2.1		Green
Which colours (2)

Cell Formulas
Range	Formula
G2:G15	G2	=BYROW(A2:E15,LAMBDA(rw,IFNA(TEXTJOIN(", ",1,FILTER(K$1:O$1,INDEX(K$2:O$9,MATCH(CONCAT(rw),J$2:J$9,0),0)="x","")),"")))
Dynamic array formulas.

@GlennUK
Some comments about your suggestion
- It is simpler to use CONCAT instead of TEXTJOIN with "" as the delimiter
- Your profile does not say which version you are using, but note that the OP has 365 and in that version Ctrl+Shift+Enter is not required.