Complex cross-checking formula type thing that I can't work out...

**Falanx** · 02-22-2010, 05:34 AM

Good morning,

This is my first post on here, so be gentle...

I've tried searching these fora and a number of others for a solution for Excel problem, but so far found nothing to match. I thought I'd throw this one out to you guys and see if you can help... I hope you can :-)

I have two workbooks, one heavily protected and locked down with macros to prevent user fiddling, and with several different automatic updating and data copying features. The other is merely a single worksheet file that contains a set of data. Most of that data is confidential so I can't release it, but I *can* demonstrate what I need to do with two more simple workbooks...

I need, most probably a formula, to check the text string contents of a cell in Workbook 2 against the text string contents of a particular cell in Workbook 1 and then return a value to (either) that cell (or another one in the same row) dependant on the values in the cell of Workbooks 1 and 2.

The particular cell in Workbook 2 contains a text string which is a list of components, ('Check value') formatted separated by a comma and a space.

Additionally, that returned value (in 'Moo, Steve, Fred or <blank>?') is one of three possible values or none, rated against their importance, as you can see from the attached files. That order of importance is Moo>Steve>Fred. is for the event that none of the contents of 'Value 1' are found in 'Check value' and is not no return at all.

Is there any way of doing this? I can't even begin to imagine how...

**jrussell** · 02-22-2010, 06:04 PM

In cell D4 of TestBook2, you can extrapolate something from this:

=IF(NOT(ISERROR(FIND("a",C4))),"moo",IF(NOT(ISERROR(FIND("b",C4))),"moo","blank"))

It's nested IF statements. You'll need one for each letter to check. You'd do them in order of precedence and it will produce "moo" if there are any of a,b,c,d, "steve" if there are any of e,f,g, "fred" if there are any of h,i,j,k,l,m,n,o,p,q, and blank if there are none of them. Hopefully I understood at least part of what you meant.

**Falanx** · 02-23-2010, 04:44 AM

Originally Posted by jrussell

In cell D4 of TestBook2, you can extrapolate something from this:

=IF(NOT(ISERROR(FIND("a",C4))),"moo",IF(NOT(ISERROR(FIND("b",C4))),"moo","blank"))

It's nested IF statements. You'll need one for each letter to check. You'd do them in order of precedence and it will produce "moo" if there are any of a,b,c,d, "steve" if there are any of e,f,g, "fred" if there are any of h,i,j,k,l,m,n,o,p,q, and blank if there are none of them. Hopefully I understood at least part of what you meant.

Yes, you did :-) I had the feeling it was such, but I'm a little inexperienced manipulating IFs and their criteria :-) Thanks.

Further, regarding limits,

I'm given to understand that the maximum number of standalone 'IFs' is 7 in Excel 2003 and 30 if you concatenate them - is this correct, because on the actual file I've got perhaps as many as 150 values to search for with the possibility of more? There's a likelihood that this may then run headlong into the 255 character limit on formula...

**jrussell** · 02-23-2010, 10:14 AM

How many different categories are there/could there be (Moo, Steve, Fred, etc?) You can simplify formulas by dividing them into chunks. That tends to make them easier to understand as well. For instance, you could use the following formulas in 4 columns to get the desired result for the example (see attached file):

=IF(OR(NOT(ISERROR(FIND("a",C4))),NOT(ISERROR(FIND("b",C4))),NOT(ISERROR(FIND("c",C4))),NOT(ISERROR(FIND("d",C4)))),"moo","")

=IF(OR(NOT(ISERROR(FIND("e",C4))),NOT(ISERROR(FIND("f",C4))),NOT(ISERROR(FIND("g",C4)))),"steve","")

=IF(OR(NOT(ISERROR(FIND("h",C4))),NOT(ISERROR(FIND("i",C4))),NOT(ISERROR(FIND("j",C4))),NOT(ISERROR(FIND("k",C4))),NOT(ISERROR(FIND("l",C4))),NOT(ISERROR(FIND("m",C4))),NOT(ISERROR(FIND("n",C4))),NOT(ISERROR(FIND("o",C4))),NOT(ISERROR(FIND("p",C4))),NOT(ISERROR(FIND("q",C4)))),"fred","")

=IF(D4="moo","moo",IF(E4="steve","steve",IF(F4="fred","fred","")))

And here's more info on the nested-if limit: http://www.ozgrid.com/Excel/seven-nested.htm

**Falanx** · 02-24-2010, 10:45 AM

Thanks for the time you've put into this :-)

If you look at the attached files, there's a total of three different states, "Moo, steve, fred", but a lot more "Value 1"s. In the real workbook I'm working on, there's a possibility of over 200 "Value 1"s, and the more I look at this, the less it seems like a set of nested IFs will be sufficient.

If there was some way to treat a cell reference as its contents rather than a cell reference in a match fomula then I could possibly do it that way with some wildcarding, but I don't think that's possible, is it?

Either that or a macro, but I'm insufficiently skilled with them to build one...