Help!

[1AG]

Hello and thank you ahead of time! Been working on finding a code for my spreadsheet for quite some time now, so I am very grateful for this message board! Please help!

I have two sheets in a workbook. Sheet 1 contains data to be retrieved into Sheet 2.
Sheet 2!A1 is the "ID Number" to look up in Sheet 1 Column A, and Match or Lookup? if Sheet 1 Column D is "Yes", then return "Purple", if Column E is "Yes" then return "Red",if Column F is "Yes" then return "Blue",if Column G is "Yes" then return "Black"

The code I am working with and return value in:
Sheet 2 Cell D4
VLOOKUP(A1,Sheet 1!A:A,IF(Sheet 1!D:D="Yes","Purple),IF(Sheet 1!E:E="Yes","Red),"IF(Sheet 1!F:F="Yes","Blue),IF(Sheet 1!G:G="Black")))

Return value in Sheet 2 Cell D4 ---if D and F and G are all "Yes" , result should be Purple, Blue, Black

Hope I was able to explain what I am trying to achieve

 

[FormR]

Hi, welcome to the forum!

Here is one option you can try.

Book2
	A	B
1	ID Number	Result
2	10	Red
3	5	Blue
4	6	Purple
5	12	Purple
6	15	#N/A
7	8	Blue
8	5	Blue
9	4	Black
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=INDEX({"Purple","Red","Blue","Black"},MATCH("Yes",INDEX('Sheet 2'!D:G,MATCH(A2,'Sheet 2'!A:A,0),0),0))

Book2
	A	B	C	D	E	F	G
1	ID Number
2	7				Yes
3	3					Yes
4	8					Yes
5	9			Yes
6	5					Yes
7	2				Yes
8	6			Yes
9	12			Yes
10	11						Yes
11	10				Yes
12	1			Yes
13	4						Yes
Sheet 2

 

[1AG]

Hi, welcome to the forum!

Here is one option you can try.

Book2
	A	B
1	ID Number	Result
2	10	Red
3	5	Blue
4	6	Purple
5	12	Purple
6	15	#N/A
7	8	Blue
8	5	Blue
9	4	Black
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=INDEX({"Purple","Red","Blue","Black"},MATCH("Yes",INDEX('Sheet 2'!D:G,MATCH(A2,'Sheet 2'!A:A,0),0),0))

Book2
	A	B	C	D	E	F	G
1	ID Number
2	7				Yes
3	3					Yes
4	8					Yes
5	9			Yes
6	5					Yes
7	2				Yes
8	6			Yes
9	12			Yes
10	11						Yes
11	10				Yes
12	1			Yes
13	4						Yes
Sheet 2

Hello and thank you, I appreciate the prompt reply, that formula worked! It is truly a blessing to be able to ask for help after so much time spent on trying to come up with a solution, thankful for the forum!

 

[1AG]

In reference to:

Return value in Sheet 2 Cell D4 ---if D and F and G are all "Yes" , result should be Purple, Blue, Black

If D, F, and G are all "Yes", the formula is only returning Purple rather than Purple, Blue, Black as the text value.

 

[FormR]

Return value in Sheet 2 Cell D4 ---if D and F and G are all "Yes" , result should be Purple, Blue, Black

Hi, yes - I missed that part. Assuming you have the textjoin() function available you can try this array formula instead.

Book8
	A	B
1	ID Number	Result
2	10	Red
3	5	Purple, Red, Blue, Black
4	6	Purple
5	12	Purple
6	15	#N/A
7	8	Blue
8	1	Purple, Red
9	4	Black
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=TEXTJOIN(", ",1,IF(INDEX('Sheet 2'!D:G,MATCH(A2,'Sheet 2'!A:A,0),0)="Yes",{"Purple","Red","Blue","Black"},""))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book8
	A	B	C	D	E	F	G
1	ID Number
2	7				Yes
3	3					Yes
4	8					Yes
5	9			Yes
6	5			Yes	Yes	Yes	Yes
7	2				Yes
8	6			Yes
9	12			Yes
10	11						Yes
11	10				Yes
12	1			Yes	Yes
13	4						Yes
Sheet 2