Need a function to return EXACT row number of a match

[bird_222]

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

 

[dan dungan]

It sounds like you want vlookup

 

[Chip Pearson]

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

 

[bird_222]

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









- 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