Index Match, 1 match and 1 contains certain text

[willow1985]

I need help with an Index Match formula. I am looking to return whatever is in column C as long as 2 criteria is met:
1. There is an exact match between E2 and Column A
2. Column B contains the word in F1 - (I would prefer a cell reference to F1 rather than typing in the text *Blue*)

Below is an example and an incorrect formula that is not returning the results I am looking for. The desired results are below in yellow

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Item	Index Helper			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird	Y		Tom	N
3	Greg	medium Green plant	Y		Greg	N
4	Will	small yellow butterfly	Y		Will	N
5	Mark	large red berry	Y		Mark	N
6
7					Desired Results:
8						Blue	Green	Yellow	Red
9					Tom	Y	N	N	N
10					Greg	N	Y	N	N
11					Will	N	N	Y	N
12					Mark	N	N	N	Y
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=IFERROR(INDEX($C$2:$C$5,MATCH(1,("*Blue*"=$B$2:$B$5)*(E2=$A$2:$A$5),FALSE)),"N")

Thank you to anyone who can assist me with this!

 

[JEC]

If the order of your name columns will be the same, you could just use this

Book1
	A	B	C	D	E	F	G	H
1	Name	Item			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird		Tom	Y	N	N	N
3	Greg	medium Green plant		Greg	N	Y	N	N
4	Will	small yellow butterfly		Will	N	N	Y	N
5	Mark	large red berry		Mark	N	N	N	Y
Sheet1

Cell Formulas
Range	Formula
E2:H5	E2	=IF(ISNUMBER(SEARCH(E1:H1,B2:B5)),"Y","N")
Dynamic array formulas.

 

[willow1985]

If the order of your name columns will be the same, you could just use this

Book1
	A	B	C	D	E	F	G	H
1	Name	Item			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird		Tom	Y	N	N	N
3	Greg	medium Green plant		Greg	N	Y	N	N
4	Will	small yellow butterfly		Will	N	N	Y	N
5	Mark	large red berry		Mark	N	N	N	Y
Sheet1

Cell Formulas
Range	Formula
E2:H5	E2	=IF(ISNUMBER(SEARCH(E1:H1,B2:B5)),"Y","N")
Dynamic array formulas.

This does not account for the Names...

If I move a name the data is now incorrect. I need to account for the names (Column E) and F1:I1 changing.
Note: On the original spreadsheet column E and F1:I1 data changes due to Unique formulas.

Index match containing certain text and exact match.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Item	Index Helper			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird	Y		Will	Y	N	N	N
3	Greg	medium Green plant	Y		Greg	N	Y	N	N
4	Will	small yellow butterfly	Y		Tom	N	N	Y	N
5	Mark	large red berry	Y		Mark	N	N	N	Y
Sheet1

Cell Formulas
Range	Formula
F2:I5	F2	=IF(ISNUMBER(SEARCH(F1:I1,B2:B5)),"Y","N")
Dynamic array formulas.

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Item	Index Helper			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird	Y		Tom	Y	N	N	N
3	Greg	medium Green plant	Y		Greg	N	Y	N	N
4	Will	small yellow butterfly	Y		Will	N	N	Y	N
5	Mark	large red berry	Y		Mark	N	N	N	Y
Sheet4

Cell Formulas
Range	Formula
F2:I5	F2	=IF(COUNTIFS($A$2:$A$5,$E2,$B$2:$B$5,"*"&F$1&"*"),"Y","N")

Or with a spill formula, which might be preferable if you want to use the F1# reference for a UNIQUE formula:

Book1
	E	F	G	H	I
1		Blue	Green	Yellow	Red
2	Tom	Y	N	N	N
3	Greg	N	Y	N	N
4	Will	N	N	Y	N
5	Mark	N	N	N	Y
Sheet4

Cell Formulas
Range	Formula
F2:I5	F2	=IF(COUNTIFS(A2:A5,E2:E5,B2:B5,"*"&F1:I1&"*"),"Y","N")
Dynamic array formulas.

 

[Asbestos_Jen]

Another option, if your Index Helpers are not going to be as simple as "Y" for all names:

2024-07-22.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Item	Index Helper			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird	a		Tom	a	N	N	N
3	Greg	medium Green plant	b		Greg	N	b	N	N
4	Will	small yellow butterfly	c		Will	N	N	c	N
5	Mark	large red berry	d		Mark	N	N	N	d
6					Sally	N	N	N	N
Sheet2

Cell Formulas
Range	Formula
F2:I6	F2	=IFERROR(IF(ISNUMBER(SEARCH(F$1,$B2)),INDEX($A$2:$C$5,MATCH($E2,$A$2:$A$5,0),3),"N"),"n")

 

[JEC]

Here a dynamic formula

Book1
	A	B	C	D	E	F	G	H
1	Name	Item			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird		Will	N	N	Y	N
3	Greg	medium Green plant		Greg	N	Y	N	N
4	Will	small yellow butterfly		Tom	Y	N	N	N
5	Mark	large red berry		Mark	N	N	N	Y
Sheet1

Cell Formulas
Range	Formula
E2:H5	E2	=IF(ISNUMBER(SEARCH(E1:H1,INDEX(B2:B5,XMATCH(D2:D5,A2:A5)))),"Y","N")
Dynamic array formulas.

 

[Asbestos_Jen]

Another option, if your Index Helpers are not going to be as simple as "Y" for all names:

2024-07-22.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Item	Index Helper			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird	a		Tom	a	N	N	N
3	Greg	medium Green plant	b		Greg	N	b	N	N
4	Will	small yellow butterfly	c		Will	N	N	c	N
5	Mark	large red berry	d		Mark	N	N	N	d
6					Sally	N	N	N	N
Sheet2

Cell Formulas
Range	Formula
F2:I6	F2	=IFERROR(IF(ISNUMBER(SEARCH(F$1,$B2)),INDEX($A$2:$C$5,MATCH($E2,$A$2:$A$5,0),3),"N"),"n")

You probably don't need the "iferror" part of my formula; I just had it there to distinguish errors from non-matches.

 

[willow1985]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Item	Index Helper			Blue	Green	Yellow	Red
2	Tom	Happy Blue bird	Y		Tom	Y	N	N	N
3	Greg	medium Green plant	Y		Greg	N	Y	N	N
4	Will	small yellow butterfly	Y		Will	N	N	Y	N
5	Mark	large red berry	Y		Mark	N	N	N	Y
Sheet4

Cell Formulas
Range	Formula
F2:I5	F2	=IF(COUNTIFS($A$2:$A$5,$E2,$B$2:$B$5,"*"&F$1&"*"),"Y","N")

Or with a spill formula, which might be preferable if you want to use the F1# reference for a UNIQUE formula:

Book1
	E	F	G	H	I
1		Blue	Green	Yellow	Red
2	Tom	Y	N	N	N
3	Greg	N	Y	N	N
4	Will	N	N	Y	N
5	Mark	N	N	N	Y
Sheet4

Cell Formulas
Range	Formula
F2:I5	F2	=IF(COUNTIFS(A2:A5,E2:E5,B2:B5,"*"&F1:I1&"*"),"Y","N")
Dynamic array formulas.

This is perfect!

Thank you and everyone so much for all of your help!

 

[JEC]

Since you have office 365, you can use the dynamic array formula's. There is just one formula for the whole output.
But, solved is solved ;p

 

[Eric W]

Glad we could help!