Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup for a value 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup for a value in a table
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 -- __________________________________ HTH Bob "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup for a value in a table
Please don't multi post, you have an answer in worksheet functions
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup value in table | Excel Worksheet Functions | |||
Lookup Table | Excel Discussion (Misc queries) | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Lookup table help please | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |