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!
[TABLE="width: 665"]
<tbody>[TR]
[TD]DESCRIPTION[/TD]
[TD]PRODUCT NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PRODUCT NO[/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE TALL 10#[/TD]
[TD="align: right"]101111[/TD]
[TD][/TD]
[TD]10#[/TD]
[TD]TALL[/TD]
[TD]BOWL[/TD]
[TD]BLUE[/TD]
[TD] (formula)[/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE TALL 20#[/TD]
[TD="align: right"]101112[/TD]
[TD][/TD]
[TD]10#[/TD]
[TD]SHORT[/TD]
[TD]BOTTLE[/TD]
[TD]GREEN[/TD]
[TD] (formula)[/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE SHORT 10#[/TD]
[TD="align: right"]101113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE SHORT 20#[/TD]
[TD="align: right"]101114[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE TALL 10#[/TD]
[TD="align: right"]101115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE TALL 20#[/TD]
[TD="align: right"]101116[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE SHORT 10#[/TD]
[TD="align: right"]101117[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE SHORT 20#[/TD]
[TD="align: right"]101118[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE BOWL TALL 10#[/TD]
[TD="align: right"]101119[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE BOWL TALL 20#[/TD]
[TD="align: right"]101120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE BOWL SHORT 10#[/TD]
[TD="align: right"]101121[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE BOWL SHORT 20#[/TD]
[TD="align: right"]101122[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOWL TALL 10#[/TD]
[TD="align: right"]101123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOWL TALL 20#[/TD]
[TD="align: right"]101124[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOWL SHORT 10#[/TD]
[TD="align: right"]101125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREEN BOWL SHORT 20#[/TD]
[TD="align: right"]101126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="5"><col></colgroup>[/TABLE]

 

[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!
[TABLE="width: 665"]
<tbody>[TR]
[TD]DESCRIPTION[/TD]
[TD]PRODUCT NO[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]PRODUCT NO[/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE TALL 10#[/TD]
[TD="align: right"]101111[/TD]
[TD]
[/TD]
[TD]10#[/TD]
[TD]TALL[/TD]
[TD]BOWL[/TD]
[TD]BLUE[/TD]
[TD] (formula)[/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE TALL 20#[/TD]
[TD="align: right"]101112[/TD]
[TD]
[/TD]
[TD]10#[/TD]
[TD]SHORT[/TD]
[TD]BOTTLE[/TD]
[TD]GREEN[/TD]
[TD] (formula)[/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE SHORT 10#[/TD]
[TD="align: right"]101113[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BLUE BOTTLE SHORT 20#[/TD]
[TD="align: right"]101114[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE TALL 10#[/TD]
[TD="align: right"]101115[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE TALL 20#[/TD]
[TD="align: right"]101116[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE SHORT 10#[/TD]
[TD="align: right"]101117[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOTTLE SHORT 20#[/TD]
[TD="align: right"]101118[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BLUE BOWL TALL 10#[/TD]
[TD="align: right"]101119[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BLUE BOWL TALL 20#[/TD]
[TD="align: right"]101120[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BLUE BOWL SHORT 10#[/TD]
[TD="align: right"]101121[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BLUE BOWL SHORT 20#[/TD]
[TD="align: right"]101122[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOWL TALL 10#[/TD]
[TD="align: right"]101123[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOWL TALL 20#[/TD]
[TD="align: right"]101124[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOWL SHORT 10#[/TD]
[TD="align: right"]101125[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]GREEN BOWL SHORT 20#[/TD]
[TD="align: right"]101126[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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