You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter momi
- Start date

Try this :

=ADDRESS(MAX((A1:H5=K1)*ROW(A1:H5)),MAX((A1:H5=K1)*COLUMN(A1:H5)))

where K1 contains the name you wish to match.

The above formula is to be entered as an array formula , using CTRL SHIFT ENTER.

Narayan

You can see that if the MAX is removed , then the formula does not function as an array formula , even when you enter it using CTRL SHIFT ENTER. So , MAX is necessary.

Narayan

I changed it to correct syntax (according your message), but it again does not work, what if value, that I need to find is not in column A, but in column D.

(note: value, that I need to find, is in column A, than it works, but value, that I need to find is in column D).

thanks.

regards.

Michal

[pre]

Code:

```
Function GetAddress(FindValue As Variant, SearchRange As Range) As String
Dim FoundCell As Range
Set FoundCell = Nothing
Set FoundCell = SearchRange.Find(FindValue)
If FoundCell Is Nothing Then
GetAddress = "Not found"
Exit Function
Else
GetAddress = FoundCell.Address(False, False)
End If
End Function
```

The function in worksheet becomes:

=GetAddress(K1,A1:H5)

Michal: my formula will only work on a 1D array (vector). You should follow Narayan's way - he's a ninja after all!

Alternatively, use Luke's UDF (if you know how to include it in your file) - once you've done it, it's really easy to invoke.

- Juanito

=ADDRESS(SUMPRODUCT((A1:H5=K1)*ROW(A1:H5)),SUMPRODUCT((A1:H5=K1)*COLUMN(A1:H5)))