find row and column

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

juanito

Member
Try this:

=ROW(INDEX(array,MATCH(name,array,0)))

(Same for COLUMN)

- Juanito

NARAYANK991

Excel Ninja
Hi Michal ,

Try this :

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

juanito

Member
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

momi

New Member
HI Narayan,

thanks for help, it is working perfectly. thanks

Michal

momi

New Member
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

sdsurzh

Member
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

NARAYANK991

Excel Ninja
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

momi

New Member
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

Luke M

Excel Ninja
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
Exit Function
Else
End If

End Function``````
[/pre]
The function in worksheet becomes:

juanito

Member
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

sdsurzh

Member
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

Sajan

Excel Ninja
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.

shrivallabha

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