"If cell contains a String of text" in an Array formula

[Cknight]

Hi all,

I have been recently learning more and more about arrays to implement into my spreadsheets and now have them functioning!

What I cannot seem to figure out is how to set the criteria for an array to function if it is searching for a certain portion of a cell, not the entire cell. Below is one of my formulas. I'm assuming I need to alter something in the "IF" portion of the formula but I could be way off hence why I am turning to the experts

=IFERROR(INDEX(SPLITS!A$2:A$1156,SMALL(IF((SPLITS!$A$2:$A$1156='KSE TO KSW'!$A$2)*(SPLITS!$H$2:$H$1156>'KSE TO KSW'!$B$2),ROW(SPLITS!A$2:A$1156)-ROW(SPLITS!A$2)+1),ROWS(SPLITS!A$2:SPLITS!A2))),"")

So basically, I am hoping to input a value into sheet 'KSE TO KSW" cell B2 and it will pull any row that contains the value of B2 from Sheet "Splits" A2:A1156. Right now, I can only get it to function if I do equal to, less than, or greater than.

Thanks in advance for your help!

 

[Luke M]

You can search cells contents using SEARCH/FIND (the latter is case-sensitive) and ISNUMBER.
Formula would become:
=IFERROR(INDEX(SPLITS!A$2:A$1156,SMALL(IF((ISNUMBER(SEARCH('KSE TO KSW'!$A$1,SPLITS!$A$2:$A$1156)))*(SPLITS!$H$2:$H$1156>'KSE TO KSW'!$B$2),ROW(SPLITS!A$2:A$1156)-ROW(SPLITS!A$2)+1),ROWS(SPLITS!A$2:SPLITS!A2))),"")

 

[Cknight]

That works perfectly! Thank you. I had the two ranges flip flopped for the SEARCH function and assumed I was way off.

Thanks again

 

[FiLoX]

Hi,

I know that this is quite an old conversation but I am struck on this same problem.

I have a DB from which I am taking information on a second sheet. It is assumed to gather all the cells from row A depending on whether the value in B2 is present in row E. The sample file is here. The formula is as follows (translated from Italian, I am unsure about the notation):

=IFERROR(INDEX(DB!$A$2:$A$51,SMALL(IF(ISNUMBER(FIND(DB!$E$2:$E$51,$B$2)),ROWS($1:$50)),ROWS(A1))),"")

The formula does not appear to work as it only takes exact columns with the value, not all the ones containing them. For example, if I insert "Drama", it only takes those with "Drama" and not other genres.

However, if I insert "Crime, Drama" I also receive the cells with Drama inside. What I don't understand is that if I use a helper column (saying if the condition is true, then put okay) and use that one as a reference, then the system works.

Thank you in advance for your suggestions on this.

Marco - huge fan from Italy

 

[bosco_yip]

Hi,

I know that this is quite an old conversation but I am struck on this same problem.

I have a DB from which I am taking information on a second sheet. It is assumed to gather all the cells from row A depending on whether the value in B2 is present in row E. The sample file is here. The formula is as follows (translated from Italian, I am unsure about the notation):
=IFERROR(INDEX(DB!$A$2:$A$51,SMALL(IF(ISNUMBER(FIND(DB!$E$2:$E$51,$B$2)),ROWS($1:$50)),ROWS(A1))),"")

The formula does not appear to work as it only takes exact columns with the value, not all the ones containing them. For example, if I insert "Drama", it only takes those with "Drama" and not other genres.

However, if I insert "Crime, Drama" I also receive the cells with Drama inside. What I don't understand is that if I use a helper column (saying if the condition is true, then put okay) and use that one as a reference, then the system works.

Thank you in advance for your suggestions on this.

Marco - huge fan from Italy

Try this,

In C5, Ctrl+Shift+Enter copied down :

=IFERROR(INDEX(DB!$A$2:$A$51,SMALL(IF(ISNUMBER(FIND($B$2,DB!$E$2:$E$51)),ROW($1:$50)),ROW(A1))),"")

Regards
Bosco

 

[FiLoX]

Hi Bosco,

thank you very much for your quick and precise reply. Indeed, I mistakenly exchanged the positions of the two parts of the FIND function, now it works perfectly.

Marco

 

[Peter Bartholomew]

Since I looked at the file this morning …

What might not suit you is that I have inserted tables and used structured references. That is simply because the direct cell reference are marginally more of a problem to my understanding than is the Italian.

I was not sure how the two genres separated by a comma was intended to work so I put in two criteria joined with an OR condition.

 

[FiLoX]

Hi Peter! Thank you for taking the time to review the file and to suggest an alternative course of action.

I must admit I am not exactly confident with tables but I will study your solution, as I am sure it could come in handy in the future.

The end goal for me was to create an internal search for a database with various conditions in a single array, if I learn how you did it I can suggest another way to achieve the same goal.

The third way would have been to create a helper column in the DB that would accomodate the conditions put in the analysis worksheet, but I find it slow and not exactly elegant.

 

[Peter Bartholomew]

Hi @FiLoX
I had assumed that helper ranges that match the number of database records would not be attractive so I assumed that an array was needed as input to the SMALL function. I found the task of comparing two comma-separated arrays for common members somewhat daunting so decided to separate the compound search string into a row array (either by formula or by alternative data input). Comparison will then give rise to a 2D array so I used MMULT to sum across each row (effectively an OR condition).

From there I can get an array of those record that meet one of the criteria by number. The rest, as they say, is history.