How can I find the row number of a matched value returned by VLOOKUP?

[RogeratCCCC]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> I’m using VLOOKUP to find an exact match in an unsorted column. The VLOOKUP works correctly, and the correct matched value is returned, but I need to know the ROW in which the match occurred, not just the value itself. Is there any way I can do that? Thanks in advance for any suggestions.

 

[mikerickson]

=MATCH("find me", $A:$A,0) will return the row number.

=MATCH("find me",$A10:$A100,0) will return the row index relative to the data range.

 

[MrKowz]

You'll have to use MATCH for this since VLOOKUP does not return a range object. What is your current formula?

 

[RogeratCCCC]

Thanks so much for the quick reply. MATCH works and I am experimenting with it. Maybe you can answer an additional question. What I am trying to do is lookup a value in the leftmost (unsorted) column of a table of rows, then copy the entire row (12 cells) to another worksheet. I was going to use MATCH and INDEX to do the copy. Is there an easier way to copy an entire row when matching the leftmost cell in the row?

Thanks again for any additional suggestions.

 

[MrKowz]

Depending how many rows you want to copy over, this can easily be handled by INDEX/MATCH or VLOOKUP. They won't "copy" the rows over, but will rather display their result on the worksheet. If you want an actual copy where the values will not update, I'd suggest a VBA alternative.