Search A Range For Specific Text String

I'm stuck trying to create a formula searching a range on a row to find specific text. The problem I have is the text string could be found in other cells that I need to ignore.

For example (attached) I want to search for the word "Communication" in the range A:H and return the value in the column to the right. But not where the word "Info" appears. So in my example I want to find the string "Communication" only, but "Information and Communication" also contains the same word, but I want it to ignore cells that have the string "Information" in it.

Can this be done?

In row 2 of the example, cell C2 meets the criteria so I'm looking for the result in col I to be D2. Hope that makes sense.

Originally Posted by Glenn Kennedy

Hi. If I've understood you correctly, this is what you need.

Yes, and no. It provides the correct result but, I had tried to simplify my example but this may have caused me to give you too little info.

The actual data I will be using has a long text string which will use the word "Communication" in them, but they are not all necessarily the same string. The only one's I want to ignore are those that contain both "Communication" and "Information".

So, for example:
"Department of Communication", "Diploma in Communication" and "Level 4 Certificate in Communication" are all acceptable. "Department of Information and Communication" isn't because it contains the string "Information".

I'm guessing this isn't possible?

Not a problem I just thought it odd no-one else had responded over the last few days.

Thanks.

Originally Posted by FlameRetired

I may be lost, too, but as I interpret, this formula in I2 and filled down:

Formula:

=COUNTIF($A2:$H2,"*Communication*")-COUNTIF($A2:$H2,"*Information*")

Results look like this:

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Subject	Dept	Subject	Dept	Subject	Dept	Subject	Dept
2	Information and Communication	1	Communication	1	Progress	2	Development	1	1
3	Progress	2	Development	2	Progress	1			0
4	Development	2	Information and Communication	2			Progress	2	0
5	Information and Communication	1	Communication	2	Development	2			1
6	Communication	1	Development	2					1
7

Does this do what you want a net count?

No, I need the formula to return the value in the column to the right of where "Communication" appears without the string "Information". So, for example, A6 meets the criteria therefore I need the formula in I6 to return the value in B6.

Thanks anyway.

Interpreting another way in J2 and filled down:

Formula:

=IFERROR(INDEX($A2:$H2,,MATCH("Communication",IF(COUNTIF($A2:$H2,"*Communication*")-COUNTIF($A2:$H2,"*Information*"),$A2:$H2,""),0)+1),"")

Results look like this:

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Subject	Dept	Subject	Dept	Subject	Dept	Subject	Dept
2	Information and Communication	1	Communication	1	Progress	2	Development	1	1	1
3	Progress	2	Development	2	Progress	1			0
4	Development	2	Information and Communication	2			Progress	2	0
5	Information and Communication	1	Communication	2	Development	2			1	2
6	Communication	1	Development	2					1	1

ProjectD1.xlsxThanks guys, these work.

Now to test my luck. As an afterthought, what if I wanted to return other adjacent cells and not just the first adjacent column value?

I've attached an extended spreadsheet example and in this, again A6 meets the criteria and R6 should return B6, S6 return C6 and so on for T6 and U6.

Originally Posted by boopathiraja

the above formula assume that you will have a certain match, if no match found will return from column 1, if you want blanks if no match found need to include an criteria, clarify that

Thanks, I do need blanks if no match found.

This in R2 and filled down and across.

Formula:

=IFERROR(INDEX(A2:H2,,MATCH("Communication",IF(COUNTIF($A2:$H2,"*Communication*")-COUNTIF($A2:$H2,"*Information*"),$A2:$H2,""),0)+1),"")

Originally Posted by boopathiraja

hi, Apply this in R2 and drag it down and across

Formula:

=IF(SUMPRODUCT(COLUMN($A2:$Q2),(ISNUMBER(SEARCH("communication",$A2:$Q2))*1)+(ISNUMBER(SEARCH("information",$A2:$Q2))*-1))=0,"",IF(COLUMN(OFFSET(INDEX($A2:$Q2,,SUMPRODUCT(COLUMN($A2:$Q2),(ISNUMBER(SEARCH("communication",$A2:$Q2))*1)+(ISNUMBER(SEARCH("information",$A2:$Q2))*-1))+1),,COLUMN(A2)-1))<=17,OFFSET(INDEX($A2:$Q2,,SUMPRODUCT(COLUMN($A2:$Q2),(ISNUMBER(SEARCH("communication",$A2:$Q2))*1)+(ISNUMBER(SEARCH("information",$A2:$Q2))*-1))+1),,COLUMN(A2)-1),""))

Thanks but this works perfectly in the example spreadsheet I uploaded but, for some reason, when I input it to my actual spreadsheet it returns random blanks and #VALUE errors.

Originally Posted by FlameRetired

This in R2 and filled down and across.

Formula:

=IFERROR(INDEX(A2:H2,,MATCH("Communication",IF(COUNTIF($A2:$H2,"*Communication*")-COUNTIF($A2:$H2,"*Information*"),$A2:$H2,""),0)+1),"")

Again, this one works exactly as required when input to the uploaded example but when I input it to my real spreadsheet it only returns blank cells.

I tried to simplify the example worksheet for ease as there are around 150+ columns on my actual real worksheet. When I've input both your formulas to my actual spreadsheet I have expanded the range for checking but it doesn't work.

I've attached the actual worksheet I will be working on and the range I need to check for the "communication" and "information" criteria, as previously explained is Y:FU. I'd be grateful for an answer to this one as I'm really scratching my head now.

Originally Posted by boopathiraja

Hi try this...

Formula:

=IF(SUMPRODUCT(COLUMN($Y2:$VA2),(ISNUMBER(SEARCH("communication",$Y2:$VA2))*1)+(ISNUMBER(SEARCH("information",$Y2:$VA2))*-1))<>0,IFERROR(INDEX($Y2:$VA2,,SUMPRODUCT(COLUMN($Y2:$VA2),(ISNUMBER(SEARCH("communication",$Y2:$VA2))*1)+(ISNUMBER(SEARCH("information",$Y2:$VA2))*-1))-COLUMNS($A$2:$X$2)+COLUMN(A$1)-1),""),"")

Excellent, that seems to work exactly, thank you for your help...

Can I ask, and sorry to be a pain, what if I want a simple search of the same range and return the same cell results? When I say simple search I mean a search that doesn't involve having to eliminate a specific string as above. So, for example:

Search Y:VA for "Technology" and return the value in the cell to the right.

Apologies for resurrecting this thread after so long but I've been unable to respond till now.

I've applied the formula above but, whereas it does work on MOST rows, in others it incorrectly returns an incorrect value this being the value in the first cell of the range (col Y) instead of the value it should return from the correct column.

I'm at a loss as I have spent some time trying to identify a common denominator on the rows where this happens but I can't.

In the attached spreadsheet I've entered the formula in "Report" worksheet and the "Raw" worksheet is the reference data. As an example, "Report" row 46 doesn't show the correct result. Col B (for example) should return Raw!BI45 but it returns Raw!Y45 instead which, coincidentally, is the first cell of the referenced range. Hope this makes sense.