CODEWITHSUNDEEP
excelexcel-formula
Swink
Swink

Reputation: 448

Excel: if Cell has background fill, create copy on another cell

I have an excel sheet where I created a conditional formula where I can search through a range for a given word and if it is a match, The cell will have a background fill. Think of a search bar of sorts.

Is it possible where I can also show the matches in an adjacent cell as well.

For instance I search a range for the word apple. Two matches are then highlighted. Can the matches also be displayed in cells adjacent to the range?

Upvotes: 0

Views: 175

Answers (2)

Jay Mangal
Jay Mangal

Reputation: 50

You can use the SEARCH function to do this. In the adjacent cells use the formula:

=IF(IFERROR(SEARCH($D$5,B3),"")=1,B3,"")

enter image description here

If you want this to get auto-filled whenever you add another row, then add column B and C to a TABLE. That will ensure that when you add another row in B your column C also gets updated.

From your question, it seems that you've also used the SEARCH function. But if not I would recommend to try it in the conditional formatting as well since this identifies the string just like the wildcard *.

Upvotes: 0

Bartek Ple
Bartek Ple

Reputation: 11

I think it would be easier to just filter data. I am unsure if what you are trying to get is possible with contidional formatting.

Upvotes: 1

Related Questions