Lookup for a word in a table
 

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

Lookup for a word in a table
 

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


On 7 Maj, 09:59, T.Mad wrote:
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

Lookup for a word in a table
 

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" wrote:

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

Lookup for a word in a table
 

thanks you 've been very helpful...

Lookup for a word in a table
 

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" wrote:

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


On 7 Maj, 09:59, T.Mad wrote:
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

Lookup for a word in a table
 

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


On 7 Maj, 11:10, T.Mad wrote:
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",COÂ*LUMN($A$1:$C$3), ""))

Many thanks again!!!!
Theo



"Jarek Kujawa" wrote:
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

On 7 Maj, 09:59, T.Mad wrote:
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- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Lookup for a word in a table
 

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

"Jarek Kujawa" wrote:

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


On 7 Maj, 11:10, T.Mad wrote:
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",COÂ*LUMN($A$1:$C$3), ""))

Many thanks again!!!!
Theo



"Jarek Kujawa" wrote:
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

On 7 Maj, 09:59, T.Mad wrote:
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- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Lookup for a word in a table
 

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)),SUM PRODUCT(($A$1:$C$3=A5)*COLUMN(A1:C1)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T.Mad" wrote in message
...
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