MS Excel - How to Vlookup an Autofilter criteria result

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
LVL 2
billb1057Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

billb1057Author Commented:
... added to this question (it is a 300 pointer after all) ... what is the difference between setting the cell format to Text versus using an apostrophe before entering the text?   And -- how can you change, programattically, between one or the other?
Thanks again.
0
Anthony MellorChartered AccountantCommented:
looking up numbers, if the exact number is not present, it goes to the next one higher and selects the one before that. Somewhat fuzzy.

If you search on text, including numbers that are entered as text, then the search is exact and will return na if not found.

by text I mean entry starts with '
the text format I have never found so useful, not least because it's so hard to change all data entered into it.
Also, data entered before the text format is set, is not subject to it.

prgrammatically: for the ' I use string formulae (that's a sfar as I get with prgs)

anthony
0
billb1057Author Commented:
Thanks.  I was wondering in more specifics how to create a string from an Excel formula.
I wonder if it is related to the "ISTEXT" function.
If I set the cell format to General (and it's numeric) or Number then ISTEXT = FALSE.
If I change the cell format to Text though, still:   ISTEXT = FALSE
If I add an apostrophe before the value, then:  ISTEXT = TRUE
(an added benefit, then the VLOOKUP I asked about works correctly).
So -- ISTEXT recognizes a difference between cell formatted as Text and a String preceeded by an apostrophe.  So, a simpler request:
What function can be used to change between Numeric and String so the text becomes like the apostrophe-Text?
That basically would answer this question -- I didn't ask it correctly because I didn't know why the formula wasn't working, but it is a problem with the String format which the macro returns in the cell.  That won't work as a search term in the VLOOKUP  because the  field I'm looking up is formatted as a number (or non-string) value.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Start This Course
Anthony MellorChartered AccountantCommented:
hi Bill, interesting, that explains why I have had so much grief with the Text format, it has the appearance of text, but isn't.

to change number to text can be as simple as --- I'm just checking  ="'"&A1 where A1 contains a number

be right back, there's also other string changers. just a tic.
0
Anthony MellorChartered AccountantCommented:
of course the cheap way to do it is stick an x in front of every number.. but that's cheating, brb
0
Anthony MellorChartered AccountantCommented:
= TEXT(A1,1) doesn't have the desired ' but is TRUE for ISTEXT
Also CONCATENATE("'",A1) same result
Also T   =T(A1)
And ="'"&A1    this is straight with no formula.
0
Anthony MellorChartered AccountantCommented:
your original question asks:  If so, is there a way to change this in the VLookup?

answer is yes, the above various formulae can be applied to the cell being looked up "on the fly".

so =VLOOKUP(TEXT(A1,1),lookuprange, etc.......

good night, 23:39 here :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
>>Is the problem because the code is returning a string and I'm looking for a number to match in the VLOOKUP?

VLOOKUP will fail when you have a data type mismatch like that.  You need to get the data types in synch, either by modifying the values in the worksheet so that the data types agree, or by using TEXT or VALUE functions to coerce the value being sought into the same data type as in the left-most column of the lookup table.

I discuss this and other issues in my article here:

http://www.experts-exchange.com/articles/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html
0
billb1057Author Commented:
I just increased the point value for a split -- original, full points to Anthony the rest to Matthew.
0
billb1057Author Commented:
Anthony -- that is excellent & it works great.  Thanks!
Matthew -- thanks for a very informative article.
0
billb1057Author Commented:
Ok, I thought I had it but I was too hasty ... could either of you guys give this solution a try in the workbook I posted and then upload the results?
Thanks
0
billb1057Author Commented:
Ok, I got it now.  
TEXT(A1,0) changes the number to Text (like the apostrophe).
Value(A1) changes text that looks like a number to a number
ISTEXT tests for if it is text.
ISNUMBER tests for if it is number
It's working great now -- many thanks again.
0
Anthony MellorChartered AccountantCommented:
:-)
0
Patrick MatthewsCommented:
billb1057,

Glad to help :)

BTW, if you liked that article, please remember to go back to it, and click 'Yes' for the 'Was this helpful' voting.

Patrick
0
billb1057Author Commented:
Good reminder -- I went back and was glad to vote Yes.  I also read it again -- very clear presentation, excellent detail and a very easy to follow text.  Great job.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.