oldrin

asked on

# function to find value and return cell reference

Hi,

I'm looking for a function that will find a partly matched value (the value does not need to be an exact match) somewhere on a spreadsheet

and return its cell reference. For instance, I'd like to be able to have a

function that will take '300' and the data range 'A1:E35' and then return the

cell that '300' is found in, i.e. D3 (if indeed D3 contains '300').

I'm looking for a function that will find a partly matched value (the value does not need to be an exact match) somewhere on a spreadsheet

and return its cell reference. For instance, I'd like to be able to have a

function that will take '300' and the data range 'A1:E35' and then return the

cell that '300' is found in, i.e. D3 (if indeed D3 contains '300').

What if >1 cell meets the criteria?

ASKER

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?

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

ASKER

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

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

ASKER

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.

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?

ASKER

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.

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)

In J1: =MIN(IF(ISNUMBER(SEARCH("C

array-entered with Ctrl+Shift+Enter

in K1: =ADDRESS(INT(J1),MOD(J1,1)

ASKER

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

findit = ""

For Each rr In r

If rr.Value = v Then

findit = rr.Address

Exit Function

End If

Next

End Function

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

Function findit(v As Variant, r As Range) As Stringfindit = ""

For Each rr In r

If rr.Value = v Then

findit = rr.Address

Exit Function

End If

Next

End Function

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thanks Rorya.

Worked perfectly.

Worked perfectly.