Lookup for a value in a table

T

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
 
B

Bob Phillips

To get the row, use this array formula

=MIN(IF(A1:C6="Mary",ROW(A1:C6)))

and the column

=MIN(IF(INDEX(A1:C6,MIN(IF(A1:C6="Mary",ROW(A1:C6))),0)="Mary",COLUMN(A1:C6)))

in the latter you can replace the first formula that is embedded within by a
refrence to the cell with the first
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top