Check if column contains text from matrix

[mark5544]

Hi, I need help with this, I am stuck.

I have this formula:
=IF(AF2<>0;IF(AND(ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"P"&AF2);Indata!P2));ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"Q"&AF2);Indata!P2));ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"R"&AF2);Indata!P2));ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"S"&AF2);Indata!P2));ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"T"&AF2);Indata!P2));ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"U"&AF2);Indata!P2));ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"V"&AF2);Indata!P2));ISNUMBER(SEARCH(INDIRECT("'"&"Bestseller spec"&"'!"&"W"&AF2);Indata!P2)));"Yes";"No");"No")

I want to see if cell AF2 has value larger than 0, then I want to check if cell P2 contains text strings from another table in sheet Bestseller spec, seen below. Which row that should be used from the table is determined by number in AF2.
k LMNO P Q R S T U VW
[TABLE="width: 1327"]
<tbody>[TR]
[TD]model 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Equipment row 1[/TD]
[TD]Equipment row 1[/TD]
[TD]Equipment row 1[/TD]
[TD]Equipment row 1[/TD]
[TD]Equipment row 1[/TD]
[TD]Equipment row 1[/TD]
[TD]Equipment row 1[/TD]
[TD]Equipment row 1[/TD]
[/TR]
[TR]
[TD]model 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Equipment row 2[/TD]
[TD]Equipment row 2[/TD]
[TD]Equipment row 2[/TD]
[TD]Equipment row 2[/TD]
[TD]Equipment row 2[/TD]
[TD]Equipment row 2[/TD]
[TD]Equipment row 2[/TD]
[TD]Equipment row 2[/TD]
[/TR]
[TR]
[TD]model 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Equipment row 3[/TD]
[TD]Equipment row 3[/TD]
[TD]Equipment row 3[/TD]
[TD]Equipment row 3[/TD]
[TD]Equipment row 3[/TD]
[TD]Equipment row 3[/TD]
[TD]Equipment row 3[/TD]
[TD]Equipment row 3[/TD]
[/TR]
[TR]
[TD]model 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I cant get the formula to work, any ideas someone? All help is much appreciated!

 

[wideboydixon]

Looks complicated! Is the text in P2 an exact match for one of those cells or just a partial match? Exact match could be coded to a much smaller formula

Anyway, if you want to see if it's in *any* of those cells, I think you need OR() rather than AND() as the latter will mandate that it's in *all* those cells.

WBD

 

[mark5544]

Looks complicated! Is the text in P2 an exact match for one of those cells or just a partial match? Exact match could be coded to a much smaller formula

Anyway, if you want to see if it's in *any* of those cells, I think you need OR() rather than AND() as the latter will mandate that it's in *all* those cells.

WBD

It will not be an exact match. Example: Text in P2 can look something like "banana, apple, orange, lemon". Lets say AF2 value is 5. In row 5 in the matrix it will be "banana", "apple", and the rest of the line commas "," "," "," "," ","). So P2 contains word banana, apple, and commas, so it should be a match.

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]R[/TD]
[TD="width: 64"]Q[/TD]
[TD="class: xl63, width: 64"]S[/TD]
[TD="width: 64"]T[/TD]
[TD="width: 64"]U[/TD]
[TD="width: 64"]V[/TD]
[TD="width: 64"]W[/TD]
[/TR]
[TR]
[TD]model 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]model 5[/TD]
[TD]banana[/TD]
[TD]apple[/TD]
[TD],[/TD]
[TD],[/TD]
[TD],[/TD]
[TD],[/TD]
[TD],[/TD]
[/TR]
</tbody>[/TABLE]

Forgot to mention this detail, P2 will always contain commas (,) which is used for the cells in the matrix that are empty.

 

[wideboydixon]

OK. Still not 100% clear on the requirements. Here's what I have in the "Bestseller spec" sheet:

Book1
	O	P	Q	R	S	T	U	V	W
1	model 1	apple	,	,	,	,	banana	,	,
2	model 2	,	orange	,	,	,	,	kiwi	,
3	model 3	,	,	lemon	,	,	,	,	lime
4	model 4	,	,	,	pear	,	,	,	,
5	model 5	banana	apple	orange	lemon	,	,	,	,
Bestseller spec

Here's my "Indata" sheet:

Book1
	P	AF	AG
2	banana, apple, orange, lemon, , , ,	5	Yes
Indata

Cell Formulas
Range	Formula
AG2	=IF(AF2>0,IF(AND(ISNUMBER(SEARCH(INDEX('Bestseller spec'!P:P,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!Q:Q,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!R:R,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!S:S,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!T:T,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!U:U,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!V:V,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!W:W,AF2),Indata!P2))),"Yes","No"),"No")

I'm in the UK so I had to swap ";" for "," in the formula but it's giving a "Yes" for row 5. Is that right?

WBD

 

[mark5544]

OK. Still not 100% clear on the requirements. Here's what I have in the "Bestseller spec" sheet:

	O	P	Q	R	S	T	U	V	W
model 1	apple	,	,	,	,	banana	,	,
model 2	,	orange	,	,	,	,	kiwi	,
model 3	,	,	lemon	,	,	,	,	lime
model 4	,	,	,	pear	,	,	,	,
model 5	banana	apple	orange	lemon	,	,	,	,

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Bestseller spec​

Here's my "Indata" sheet:

	P	AF	AG
banana, apple, orange, lemon, , , ,	Yes

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]5[/TD]

</tbody>

Indata​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG2[/TH]
[TD="align: left"]=IF(AF2>0,IF(AND(ISNUMBER(SEARCH(INDEX('Bestseller spec'!P:P,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!Q:Q,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!R:R,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!S:S,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!T:T,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!U:U,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!V:V,AF2),Indata!P2)),ISNUMBER(SEARCH(INDEX('Bestseller spec'!W:W,AF2),Indata!P2))),"Yes","No"),"No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



I'm in the UK so I had to swap ";" for "," in the formula but it's giving a "Yes" for row 5. Is that right?

WBD

Yes that did it thank you very much!

 

[wideboydixon]

Cool. I removed all the INDIRECT() functions as it's quite volatile with those in.

WBD