Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row number
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, -- Farhad Hodjat |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row number
=MATCH("ccc",A1:A10)
-- Gary''s Student - gsnu200758 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row number
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.) Gary''s Student wrote: =MATCH("ccc",A1:A10) -- Gary''s Student - gsnu200758 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row number
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. -- Gnothi se auton. "Farhad" wrote: 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, -- Farhad Hodjat |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row number
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 -- Farhad Hodjat "Gary''s Student" wrote: =MATCH("ccc",A1:A10) -- Gary''s Student - gsnu200758 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row number
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 On Nov 22, 7:17 pm, Farhad wrote: 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 -- Farhad Hodjat "Gary''s Student" wrote: =MATCH("ccc",A1:A10) -- Gary''s Student - gsnu200758- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the number of a worksheet | Excel Discussion (Misc queries) | |||
Finding number less than 1 | Excel Discussion (Misc queries) | |||
finding a number and the number of times it occurs | Excel Discussion (Misc queries) | |||
finding the right number | Excel Worksheet Functions | |||
finding row number? | Excel Discussion (Misc queries) |