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

model 1

Equipment row 1

Equipment row 1

Equipment row 1

Equipment row 1

Equipment row 1

Equipment row 1

Equipment row 1

Equipment row 1

model 2

Equipment row 2

Equipment row 2

Equipment row 2

Equipment row 2

Equipment row 2

Equipment row 2

Equipment row 2

Equipment row 2

model 3

Equipment row 3

Equipment row 3

Equipment row 3

Equipment row 3

Equipment row 3

Equipment row 3

Equipment row 3

Equipment row 3

model 4

model 5

etc

model 6

etc

model 7

model 8

model 9

model 10

model 11

model 12

model 13

model 14

model 15

model 16

model 17

model 18

model 19

model 20

model 21

<tbody>
</tbody>

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.

	R	Q	S	T	U	V	W
model 1
model 2
model 3
model 4
model 5	banana	apple	,	,	,	,	,

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

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
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	,	,	,	,

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Bestseller spec​

Here's my "Indata" sheet:

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Indata​

Worksheet Formulas Cell 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") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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