Home 
Search 
Today's Posts 
#1




Need a function to return EXACT row number of a match
Can someone help me with a formula that returns the exact row number
(i.e. the row number on the side of the spreadsheet) where a match is found on? If possible, I DON'T want to use 'Match + offset number. For example: A B 480 bull turtle 481 Pig mouse 482 cat dog I would like to use something like =MATCH("Pig",$A$480:$B$482,0). Of course this will return a value of '2' but I want it to return a value of '481'. I need to be able to specify the area to search in because there are other rows that have the value "Pig" in it. TIA 
#2




Need a function to return EXACT row number of a match
It sounds like you want vlookup

#3




Need a function to return EXACT row number of a match
I don't see why an Base+Offset won't fit the bill. You can use
somthing like =ROW(INDIRECT("C"&ROW(C6)+MATCH("bird",C6:C10,0))) to find the row number within C6:C10 on which the word "bird" appears. If it is a matter of defining different lookup regions for a MATCH or V/HLOOKUP function, you might be able to use the INDIRECT function. If cell B1 contains the text string C1120, you can use INDIRECT like =VLOOKUP(123,INDIRECT(B1),2,FALSE) to set the look up range based on the value of B1, in this case setting the lookup range to C1120. INDIRECT isn't limited to a simple onecell indirection as shown above. You can build up any text string using any method you like, pass it to INDIRECT, and get a valid range reference that can be used anywhere a hard coded reference might appear. But to answer your immediate question... No, there is no way to get the row number of some value without resorting to one sort of Base+Offset calculation or another. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 14 Nov 2008 14:44:44 0800 (PST), wrote: Can someone help me with a formula that returns the exact row number (i.e. the row number on the side of the spreadsheet) where a match is found on? If possible, I DON'T want to use 'Match + offset number. For example: A B 480 bull turtle 481 Pig mouse 482 cat dog I would like to use something like =MATCH("Pig",$A$480:$B$482,0). Of course this will return a value of '2' but I want it to return a value of '481'. I need to be able to specify the area to search in because there are other rows that have the value "Pig" in it. TIA 
#4




Need a function to return EXACT row number of a match
On Nov 14, 5:13*pm, Chip Pearson wrote:
I don't see why an Base+Offset won't fit the bill. You can use somthing like =ROW(INDIRECT("C"&ROW(C6)+MATCH("bird",C6:C10,0))) to find the row number within C6:C10 on which the word "bird" appears. If it is a matter of defining different lookup regions for a MATCH or V/HLOOKUP function, you might be able to use the INDIRECT function. If cell B1 contains the text string C1120, you can use INDIRECT like =VLOOKUP(123,INDIRECT(B1),2,FALSE) to set the look up range based on the value of B1, in this case setting the lookup range to C1120. INDIRECT isn't limited to a simple onecell indirection as shown above. You can build up any text string using any method you like, pass it to INDIRECT, and get a valid range reference that can be used anywhere a hard coded reference might appear. But to answer your immediate question... No, there is no way to get the row number of some value without resorting to one sort of Base+Offset calculation or another. Cordially, Chip Pearson Microsoft MVP * * Excel Product Group Pearson Software Consulting, LLCwww.cpearson.com (email on web site) On Fri, 14 Nov 2008 14:44:44 0800 (PST), wrote: Can someone help me with a formula that returns the exact row number (i.e. the row number on the side of the spreadsheet) where a match is found on? *If possible, I DON'T want to use 'Match + offset number. For example: * * * * * * A * * * * *B 480 * * bull * * * turtle 481 * * Pig * * * *mouse 482 * * cat * * * * dog I would like to use something like =MATCH("Pig",$A$480:$B$482,0). *Of course this will return a value of '2' but I want it to return a value of '481'. *I need to be able to specify the area to search in because there are other rows that have the value "Pig" in it. TIA Hide quoted text   Show quoted text  Thanks for the solution! I didn't think to add the starting row number of the range to the match value to get the match row. I actually had to subtract 1 from it to get the correct number. I.E. =ROW($C$482")+MATCH($C11,$C$482:$C$550,0)1 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to get exact match with LOOKUP function?  Excel Worksheet Functions  
Find Exact Match using INDEX, MATCH  Excel Worksheet Functions  
If function to match cols and return value of another col  Excel Discussion (Misc queries)  
match a name and return a tel number?  Excel Discussion (Misc queries)  
MATCH function  2 columns w/ SIMILAR, not EXACT data  Excel Worksheet Functions 