Finding a row number

[Guest]

Hi,

Is there a function in Excel that return the row number of a lookup value in
the main list?

A
1 aaa
2 bbb
3 ccc
4 ddd

what i want is to look in the list say for "ccc" and return number 3 which
is the number of row that my lookup data is in it.

Thanks,

 

[Guest]

=MATCH("ccc",A1:A10)

 

[Dave Peterson]

Since the formula is matching on text, it may better to look for an exact match:

=match("ccc",a1:a10,0)
or
=match("ccc",a:a,0)
(if the whole column could be used.)

 

[Guest]

How you have it MATCH works but this is not the way to do things.

Instead, put your 1, 2, 3 and 4 numbers in column B and use VLOOKUP to find
"ccc", better yet if you do not have many items in column A, use a Data
Validation Pick List so the user can select the exact item from the list in
an input cell and return the correct answer using VLOOKUP. The advantage here
is that you can add other columns to your table and specifiy VLOOKUP to find
another column value in a row.

 

[Farhad]

Hi,

Thanks for your help but what if my data would be like below:

A
..
..
15 aaa
16 bbb
17 ccc
18 ddd

so if i put formula like: =MATCH("ccc",A15:A18) the resuilt comes up 3 but
if i put the formula like: =MATCH("ccc",A1:A18) the result comes up 17.
Please advice.

Thnaks

 

[Pete_UK]

Match returns the relative position of the found item in the range. In
your first case the range starts at row 15, so you need to add 14 onto
the result to get the absolute row number, but in the second case your
range starts at row 1, so the result is the same as the row number.

Hope this helps.

Pete