CODEWITHSUNDEEP
vbaexcel
Tornado168
Tornado168

Reputation: 465

vba - Get row number of a cell with certain value in a named range

Is there a way to find the row of a cell containing certain value in a named range (Table)?

I have a table named "Table1" from C4 to D10.. I want to return the row number of a cell in column c that contains the value "CCC".. This value is found at the third row (C6).. So I want the code to return the number "3" which means the third row in the table "Table1" and not the number "6" that means it's found in cell "C6".

Thank you in advance.

Upvotes: 1

Views: 7036

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

Edit:

I missed the part about "relative to the table" but that's even easier...

Here's one that will work:

=MATCH("CCC",Table1,0)

or, referring to the cell range instead of the table directly:

=MATCH("CCC",C4:C10,0)

Original Answer:

If you want to know the row number of the first cell in Column C that contains CCC, you could use the MATCH function, either in a worksheet formula, or in VBA.

On the worksheet:

=MATCH("CCC",C:C,0)

or, in VBA:

WorksheetFunction.Match("CCC",Range("C:C"),0)

The fact that it's in a table is irrelevant since you've identified the column.

Incidentally, I can think of at least half a dozen other ways to get the same data just as easily.

Here's one that refers to the table directly:

=ROW(INDEX(Table1,MATCH("CCC",Table1,0)))

...and more variations:

=MATCH("CCC",C4:C10,0)-1+ROW(C4)

or 

=MATCH("CCC",Table1,0)-1+ROW(Table1)

Note that a big difference between MATCH and VLOOKUP is that MATCH returns a range object, whereas VLOOKUP only returns the value of the matched cell, and therefore isn't suitable for a task like this.

More Information:

Upvotes: 3

Related Questions