# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Search for String in Range, If Match, Return Matching Cell Value

## ryanb909

I'm having a dog of a time with a formula that will search for string in a range and if a match is found, then return the contents of the cell that matches. For instance in cell B1 if I am searching A1:A3 for the word "Red" then in B1 it would return "Red Apple" since red was found in A2. See small example below:

Column A   Column B

Blueberry    Red Apple
Red Apple
Green Apple

----------


## ajryan88

Hi,

Try the following *array formula* on your data set. It works for the sample data, you will just need to adjust the ranges to match your actual data:

```
Please Login or Register  to view this content.
```

Remember, this is an array formula, so you will need to use *Ctrl + Shift + Enter* to confirm it instead of just Enter.

Hope this helps  :Smilie:

----------


## Fotis1991

Try

=INDEX(A:A,MATCH("*Red*",A:A,0))

OR

Put the word that you are looking for in a cell-let's say in G1 and then use.

=INDEX(A:A,MATCH("*"&G1&"*",A:A,0))

----------


## ajryan88

Thanks Fotis, I wasn't aware of the use of a wildcard (*) in Excel formulas. I learnt something today!  :Smilie:

----------


## Fotis1991

*ALL* of us learning something new every day my friend! :Smilie:

----------


## ryanb909

> Put the word that you are looking for in a cell-let's say in G1 and then use.
> 
> =INDEX(A:A,MATCH("*"&G1&"*",A:A,0))



  Worked like an absolute charm...I actually needed to look for a specific string length of characters in multiple cells so I altered it a bit and pulled it down to several cells...if anyone is looking for another solution based on specific characters in a cell here is what I came up with thanks to Fotis1991:

=INDEX(GL$7:GL$2500,MATCH("*"&LEFT(D12,LEN(D12)-7)&"*",GL$7:GL$2500,0))

Thanks everyone!

----------


## Fotis1991

You are welcome and thanks for the feed back. :Smilie: 

It's always very pleasure for me to see someone to use a formula that i suggested in such a cleaver way! Gongr! :Smilie: 

As that takes care of your original question, please select *Thread Tools* from the menu link above and mark this thread as SOLVED. Thank you. 

*Also*, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of* those* who helped.

----------

