Lookup for a word in a table

[T.Mad]

Hi there,
I wonder if anyone can give me a good piece of advice in how to find a
specific value in a table. For example I have the next table:
A B C
1 JOHN MICHAEL MAURO
2 GEORGES STEVEN MARY
3 KIM YAN ANN

I want to find the coordinates of the word MARY. Thus, I need two lookup
functions one which will return the ROW of this word (2nd) and one which will
return the COLUMN of this word (3rd).
The problem with the existing lookup functions (eg MATCH) is that they can
only accept an array to lookup and not a two dimensional table. Please
correct me if I am wrong.
Is it possible to do it with macro functions?
Many thanks
Theo

 

[Jarek Kujawa]

would this array formula (CTRL+SHIFT+ENTER to insert instead of simply
entering it):

=MIN(IF($A$1:$C$3="MARY",ROW($A$1:$C$3),""))&"/"&MAX(IF($A$1:$C
$3="MARY",COLUMN($A$1:$C$3),""))

help?

adjust yr ranges to suit

 

[Mike H]

Hi,

I'm not sure what you want. If you want the address of the name try this
with your lookup value in D1

=CELL("Address",INDEX(A1:C3,MIN(IF(A1:C3=D1,ROW(A1:C3)-ROW(A1)+1)),MIN(IF(A1:C3=D1,COLUMN(A1:C3)-COLUMN(A1)+1))))

If you want the row change 'ADDRESS' to ROW in the formula and for the
column change ADDRESS to COL

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

 

[T.Mad]

thanks you 've been very helpful...

 

[T.Mad]

Dear Jarek ,
Please just explain to me why the first one is MIN and the other is MAX.
Is there something wrong with the next expression?
=MIN(IF($A$1:$C$3="MARY",ROW($A$1:$C$3),""))&"/"&MIN(IF($A$1:$C$3="MARY",COLUMN($A$1:$C$3),""))

Many thanks again!!!!
Theo

 

[Jarek Kujawa]

my fault
should be MIN and MIN of course
sorry
your formula does the same what mine was meant to do

 

[T.Mad]

No worries Jarek,
I 've sent you an email cause I thought that u didn't see my reply.
Many thanks again..

 

[Ashish Mathur]

Hi,

In this specific example where data starts from row 1, you can use this
formula. A5 holds Mary

=ADDRESS(SUMPRODUCT(($A$1:$C$3=A5)*ROW(C1:C3)),SUMPRODUCT(($A$1:$C$3=A5)*COLUMN(A1:C1)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com