Conditional formatting for contains certain text from a range of values

up vote
0
down vote

favorite

1

How do you do conditional formatting to find out if range 2 contains the text value from range 1? For example check for Apple in red apple should highlight it.
I need to compare A3:A6 with C3:C7. The end result should be C3, C4 and C5 highlighted.

A   B            C

1 Range1       Range2

==========================

3 Apple        Red Apple

4 Orange       Green Apple

5 Pear         Orange

6 Watermelon   Banana

7              Kiwi

I tried this formula but it only check B3 against Range2 and skips B4 to B6.

=FIND($B$3:$B$6,C3)>0

excel conditional-formatting

share|improve this question

edited yesterday

asked yesterday

Manick9

579

add a comment | 

up vote
0
down vote

favorite

1

How do you do conditional formatting to find out if range 2 contains the text value from range 1? For example check for Apple in red apple should highlight it.
I need to compare A3:A6 with C3:C7. The end result should be C3, C4 and C5 highlighted.

A   B            C

1 Range1       Range2

==========================

3 Apple        Red Apple

4 Orange       Green Apple

5 Pear         Orange

6 Watermelon   Banana

7              Kiwi

I tried this formula but it only check B3 against Range2 and skips B4 to B6.

=FIND($B$3:$B$6,C3)>0

excel conditional-formatting

share|improve this question

edited yesterday

asked yesterday

Manick9

579

add a comment | 

up vote
0
down vote

favorite

1

up vote
0
down vote

favorite

1

1

How do you do conditional formatting to find out if range 2 contains the text value from range 1? For example check for Apple in red apple should highlight it.
I need to compare A3:A6 with C3:C7. The end result should be C3, C4 and C5 highlighted.

A   B            C

1 Range1       Range2

==========================

3 Apple        Red Apple

4 Orange       Green Apple

5 Pear         Orange

6 Watermelon   Banana

7              Kiwi

I tried this formula but it only check B3 against Range2 and skips B4 to B6.

=FIND($B$3:$B$6,C3)>0

excel conditional-formatting

share|improve this question

edited yesterday

asked yesterday

Manick9

579

How do you do conditional formatting to find out if range 2 contains the text value from range 1? For example check for Apple in red apple should highlight it.
I need to compare A3:A6 with C3:C7. The end result should be C3, C4 and C5 highlighted.

A   B            C

1 Range1       Range2

==========================

3 Apple        Red Apple

4 Orange       Green Apple

5 Pear         Orange

6 Watermelon   Banana

7              Kiwi

I tried this formula but it only check B3 against Range2 and skips B4 to B6.

=FIND($B$3:$B$6,C3)>0

excel conditional-formatting

excel conditional-formatting

share|improve this question

edited yesterday

asked yesterday

Manick9

579

share|improve this question

edited yesterday

asked yesterday

Manick9

579

share|improve this question

share|improve this question

edited yesterday

edited yesterday

edited yesterday

asked yesterday

Manick9

579

asked yesterday

Manick9

579

asked yesterday

Manick9

579

579

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

up vote
1
down vote



accepted

It is possible and I had fun working on this problem but I really wish you had done some work as well - seeing your own effort would have been just as rewarding as solving the problem by myself.

The formula to be used with conditional formatting is:
=NOT(ISERROR(AGGREGATE(15, 6, ROW($A$1:$A$4)/SEARCH($A$1:$A$4,B1), 1)))=TRUE

It obviously works as a normal, non-array formula too:

share|improve this answer

edited 18 hours ago

answered yesterday

Michal Rosa

534210

• 
  
  
  

  
  

  
  
  
  
  
  

  
  But we can't use array formula in conditional formatting?
  – Manick9
  yesterday
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It's impossible to use one that requires Control-Shift-Enter to work, but it's possible to use one that uses the AGGREGATE function as per my solution. Phew. That was fun to solve.
  – Michal Rosa
  17 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks a lot! The conditional formatting formula looks quite complicated.
  – Manick9
  16 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372345%2fconditional-formatting-for-contains-certain-text-from-a-range-of-values%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
1
down vote



accepted

It is possible and I had fun working on this problem but I really wish you had done some work as well - seeing your own effort would have been just as rewarding as solving the problem by myself.

The formula to be used with conditional formatting is:
=NOT(ISERROR(AGGREGATE(15, 6, ROW($A$1:$A$4)/SEARCH($A$1:$A$4,B1), 1)))=TRUE

