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

Need a function to return EXACT row number of a match
 

It sounds like you want vlookup

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 C11:D20, 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 C11:D20. INDIRECT isn't limited to a
simple one-cell 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

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 C11:D20, 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 C11:D20. INDIRECT isn't limited to a
simple one-cell 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