function to find value and return cell reference

What if >1 cell meets the criteria?

Then the cell reference of the first find should be returned

Define "first".

For example, which would be "first", A5, C3, or E1, and why?

If "300" is there in the cell B5 and the cell D6 then the function should return the reference of B5

oldrin,

With respect, your example does not demonstrate a rule that would determine which cell is "first".  Let's return to my example: out of A5, C3, or E1, which cell would you consider to be "first"?

If A5, that suggest you want to search each column left to right, and top to bottom within columns.

If E1, that suggests you want to search each row top to bottom, and left to right within rows.

If C3, then that suggests the rule is something else, but it would not be immediately clear just what that rule is.

Patrick

Patrick,

Sorry, did not take into consideration the different possible rules.

For my problem, the second rule of "E1" would be perfect. Searching the rows from top to bottom of the specified range.

How do you define 'partly matched'? Is your data table sorted?

Example for partly matched would be:

if I want to find a text "current" from a range a1:e25, and the cell "B5" contains text "Current update" then the function should return the reference of the "B5" because it has partly matched.  The data in the table is not sorted. The data is actually an output of a webquery.

Assuming the text "current" in I1:
In J1: =MIN(IF(ISNUMBER(SEARCH("Current",$A$1:$E$25)),ROW($A$1:$E$25)+COLUMN($A$1:$E$25)/1000))
array-entered with Ctrl+Shift+Enter
in K1: =ADDRESS(INT(J1),MOD(J1,1)*1000)

Thanks Rorya,

I was looking for a vba code which I could use it as a function.

I have found the below code which works for an exact match.  I do not know how it works so I am not able to modify it for my requirement of a part match.

Could you please modify it for my requirement ?

Thanks

Select allOpen in new window

Function findit(v As Variant, r As Range) As String
findit = ""
For Each rr In r
If rr.Value = v Then
findit = rr.Address
Exit Function
End If
Next
End Function

Select allOpen in new window

Thanks Rorya.

Worked perfectly.