How can I find the index of a row for a value in excel É

Question

Hi ,

is there a function in excel which can find the index of the row for a particular value É

ie if I write function(5.77) it will return 5 , because 5.77 appears in row 5 .

'1 Frequency '2 4.14 '3 4.19 '4 5.17 '5 5.77 '6 6.39

Answer 1

The function is called MATCH. It doesn't return the row number, but it returns the index of the cell inside the range, you can add the base row number to that to get the actual row number.

PS. This is an excel function used in cell formulas, not an excel-vba function...

Answer 2

You can use the MATCH() worksheet function. In order to use this in VBA, you need to use the WorksheetFunction object, which exposes a subset of the Excel worksheet functions. E.g.

Dim rowIndex as Long
rowIndex = WorksheetFunction.Match(5.77, Range(A1:A6))