I have a drop down list generated in Excel from row values. I want to select from that drop down, get the index of that value in the drop down array, then use that for an offset value to select the value from a different column.

I can't seem to find a simple way to get the index of the selected value in the drop down.

Every answer I find makes assumptions about where the array is generated, but here they are generated by different sets of data that can be changed at any time.


you don't need VBA to resolve this.

  1. Define a named range in the workbook for the list you are using for the validation (In my case I have <5, >5 and >10 in cells A2,A3 & A4 for Years of Service, so the named range is called Years_of_service.

  2. Validation is in cell C1 using the defined name range of Years_of_service for the list.

  3. Formula in cell D1 to work out the offset or the item selected in the list is =MATCH(C1,Years_of_service,0) .

Mark O'Rafferty



Try pasting the following VBA into "ThisWorkbook" (If you need help with this, ask in comment):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ValidationIndex As Long
Dim rngTest As Excel.Range

'assumes the data validation is in a cell named "rngTest"
On Error Resume Next
Set rngTest = Sh.Range("rngTest")
If rngTest Is Nothing Then
    Exit Sub
End If
On Error GoTo 0

If Not Intersect(ActiveCell, Sh.Range("rngTest")) Is Nothing Then
    ValidationIndex = GetValidationIndex
    Sheets("Sheet1").Range("E2").Value = ValidationIndex
End If
End Sub

Function GetValidationIndex() As Long
'returns a 1-based index
Dim rngTest As Excel.Range
Dim varValidationString As Variant
Dim ErrNumber As Long
Dim i As Long

With ActiveCell.Validation
    If .Type = xlValidateList Then    '3
        On Error Resume Next
        Set rngTest = ActiveCell.Parent.Range(.Formula1)
        'I do this goofy thing with ErrNumber to keep my indenting and flow pretty
        ErrNumber = Err.Number
        On Error GoTo 0
        'if the Validation is defined as a range
        If ErrNumber = 0 Then
            GetValidationIndex = Application.WorksheetFunction.Match(ActiveCell.Value2, rngTest, 0)
            Exit Function
        'if the validation is defined by comma-separated values
            varValidationString = Split(.Formula1, ",")
            For i = LBound(varValidationString) To UBound(varValidationString)
                If varValidationString(i) = ActiveCell.Value2 Then
                    GetValidationIndex = i + 1
                    Exit Function
                End If
            Next i
        End If
    End If
End With
End Function

A few notes:

  • This assumes that your drop-down box/cell is named "rngTest". If not, then rename it or change it throughout this code to it's existing name.
  • Line 15 defines the exact cell where the index result will output. You'll need to change the sheet and cell names to match your own.

I have tested this and it does work, though I cannot take full credit as the original code was something I found months ago. If you have trouble, it is sometimes easier to replicate this process in a new worksheet until you understand it's theory of operation.

  • Thanks Robert. I actually want to use the index combined with some data and display it in another cell, so I don't think a msgbox will work... sounds like the only option I have is turning this into a combobox? – user339389 Jun 30 '14 at 7:47
  • The code has been updated for you and will now output to a cell that can then be used however you'd like. – Robert Seal Jun 30 '14 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.