Obtaining the Index Number of a Drop-Down List

Example

Sub test()
    Dim rng As Range, r As Range, s As String, x, temp, msg As String
    On Error Resume Next
    Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    If rng Is Nothing Then MsgBox "No cell has Validation": Exit Sub
    For Each r In rng
        If r.Validation.Type = 3 Then
            s = r.Validation.Formula1
            If s Like "=*" Then
                x = Evaluate(Mid(s, 2) & "&""""")
            Else
                x = Split(s, ",")
            End If
            temp = Application.Match(CStr(r), x, 0)
            If Not IsError(temp) Then msg = msg & vbLf & r.Address(0, 0) & vbTab & temp
        End If
    Next
    If Len(msg) Then MsgBox "Cell" & vbTab & "Index" & msg
End Sub

Thank you very much for your example jindon. As I'm still not too familiar with all the various objects you've included in your code, I'll need to do some research to understand your example. I'll get back to you should I have any further questions.

In the meanwhile, I am still open for other solutions that may solve my issue. I've also edited the original post to include some further context and welcome any other means of carrying it out.