troubleshooting Question

MS Excel - How to Vlookup an Autofilter criteria result

Avatar of billb1057
billb1057Flag for United States of America asked on
Microsoft Excel
15 Comments2 Solutions524 ViewsLast Modified:
In the attached file, there is a function which returns the result of an Autofiltered column.  I am now using that to reference the value and then use a VLookup to find data.  I'm getting #N/A results.
... last minute insight.  Is the problem because the code is returning a string and I'm looking for a number to match in the VLOOKUP?  If so, is there a way to change this in the VLookup?  If not, can you help with a solution?
Many thanks.
Function AutoFilter_Criteria(Header As Range) As String
    Dim strCri1 As String, strCri2 As String
    Application.Volatile
    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)
            If Not .On Then Exit Function
                strCri1 = .Criteria1
            If .Operator = xlAnd Then
                strCri2 = " AND " & .Criteria2
            ElseIf .Operator = xlOr Then
                strCri2 = " OR " & .Criteria2
            End If
        End With
    End With
    AutoFilter_Criteria = Right(strCri1, Len(strCri1) - 1)

End Function

Open in new window

drillDown.xls
ASKER CERTIFIED SOLUTION
Anthony Mellor
Owner

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros