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
    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

Anthony Mellor

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