• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find row and column

M

momi

New Member
Hi all,

I would need help, I have an array A1:H5 with unique names, I need to find one name in this array and I need to know column and row where this name is "sitting".

thanks for help.

Michal
 
Hi Michal ,


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
 
Nice formula Narayan - I hadn't noticed that the array is in 2D.


One question - do you actually need to use the MAX function or will it work without it?


- Juanito
 
Hi Juanito,


thanks, I tried to do that, but it gave me NA

=ROW(INDEX(A1:H5,MATCH(K1,A1:H5,0)))


what I am doing wrong?


thanks.

Michal
 
Hi Momi,


=ROW(INDEX(A1:H5,MATCH(K1,A1:H5,0))) (Wrong Syntax)

=ROW(INDEX(A:A,MATCH(K1,A:A,0))) (Correct Syntax - INDEX & MATCH will search either in column or row)


Thanks,

Suresh Kumar S
 
Hi Juanito ,


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
 
Hi Suresh,

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
 
Can we use an UDF? Using this short one

[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
[/pre]
The function in worksheet becomes:

=GetAddress(K1,A1:H5)
 
Interesting stuff. Thanks for your explanations, Narayan: I think it's because of the 2D array.


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
 
Hi Michal,


The syntax for INDEX & MATCH formula will search for a single column or row only not for morethan one column or row.


Thanks,

Suresh Kumar S
 
Hi Suresh Kumar,

You can use MATCH to search across rows and columns by reversing the arguments.


For example:

=MATCH(A1:C2, E1, 0)


Cheers,

Sajan.
 
Building on Narayan's ground work following ARRAY formula (since it uses SUMPRODUCT) can be used without CTRL + SHIFT + ENTER:


=ADDRESS(SUMPRODUCT((A1:H5=K1)*ROW(A1:H5)),SUMPRODUCT((A1:H5=K1)*COLUMN(A1:H5)))
 
You must log in or register to reply here.
Back
Top