Get Row Number from Vlookup ?

phxsportz · May 19, 2009

I know this is probaly a no brainer (for those with a brain-heh), but is there a way to get the row number of a specific item, perhaps using vlookup ?

example -- Cells A1, A2 and A3 contain the following data respectively

DOG
CAT
MOUSE

I need a function that will tell me that CAT is in Row 2

Special-K99 · May 19, 2009

=match("cat",a1:a3,0)

phxsportz · May 19, 2009

Thanks... I knew it had to be something tragically simple.. heh

Cbrine · May 19, 2009

=MATCH("CAT",C1:C5,FALSE)

You can replace the "CAT" with an address.

PS-If your range of lookup data starts on row 2, this will return 1 as the value, so if you are looking for the row number, always start the range on row 1(ie...C1:???, not C2:???)
HTH
Cal

phxsportz · May 19, 2009

Hmm... tells me Sub or Function not defined, is this another one of those Add Ins that I need to activate ?

PS.. Using Excel 2K3

phxsportz · May 19, 2009

PPS I need this in VB, sorry...

phxsportz · May 19, 2009

oops.. Got it.. thanks for the help guys...

Solution....

Code:

      rcd_num = WorksheetFunction.Match(tb_Liqname, Liquor_List, False) + 1

Cbrine · May 19, 2009

Not sure of the overhead using the worksheetfunction option, but here's another alternative using the find method.

Code:

Dim found As Range

Set found = ActiveSheet.Range("C2:C5").Find("DOG")
If Not found Is Nothing Then
    MsgBox found.Row
End If

HTH
Cal

Jonmo1 · May 19, 2009

=MATCH("CAT",C1:C5,FALSE)

The reason this errored is because...
The 3rd argument of Match is NOT a True/False option.
It has 3 options

-1 = Find Smallest Value that is Greater Than or Equal To the Lookup value - Sort Data DESCENDING
0 = Find EXACT match - Data does not need to be sorted
1 = Find Largest Value that is Less Than or Equal To the Lookup value - Sort Data ASCENDING

watsonla · Sep 3, 2011

I have been reading this thread for some help finding a row with in a list of data.

the help given on this thread suggests two options.

 =MATCH("CAT",C1:C5,FALSE)

[FONT=&quot]Dim found As Range[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Set found = ActiveSheet.Range("C2:C5").Find("DOG")[/FONT]
[FONT=&quot]If Not found Is Nothing Then[/FONT]
[FONT=&quot] MsgBox found.Row[/FONT]
[FONT=&quot]End If[/FONT]

Both of these produce a number of a row.

I would like to know how to change the "cat" or "dog" to whatever is in a specific cell... e.g. if cell c3 had "123" how can i search for the contents of c3 when the data in c3 varies.

Once the row is known, is it possible for the cursor to go to that row. eg if "123" was in row A500 can the cursor go directly to that row and scroll the spreadsheet so A500 is in the current view?

Very greatful for any help.

Get Row Number from Vlookup ?

phxsportz

Well-known Member

Excel Facts

Special-K99

Well-known Member

phxsportz

Well-known Member

Cbrine

Well-known Member

phxsportz

Well-known Member

phxsportz

Well-known Member

phxsportz

Well-known Member

Cbrine

Well-known Member

Jonmo1

MrExcel MVP

watsonla

New Member

Similar threads

Forum statistics

Share this page

Get Row Number from Vlookup ?

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock