Find text within a table

[b0unce]

Hello,

I am trying to write a formula, which gave me the address of a cell which contains specific text. Example:

	A	B	C	D
1		Text to find	Result
2		Apples	C6
3
4
5	Grapes	Bananas	Pineapples	Potatoes
6	Pears	Carrots	Apples	Strawberries
7	Peas	Cucumbers	Passionfruits	Tomatoes

<tbody>
</tbody>

In cell C2 I need to type in a formula, which searched the table A5:D7 for a text string which is in B2 and displayed the resulting cell address. Have tried multiple options but nothing seems to work.
Does anyone have any ideas how to achieve the wanted result?
Thank you in advance!

 

[JoeMo]

Here's a UDF you can use until someone provides you with the proper combination of native worksheet functions to solve your problem. An example of use of the UDF is below, followed by instructions for installing the UDF and the UDF itself.

Excel Workbook
	A	B	C	D
1		Text to find	Result
2		Apples	C6
3
4
5	Grapes	Bananas	Pineapples	Potatoes
6	Pears	Carrots	Apples	Strawberries
7	Peas	Cucumbers	Passionfruits	Tomatoes
Decade

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function FoundTextAddress(R As Range, Txt As String) As String
Dim Fnd As Range
Set Fnd = R.Find(Txt, lookat:=xlWhole)
If Fnd Is Nothing Then
    FoundTextAddress = ""
Else
    FoundTextAddress = Fnd.Address(0, 0)
End If
End Function

 

[Aladin Akyurek]

With a formula... Control+shift+enter, not just enter:

=IF(COUNTIFS(A5:D7,B2)=1,ADDRESS(MIN(IF(A5:D7=B2,ROW(A5:D7))),MIN(IF(A5:D7=B2,COLUMN(A5:D7))),4),"")