# Excel if column partially contains string and on match the same row contains a different value

I need some help calculating the TRUE/FALSE section, I have filled it in manually to show what results I want to achieve.

Essentially, I need an `IF()` statement that will be TRUE on sheet 2 if the header and ItemName match on Sheet 1.

Sheet 1 - DATA

School ItemName
blabla sch1 com.123.abc
blabla sch1 com.456.def
blabla sch2 com.xxx.abc
blabla sch2 com.456.def

Sheet 2 - CALCs

ItemName sch1 sch2 sch3
com.123.abc TRUE FALSE FALSE
com.xxx.abc FALSE TRUE FALSE
com.456.def TRUE TRUE FALSE
com.123.abc TRUE FALSE FALSE
• Hi and welcome to Superuser 👋. The values in the Sheet 1 School column aren't the exact same as the headers in Sheet 2? What is the "blabla" there? May 28 at 21:34

A more generic approach to this issue that also works in cases where the school might have slightly varying patterns would be an `IF()` and `SUMPRODUCT()` type approach. Assuming your data is stored in the range `A1:B5` on the `DATA` sheet, and you perform your calculations in the range `A1:D5` on the `CALCs` sheet it looks as follows:

First, you can return the unique ItemNames using `=UNIQUE(DATA!\$B\$1:\$B\$5)`; as currently you have two times "com.123.abc" in your output. If this is intended you can just skip this an copy the ItemNames to the `CALCs` sheet. Next, you put in `B1:D1` the unique schools.

In cell `B2` you need to put the following formula and then copy it to the other cells respectively:

``````=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B\$1,DATA!\$A\$2:\$A\$5)))*
(ISNUMBER(SEARCH(\$A2,DATA!\$B\$2:\$B\$5)))), TRUE, FALSE)
``````

This function uses the `SEARCH()` and `ISNUMBER()` function to check if the ItemName and SchoolName is found in the List of ItemNames and SchoolNames respectively. The double negative `--` operator is used to convert the result, i.e., TRUE/FALSE to 1 and 0, which serves as the input to the `SUMPRODUCT()` function, which is then used to combine the results of the two components. If the sum is greater than zero, it will return TRUE and FALSE otherwise.

The output for the data you provided looks then as follows:

ItemName sch1 sch2 sch3
com.123.abc TRUE FALSE FALSE
com.456.def TRUE TRUE FALSE
com.xxx.abc FALSE TRUE FALSE

Now, to the benefits of this approach and how it is more generic. Assume you have input data from the schools that is not as clean; this divergence could take many forms, e.g., dot instead of space, no space, hypen instead of space and additional content at the end etc.

School ItemName
blabla sch1 com.123.abc
blabla.sch1 com.456.def
blablasch2 com.xxx.abc
blabla-sch2asdf com.456.def

Even in this case the result will be the same, and look just similar to the table before:

ItemName sch1 sch2 sch3
com.123.abc TRUE FALSE FALSE
com.456.def TRUE TRUE FALSE
com.xxx.abc FALSE TRUE FALSE

Just connect the cell values with some character that is difficult to type from the keyboard (for example, Tab = `CHAR(9)`) and instead of two search columns you get one. And in one column, the usual `MATCH()` will be able to find the desired pair of values, if you connect them through the same separator character. Oh yes, if the value pair is not found, then `MATCH()` will return a `#N/A` error. The `ISNUMBER()` function converts the search result to the very TRUE and FALSE that you need.

Since MATCH() knows what "wildcards" are, the formula is very simple:

``````{=ISNUMBER(MATCH("*"&B\$1:D\$1&CHAR(9)&\$A2:\$A5;DATA!\$A\$1:\$A\$5&CHAR(9)&DATA!\$B\$1:\$B\$5;0))}
``````

I proceeded from the same considerations as @karl - the data range is `DATA!\$A\$1:\$B\$5`, and the calculation is based on the values ​​in `B\$1:D\$1` and `A2:\$A5`

• This is pretty much what I did only I didn't realize I could use a wildcard and used ISERROR, which is pointless. ISNUMBER is much better here. What is the point of the separator though? It seems to work exactly the same without it (I didn't use one). You are adding it to both the search value and the array anyway so not adding it at all should produce the same results no? May 29 at 12:21
• @andrewb Yes, in most cases it is. However, take "blabla sch11"&"com.123.abc" and "sch1"&"1com.123.abc" and get "false positive" May 29 at 12:53
• I see! Thank you. I didn't think of that. May 29 at 13:09