Finding a row number

F

Farhad

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,
 
D

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.)
 
X

xrelanon

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.
 
F

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
 
P

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
 

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