Search column cells for multiple criteria

[asippel1]

I am trying to search for multiple text fields in column A and once they are all found in one cell, return the value of the next cell in column B.

Example:
I want to search for 10#, TALL, BOWL, and BLUE in column A and once all of them are found in one cell, return the Product No that is in Column B. My real data has other information within the cells in column A (Description) which is why I need the formula to be done this way instead.

Any help would be appreciated. I've tried looking everywhere and can't seem to find the answer. Thanks in advanced!

DESCRIPTION	PRODUCT NO						PRODUCT NO
BLUE BOTTLE TALL 10#	101111		10#	TALL	BOWL	BLUE	(formula)
BLUE BOTTLE TALL 20#	101112		10#	SHORT	BOTTLE	GREEN	(formula)
BLUE BOTTLE SHORT 10#	101113
BLUE BOTTLE SHORT 20#	101114
GREEN BOTTLE TALL 10#	101115
GREEN BOTTLE TALL 20#	101116
GREEN BOTTLE SHORT 10#	101117
GREEN BOTTLE SHORT 20#	101118
BLUE BOWL TALL 10#	101119
BLUE BOWL TALL 20#	101120
BLUE BOWL SHORT 10#	101121
BLUE BOWL SHORT 20#	101122
GREEN BOWL TALL 10#	101123
GREEN BOWL TALL 20#	101124
GREEN BOWL SHORT 10#	101125
GREEN BOWL SHORT 20#	101126

<tbody>
</tbody><colgroup><col><col><col span="5"><col></colgroup>

 

[frank_AL]

Here are a couple of options:

If you want to specify the text as you described you can use: =IFERROR(IF(AND(SEARCH("10#",A11)>0,SEARCH("TALL",A11)>0,SEARCH("BOWL",A11)>0,SEARCH("BLUE",A11)>0),B11,""),"")

If you want to look for the values listed in cells D3 through G3 you can use: =IFERROR(IF(AND(SEARCH(D3,A3)>0,SEARCH(E3,A3)>0,SEARCH(F3,A3)>0,SEARCH(G3,A3)>0),B3,""),"")

 

[markmzz]

I am trying to search for multiple text fields in column A and once they are all found in one cell, return the value of the next cell in column B.

Example:
I want to search for 10#, TALL, BOWL, and BLUE in column A and once all of them are found in one cell, return the Product No that is in Column B. My real data has other information within the cells in column A (Description) which is why I need the formula to be done this way instead.

Any help would be appreciated. I've tried looking everywhere and can't seem to find the answer. Thanks in advanced!

DESCRIPTION	PRODUCT NO						PRODUCT NO
BLUE BOTTLE TALL 10#	101111		10#	TALL	BOWL	BLUE	(formula)
BLUE BOTTLE TALL 20#	101112		10#	SHORT	BOTTLE	GREEN	(formula)
BLUE BOTTLE SHORT 10#	101113
BLUE BOTTLE SHORT 20#	101114
GREEN BOTTLE TALL 10#	101115
GREEN BOTTLE TALL 20#	101116
GREEN BOTTLE SHORT 10#	101117
GREEN BOTTLE SHORT 20#	101118
BLUE BOWL TALL 10#	101119
BLUE BOWL TALL 20#	101120
BLUE BOWL SHORT 10#	101121
BLUE BOWL SHORT 20#	101122
GREEN BOWL TALL 10#	101123
GREEN BOWL TALL 20#	101124
GREEN BOWL SHORT 10#	101125
GREEN BOWL SHORT 20#	101126

<tbody>
</tbody>

Maybe this:

In H2 and copy down:

=LOOKUP(1,1/(MMULT(--ISNUMBER(SEARCH(" "&$D2:$G2&" "," "&$A$2:$A$17&" ")),{1;1;1;1})=4),$B$2:$B$17)

Markmzz