Multiple Criteria Search Array Formula - Not Working!

[jonsharman]

Hi,

I have an array formula that I am trying to use to find specific order numbers based on a free-text search across a three-column table. The formula I am using is:

=IFERROR(INDEX($A$2:$D$14, SMALL(IF(ISERROR(SEARCH($F$4,$B$2:$D$14)*SEARCH($F$3,$B$2:$D$14)*SEARCH($F$2,$B$2:$D$14)), "", ROW(A2:A14)-MIN(ROW(A2:A14))+1), ROW(A1)), COLUMN(A1)),"")

And the data is laid out like so:

ORDER	FIRST	SECOND	THIRD	SEARCH	RESULTS
3604955	Media Pack XL	Chili Pack		Criteria 1	chili	3604955
3685809	John Cooper Works Chili Pack			Criteria 2		3685809
3686844	John Cooper Works Chili Pack	Media Pack XL		Criteria 3		3686844
3725450	Chili Pack					3725450
3727043	John Cooper Works Chili Pack	Media Pack XL				3727043
3755662	John Cooper Works Chili Pack	Media Pack XL	MINI Tech Pack			3755662
3725404	Chili Pack					3725404
3738095	Chili Pack					3738095
3761426	MINI Sport Pack	MINI JCW Sport Pack				3762543
3762543	Chili Pack					3785108
3767979	Pepper Pack
3773225	Pepper Pack
3785108	Chili Pack

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

This fills down to give the results. With one criteria (chili, john, pepper etc.) it is showing the correct order numbers. The issue when I start free-typing into the second criteria box there are no results returned e.g if I type in 'XL' into cell F3 the results column goes blank when in fact I want it to show all order numbers that have the instances of 'chili' and 'xl' within them. I've have tried an AND statement but that doesn't seem to help because the cells may be blank that I am searching ... ?

Can anyone see where I am going wrong?

Thanks

 

[63falcondude]

Try changing ISERROR to ISNUMBER.

Edit: Never mind, that does not produce the result that I was expecting. Working on it.

 

[James006]

Hi,

Your Array Formula is using the sign * (multiply) which within an array formula means AND ...

So for example ... just add XL to one Chili pack ... and you will get a result ...

In my opinion, it is only a confusion with what you are looking for ... which is OR ...

Within array formulas ... to mean OR, you need to use the + sign ...

Hope this explanation clarifies the matter ...

 

[jonsharman]

I tried with the '+' originally but that just gives the same output ... ??? As soon as I enter a second criteria everything disappears as if it giving a false result but I can't spot it.

What effect does the ISNUMBER have?

 

[James006]

You can test

=IFERROR(INDEX($A$2:$D$14, SMALL(IF(ISERROR(SEARCH($F$2,$B$2:$D$14)), "", ROW(A2:A14)-MIN(ROW(A2:A14))+1), ROW(A1)), COLUMN(A1)),0)+IFERROR(INDEX($A$2:$D$14, SMALL(IF(SEARCH($F$3,$B$2:$D$14)), "", ROW(A2:A14)-MIN(ROW(A2:A14))+1), ROW(A1)), COLUMN(A1)),0)+IFERROR(INDEX($A$2:$D$14, SMALL(IF(ISERROR(SEARCH($F$4,$B$2:$D$14)), "", ROW(A2:A14)-MIN(ROW(A2:A14))+1), ROW(A1)), COLUMN(A1)),0)

 

[63falcondude]

Here is one option using a helper column:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	ORDER	FIRST	SECOND	THIRD	Helper		SEARCH		RESULTS
2	3604955	Media Pack XL	Chili Pack		TRUE		Criteria 1	chili	3604955
3	3685809	John Cooper Works Chili Pack			FALSE		Criteria 2	XL	3686844
4	3686844	John Cooper Works Chili Pack	Media Pack XL		TRUE		Criteria 3		3727043
5	3725450	Chili Pack			FALSE				3755662
6	3727043	John Cooper Works Chili Pack	Media Pack XL		TRUE
7	3755662	John Cooper Works Chili Pack	Media Pack XL	MINI Tech Pack	TRUE
8	3725404	Chili Pack			FALSE
9	3738095	Chili Pack			FALSE
10	3761426	MINI Sport Pack	MINI JCW Sport Pack		FALSE
11	3762543	Chili Pack			FALSE
12	3767979	Pepper Pack			FALSE
13	3773225	Pepper Pack			FALSE
14	3785108	Chili Pack			FALSE
Sheet2

Cell Formulas
Range	Formula
E2	=AND(COUNTIF(B2:D2,"*"&$H$2&"*"),COUNTIF(B2:D2,"*"&$H$3&"*"),COUNTIF(B2:D2,"*"&$H$4&"*"))
I2	{=IFERROR(INDEX($A$2:$A$14,SMALL(IF($E$2:$E$14=TRUE,ROW($A$2:$A$14)-1),ROWS(A$1:A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jonsharman]

63falcondude,

Excellent - works a charm! Would there be any disadvantage to using a Helper column over trying to get the formula into one movement? I guess with the Helper column it is easier to add more search criteria?

 

[63falcondude]

63falcondude,

Excellent - works a charm! Would there be any disadvantage to using a Helper column over trying to get the formula into one movement? I guess with the Helper column it is easier to add more search criteria?

The only disadvantage of using a helper column in this case is that you have an extra column in your worksheet. You can always hide the column if you want to.

The use of the helper column simplifies things greatly and makes it easier to add more search criteria (you would only have to change the helper column formula).