Return row number of a specific value

To return a row number of a specific value we need to apply the Excel MATCH function

Example: Return row number of a specific value

METHOD 1. Return row number of a specific value

EXCEL

=MATCH(D5,B:B,0)

The formula uses the Excel MATCH function to return the row number in column B that captures the value of "Cereal". The formula will only return the row of the first occurrence of the specific value.

METHOD 1. Return row number of a specific value using VBA

VBA

Sub Return_row_number_of_a_specific_value()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'apply the formula to return the row number of a specific value held in cell (D5) in column B
ws.Range("E5").Value = Application.WorksheetFunction.Match(ws.Range("D5"), ws.Range("B:B"), 0)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Specific Value: Have the specific value that you are searching for in cell ("D5").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Column Reference: Select the column(s) that you are searching through for the specific value by changing the column reference ("B:B") in the VBA code to any column in the worksheet, that doesn't conflict with the formula.
Specific Value: Select the specific value that you are searching for by changing the cell reference ("D5") in the VBA code to any cell that contains the specific value that you wan to search for in the Analysis worksheet that doesn't conflict with the formula. You can also change the value that is held in cell ("D5") to any specific value that you are looking to return the row number for.

Explanation about the formula used to return row number of a specific value

EXPLANATION

EXPLANATION
To return a row number of a specific value we need to apply the Excel MATCH function.

FORMULAS
=MATCH(lookup_value, column_ref, 0)

ARGUMENTS
lookup_value: The value that you intend to search for and return the row number.
column_ref: The column from which to lookup the specific value.

ADDITIONAL NOTES
Note 1: Given the formula uses the Excel MATCH function, the specific value is case-sensitive.
Note 2: If the formula cannot locate the specified value from the selected range it will return an #NA error.