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

Question

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

Answer 1

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

Answer 2

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