0

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?

Improve this question
2
  • By matches, I take it you mean indices or something a bit more complex than reprinting "apple"? The problem with the obvious approach is that match() only returns the first match, so you'd not get multiple results. Commented Apr 19, 2021 at 14:57
  • Sure, you just mark the range, the formula result shall be applied to and write the formula with the range, where the conditions are in. Commented Apr 19, 2021 at 15:38

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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 *.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.