asked on

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

billb1057

ASKER

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

Anthony Mellor

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

billb1057

ASKER

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.

Anthony Mellor

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.

Anthony Mellor

of course the cheap way to do it is stick an x in front of every number.. but that's cheating, brb

Anthony Mellor

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

ASKER CERTIFIED SOLUTION

Anthony Mellor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billb1057

ASKER

I just increased the point value for a split -- original, full points to Anthony the rest to Matthew.

billb1057

ASKER

Anthony -- that is excellent & it works great. Thanks!
Matthew -- thanks for a very informative article.

billb1057

ASKER

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

billb1057

ASKER

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.

Anthony Mellor

:-)

Patrick Matthews

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

billb1057

ASKER

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.