[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

MS Excel - How to Vlookup an Autofilter criteria result

Posted on 2010-03-31
15
Medium Priority
?
494 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:billb1057
  • 7
  • 6
  • 2
15 Comments
 
LVL 2

Author Comment

by:billb1057
ID: 29220101
... 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
 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 29243654
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
 
LVL 2

Author Comment

by:billb1057
ID: 29258597
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 29265469
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
 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 29265645
of course the cheap way to do it is stick an x in front of every number.. but that's cheating, brb
0
 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 29266535
= 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
 
LVL 9

Accepted Solution

by:
Anthony Mellor earned 1200 total points
ID: 29267246
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
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 29299546
>>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
 
LVL 2

Author Comment

by:billb1057
ID: 29334375
I just increased the point value for a split -- original, full points to Anthony the rest to Matthew.
0
 
LVL 2

Author Comment

by:billb1057
ID: 29334586
Anthony -- that is excellent & it works great.  Thanks!
Matthew -- thanks for a very informative article.
0
 
LVL 2

Author Comment

by:billb1057
ID: 29335841
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
 
LVL 2

Author Comment

by:billb1057
ID: 29336603
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
 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 29340782
:-)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 29454513
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
 
LVL 2

Author Comment

by:billb1057
ID: 29455392
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question