Alternative to nesting formula? "IF(ISNUMBER(SEARCH"

[ad9051]

Hello guys,

Thanks for taking the time to look at my problem,

I want to create a formulae that looks as a cell to the left and performs a function if it contains w or x, or y, or z and so on. Normally i would use a series of nested "=IF(ISNUMBER(SEARCH("v",A2))" but my list of possible searches (x/y/z) is more than 30. The formula is getting ridiculous. Is there a simpler way of doing this?? I could put my list of possible searches (x/y/z's) in an array for formula reference.

Any help would be greatly appreciated.

Many thanks

Alan

[Richard Buttrey]

Hi,

Sounds like you should be using =VLOOKUP() using a table containing your w,x,y etc. values in column 1 and the results you want to return in column 2 of the table.
Upload the workbook with some manually calculated results if you're still unsure.

[mikerickson]

You could use a formula like
=IF(SUMPRODUCT(COUNTIF(A2, "*" & {"a","x","y"} & "*"))<>0, "do something", "not")

I agree that it would be easier to maintain if you have the key characters in a range, in which case the formulation would be

=IF(SUMPRODUCT(COUNTIF(A2, "*" & AA1:AA30 & "*"))<>0, "do something", "not"), which will give you false postitive if any of AA1:AA30 is blank

[ad9051]

Hi thanks both for the reply's, i like the idea of the array formula but i do not know how to write them as of yet. I have provided an example of what i am trying to achieve. Hopefully if either of you can create an equation to solve this more efficiently than i am currently (not hard) then i will be able to adapt it to my needs.

Many thanks again for the help!

Alan

nest formula example.xlsx

[mikerickson]

I don't understand what you are trying to do.
Is the list Dave, Fred, Millie in the attachment the desired result or is it an input?
If it is an input, what result do you want.

[Richard Buttrey]

Hi,

It would really assist if you could upload your actual workbook. We don't need it all, just a representative sample and you should of course anonymise the names and any other confidential stuff, but it's useful to be able to see the requirement in context.

I'm also bemused by your comment that you have 'hundreds of thousands of data tables. I'm presuming you don't really mean that you have that many lists similar to the UK table you show but I am perplexed.

It may be that when we see your actual workbook that there is a simpler way of organising it in order to find a name within a table.

One thing that isn't clear is how is the system expected to know that you are searching for say 'Dave' as per your example, and not say 'Text Dave Ra' or some other string? Is random text really random in length, both before and after the name, e.g. Dave, or is its length fixed so that the string dave can be identified by its position?

Regards

[ad9051]

Sorry Mike, its so easy to assume people understand what you desire when making examples. Erm, Ok so the desired result is in Column E. This is simply searching the datasets titles in the left for one of the three "UK names" listed (Dave, Fred, Millie.) If there is a match it is labeling them as UK. For those that it does not match it will simply leave blank. As mention before my real list of names to search for is over 30. The "random text" represents data unimportant to this example.

Thanks again

Alan

[mikerickson]

Put this in E5 and drag down
=IF(SUMPRODUCT(COUNTIF(A5,"*"&I6:I8&"*"))<>0,"UK","")

[ad9051]

@ Richard Buttrey

I am afraid i cannot upload my real example as mentioned before, and apologies i do not have hundreds of thousands of data sets, but data sets which take up hundreds of thousands of rows (average dataset 8.654 rows). I merely quoted this fact so to highlight the need for an efficient formula rather than my clunky thing.

The text either side of "Dave" in my example can be any amount long But will always be separated by a space, you would never encounter "textextDavetext" It would be in the format "texttext Dave text" also as per this example the string dave can be identified by its position. I was hoping a formula would simply be able to search within the body of text for any one of a given list, if it returns true enter desired text (in this case UK) if not do nothing.

Thanks again for your reply

All the best

Alan

[Richard Buttrey]

....I suspect this is going to be easier if you have your names in one column of a table and their locations, e.g. "UK" alongside in a second column.

Regards

[ad9051]

Ultimate referencing the array fixed it. "=IF(SUMPRODUCT(COUNTIF(A5,"*"&$I$6:$I$8&"*"))<>0,"UK","")" Many thanks Mike

I will annotate your reputation as such!

Would it be possible to explain what each of these functions achieves when put into a formaula : "*"& ?

This could prove very useful when i come to write more advanced formaula.

Thanks again

Alan

[Richard Buttrey]

Hi,

With the aforementioned table in H:I then E5:

=VLOOKUP(MID(A5,FIND(" ",A5)+1,FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1),I6:J30,2,FALSE)

[daddylonglegs]

Just a comment on Mike's suggestion. If you are looking for Fred and Freda is in A5 then you'll get a "false positive" UK result, so given that you say there will always be spaces either side this version will count Fred but not Freda

=IF(SUMPRODUCT(ISNUMBER(SEARCH(" "&I$6:I$8&" "," "&A5&" "))+0),"UK","")

[ad9051]

Thanks Longlegs!

[mikerickson]

To explain my formula:

"*"&$I$6:$I$8&"*" is an array of values begining and ending with the wildcard *

COUNTIF(A5,"*X*") returns the number of cells in the range A1 which contain an X. (i.e. 1 or 0)

COUNTIF(A5,"*"&I6:I8&"*") is an array of the number of cells that contain I6, then the number of cells that contain I7, then the number of cells that contain I8

SUMPRODUCT(COUNTIF(A5,"*"&I6:I8&"*")) adds all of those numbers.

If SUMPRODUCT(...) = 0, then none of the values in I6:I8 are in A5.

[kvsrinivasamurthy]

In E5 the below array formula can be used.A volatile range "UKnames" is used so that you can add names to the list without changing anything in formula.Uk names cover from I6 to end of that column upto I20.You can drag down the formula.
Pl see attached file.

Please Login or Register  to view this content.