It obviously works as a normal, non-array formula too:

share|improve this answer

edited 18 hours ago

answered yesterday

Michal Rosa

534210

• 
  
  
  

  
  

  
  
  
  
  
  

  
  But we can't use array formula in conditional formatting?
  – Manick9
  yesterday
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It's impossible to use one that requires Control-Shift-Enter to work, but it's possible to use one that uses the AGGREGATE function as per my solution. Phew. That was fun to solve.
  – Michal Rosa
  17 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks a lot! The conditional formatting formula looks quite complicated.
  – Manick9
  16 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote



accepted

It is possible and I had fun working on this problem but I really wish you had done some work as well - seeing your own effort would have been just as rewarding as solving the problem by myself.

The formula to be used with conditional formatting is:
=NOT(ISERROR(AGGREGATE(15, 6, ROW($A$1:$A$4)/SEARCH($A$1:$A$4,B1), 1)))=TRUE

It obviously works as a normal, non-array formula too:

share|improve this answer

edited 18 hours ago

answered yesterday

Michal Rosa

534210

• 
  
  
  

  
  

  
  
  
  
  
  

  
  But we can't use array formula in conditional formatting?
  – Manick9
  yesterday
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It's impossible to use one that requires Control-Shift-Enter to work, but it's possible to use one that uses the AGGREGATE function as per my solution. Phew. That was fun to solve.
  – Michal Rosa
  17 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks a lot! The conditional formatting formula looks quite complicated.
  – Manick9
  16 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote



accepted

up vote
1
down vote



accepted

It is possible and I had fun working on this problem but I really wish you had done some work as well - seeing your own effort would have been just as rewarding as solving the problem by myself.

The formula to be used with conditional formatting is:
=NOT(ISERROR(AGGREGATE(15, 6, ROW($A$1:$A$4)/SEARCH($A$1:$A$4,B1), 1)))=TRUE

It obviously works as a normal, non-array formula too:

share|improve this answer

edited 18 hours ago

answered yesterday

Michal Rosa

534210

It is possible and I had fun working on this problem but I really wish you had done some work as well - seeing your own effort would have been just as rewarding as solving the problem by myself.

The formula to be used with conditional formatting is:
=NOT(ISERROR(AGGREGATE(15, 6, ROW($A$1:$A$4)/SEARCH($A$1:$A$4,B1), 1)))=TRUE

It obviously works as a normal, non-array formula too:

share|improve this answer

edited 18 hours ago

answered yesterday

Michal Rosa

534210

share|improve this answer

share|improve this answer

edited 18 hours ago

edited 18 hours ago

edited 18 hours ago

answered yesterday

Michal Rosa

534210

answered yesterday

Michal Rosa

534210

answered yesterday

Michal Rosa

534210

534210

• 
  
  
  

  
  

  
  
  
  
  
  

  
  But we can't use array formula in conditional formatting?
  – Manick9
  yesterday
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It's impossible to use one that requires Control-Shift-Enter to work, but it's possible to use one that uses the AGGREGATE function as per my solution. Phew. That was fun to solve.
  – Michal Rosa
  17 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks a lot! The conditional formatting formula looks quite complicated.
  – Manick9
  16 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  But we can't use array formula in conditional formatting?
  – Manick9
  yesterday
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It's impossible to use one that requires Control-Shift-Enter to work, but it's possible to use one that uses the AGGREGATE function as per my solution. Phew. That was fun to solve.
  – Michal Rosa
  17 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks a lot! The conditional formatting formula looks quite complicated.
  – Manick9
  16 hours ago
  

  
  
  
  

But we can't use array formula in conditional formatting?
– Manick9
yesterday

But we can't use array formula in conditional formatting?
– Manick9
yesterday

1

1

It's impossible to use one that requires Control-Shift-Enter to work, but it's possible to use one that uses the AGGREGATE function as per my solution. Phew. That was fun to solve.
– Michal Rosa
17 hours ago

It's impossible to use one that requires Control-Shift-Enter to work, but it's possible to use one that uses the AGGREGATE function as per my solution. Phew. That was fun to solve.
– Michal Rosa
17 hours ago

Thanks a lot! The conditional formatting formula looks quite complicated.
– Manick9
16 hours ago

Thanks a lot! The conditional formatting formula looks quite complicated.
– Manick9
16 hours ago

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372345%2fconditional-formatting-for-contains-certain-text-from-a-range-of-values%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here