Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Excel VBA Interception and Implicit Intersection and VLookUp

  1. #11
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Single value output VLookUp: Used to obtain multiple outputs

    Single value output VLookUp: Used to obtain multiple outputs

    An Example of a Function which normally returns a single value, which can be used to produce multiple values , but which does not follow the usual Trends.

    So, about 5 posts back we ( or I at any rate ) drifted away from the main point that was looking at VLookUp...

    As mentioned , it is a bit of a pain in the bottom as it does not follow the usual trend of things.
    I started looking at this from about here:_...
    https://www.excelforum.com/developme...ml#post4562694
    _... then , with no enlightenment from the Masters, I started looking again from about here:. _...
    https://www.excelforum.com/developme...ml#post4589521

    So where were we.._____ Extending the background work on VLookUp
    I am looking generally at getting multiple values out of a Worksheet formula, in particular for formulas that might not generally be used for or thought of as doing that. I am putting some emphasis finally on the case of VLookUp as it is a bit quirky and does not quite follow the trend of what usually happens.

    In many cases Functions, especially worksheet Functions, which usually take one or more of their arguments as single values will accept in place of those arguments multiple values as range address reference or an Array of values, see here for example:
    http://www.excelforum.com/showthread...95#post4571172

    Often this will result in multiple answers, in place of the single answer for the case of the normal single value arguments case.
    The simplest form and analysis and explanation of this is as follows:
    In most things that Excel does relating to a spreadsheet, or at the occurrence of most events , a process of analysis sets off as a result of which the entire worksheet is updated. It sets off a progression along some effective 2 dimensional grid system , starting at top left, then along the columns, then down a row and repeats again from the left, eventually arriving at bottom right.
    Synchronisation of this to offset memory locations results in what we “see” on our computer screen. http://www.excelfox.com/forum/showth...on-and-VLookUp
    A by Product of this is that when a usually single value argument is given multiple values the evaluation process is effectively perverted such that the evaluation is done at a “time” or point of the progression offset by an amount proportional to the offset in the range or the Array of the member values http://www.eileenslounge.com/viewtopic.php?f=4&t=22534 The end result of that is that Excel has the multiple answers “available” somewhere/ somehow in a matrix or array of dimensions mirroring the maximum dimensions of any Arrays or ranges used in the place of usually single value arguments.

    ==In a Spreadsheet:==
    The process to allow this to happen and “see” the “available” values in a spreadsheet seems fairly well understood involving a mechanism for
    _ somehow doing effectively a Controlled Shift before Enter adjustment adjusting ( CSE Type 1 ) .
    _ In addition, one can effectively place the formula in a range of cells which if chosen appropriately will reveal all the “available” values ( CSE type 2 ). http://www.excelfox.com/forum/showth...alue#post10038 http://www.excelfox.com/forum/showth...0061#post10061

    Without this process it is not always straight forward to get the multi value output results:

    In a famous blog here: https://excelxor.com/2014/09/05/inde...ray-of-values/ a way is shown to get multiple values from a VLookpUp Function without CS Entry

    I am having a look at that a bit here:
    Consider a simple VLookUp Function example use of:
    This could be the “Look Up Table”
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    VLookUp Function is, in the usual single value argument option, then used to return the number 1, 2, or 3, depending on whether you give the function A, B, or C.
    The first single argument then would be, for example A. This is the “Look Up Value”
    The second argument is that Look Up Table ( which is a fixed array always, and not part of our “multi value ( array ) in place of usual single value discussions )
    The third argument simply defines the column number used in the Look Up Table for the output, ( in this case 3 )
    The last argument takes a 0 or False as meaning only accept an exact match to the Look Up value. ( 1 or True allows the nearest match to be accepted )
    ____ = VLOOKUP(LookUpValue,LookUpTable,ColumnNumber,0)
    So this pseudo like thingy would be our example
    ____ 1 = VLookUp(“A”, I22:K24, 3, 0)
    or
    ____ 1 = VLookUp(I22, I22:K24, 3, 0)

    In most cases, as is here, CS Entry of the formula in such a form _..
    VLookUp({“A”; “B”; “C”}, I22:K24, 3, 0) or VLookUp(I22:I24, I22:K24, 3, 0)
    _..will give us an Array of values out in the orientation of the first argument,
    {1; 2; 3}.
    By the way, this, ; , is the Excel convention for “vertical” or “column” orientation so we effectively have this:
    1
    2
    3
    ( Remember I22:K24 is always our fixed Look Up table. This could be replaced with
    { " "A","-","1";"B","-","2";"C","-","3"} , which is effectively “looking” like:
    A - 1
    B - 2
    C - 3 )

    Things are not always so straight forward as they usually are in using CS Entry, and even the best people are not quite sure why.....

    ==“Evaluate” outside the spreadsheet==
    I would argue the distinction between in a spreadsheet and similar things “outside” like in a VBA code is fine as the basic processes are probably the same... But anyway:
    We have 2 other ways of “evaluating” such a formula. They are both, I think, similar in how they work. They both use the same basic processes as in a spreadsheet after hitting Enter, or, I expect, they actually go straight into doing the CS Enter. ( In the spreadsheet you may do the CS Entry in any situation where you would do just Enter. It does no harm. It may involve doing unnecessary extra work. I don’t know )
    _(i) One way - F9:
    If you select a part of a formula or function in the formula bar and hit F9, you will get an “instant” evaluation. – The formula should change to show you a single value or array of values as appropriate for what you “evaluated”
    _(ii) Another way - VBA Evaluate Method:
    In most cases we may use the Evaluate(“ “) function in VBA to do “evaluations” as if we had the thing between the quotes, “ “ in a cell. ( The fact that we have Evaluate(strEval), where strEval is a String is very useful. We can then not only include all the text including formulas and functions that you would type in a cell, but also VBA things. So our formula could be written in a VBA code such
    Dim ColumnIndex As Long
    _Let ColumnIndex = 3
    strEval = "=VLookUp(I22, I22:K24, " & ColumnIndex & ", 0)"


    Once again, same screen shots_..
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    Row\Col
    M
    40
    41
    _...My first problems with VLookUp and Range Evaluate, started here: https://www.mrexcel.com/forum/excel-...e-vlookup.html
    The usual Evaluate(“ “) trends discussed in the previous posts ( using Rept Function as example ) did not seem to work in this case.

    _.....
    So , here we go with VLookUp: Our three similar Excel / VBA Processes
    _.. CSE
    _ .. F9 Formula bar
    _ .. Evaluate(“ “)



    _ .. Reviewing CSE ( Type 2 ) ( Line Numbers for code in over next post )
    Using an arbitrary 3 row x 1 column “Window” selected before _..
    F2 ( or click in formula bar )
    Paste in formula
    CSE

    Result: ... All is well
    Row\Col
    I
    J
    K
    L
    M
    N
    22
    A
    -
    1
    1
    1
    1
    23
    B
    -
    2
    2
    24
    C
    -
    3
    3
    Row\Col
    I
    J
    K
    L
    M
    N
    22
    A
    -
    1
    =VLOOKUP({"A","B","C"},I22:K24,3,0)
    =VLOOKUP(I22:I24,I22:K24,3,0)
    =VLOOKUP(I22:I24, I22:K24, 3, 0)
    23
    B
    -
    2
    =VLOOKUP(I22:I24, I22:K24, 3, 0)
    24
    C
    -
    3
    =VLOOKUP(I22:I24, I22:K24, 3, 0)

    ( Note in the above screenshot Implicit Intersection is responsible for the effective Formula in H22 of =VLOOKUP({I22,I22:K24,3,0) , that is to say =VLOOKUP({"A"},I22:K24,3,0) )
    So, so far so good.. In the next two variations of the similar processes _ ..
    _ .. F9 Internal Anomalies.
    _ .. Case VLookUp, ( Evaluate(“ “)

    _ .. we see problems, / anomalies to the trends so far discussed: In the next post we will see that the internal F9 type evaluation is more difficult to bring about. That is to say removal of the Excuse for an abortion in the excepted normal Interception intercourse is not following the accepted practices discussed thus far for normal Fuktions.. ¯\(ツ)/¯ ʅ_(ツ)_ʃ


    http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10172#post10172
    Last edited by DocAElstein; 03-15-2017 at 05:29 AM.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  2. #12
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Digressions From Excepted Interception Intercourse in removing Excuse for Abortion

    [
    Digressions From Excepted Interception Intercourse in removing Excuse for Abortion
    _ .. “F9 Internal” Anomalies.
    _ .. Case VLookUp, Evaluate(“ “)


    For the case of our simple example, REPT(I22:I23,2), we found that F9 in formula gave us {"AA";"BB"}, even for the case of the formula erroring when in a non Implicit Intersection cell. Appling F9 to the I22:I23 function section returns {"A";"B"}

    For the case of =VLOOKUP(I22:I24,I22:K24,3,0) applying F9 to I22:I24 returns {"A";"B";"C"}, but applying to the entire =VLOOKUP(I22:I24,I22:K24,3,0) we get an error

    Looking in Evaluate(“=VLOOKUP(I22:I24,I22:K24,3,0)”) returns us, as possibly expected, just the first value, 1.
    As might also be expected, ( by virtue of F9 on =VLOOKUP(I22:I24,I22:K24,3,0) in the formula bar not working ), we find that typical “to get Multivalve Wonks “ 110 120 do not work, simply returning us {1}.

    150 160 Very interestingly, we do not kill Interception.

    Is this all telling us that VLookUp is not , for some strange reason , doing the preliminary Excuse for an Abortion. God only knows why,
    it could be anything – For example ,
    How about :
    A hold on the second argument as fixed Matrix Look Up Table may have inadvertently been applied to the first argument??
    This would tie up with the next few lines in particular that this sort of thing
    180 __ =If(Row(),VLOOKUP({""A"";""B"";""C""},I22:K24,3,0) )
    _.. is not working, either in the code, as above, or, in its spreadsheet version of this
    _ VLOOKUP({"A";"B";"C"},I22:K24,3,0)
    ( The latter returns 1 rather than an error in the spreadsheet as Interception is apparently not working, the former returns {1} as String in a (1 To 1) Variant type Array, as we do array analysis , but on a simple constant of 1 )

    So it appears we do not have an Array at that first argument, resulting in VLookUp returning a single simple value.
    So we now investigate how to “force” an Array in there.
    Whatever trick is needed here, it is not to force multivalve analysis , as this ( as we already saw ) , will simple change the single value to be returned, ( all be it in a (1 To 1 ) Array )

    ' I know an answer, as I have it from here: https://excelxor.com/2014/09/05/inde...ray-of-values/ . The answer appears to have no known logic or understanding. It is currently more or less the only known way to achieve what is likely to be achieving making the second argument, in this case {"A";"B";"C"} , actually look at that point as {"A";"B";"C"} .

    The trick is T(IF(1, {“A”;”B”;”C”}))

    The “trick” turns back on Interception resulting in an error in the substrate without CSE
    Formula: VLookUpFormula.JPG http://imgur.com/sXKOOEu http://imgur.com/a/fK4DY
    =VLOOKUP(T(IF(1,{"A";"B";"C"})),I22:K24,3,0)
    Value returned in cell: VLookUpInCellInterceptionError.JPG http://imgur.com/aur3Ez7 http://i.imgur.com/aur3Ez7.jpg
    #WERT!
    The result after highlighting the formula in the Formula Bar and hitting F9 is
    VLookUpF9FormulaBar.JPG http://imgur.com/MAuIwbZ http://i.imgur.com/MAuIwbZ.jpg
    ={1;2;3}
    =
    {1;2;3}


    and the more usual results in evaluate(“ “), of a single value , 240 , or with a typical recursion , 250 , the final full values

    ' I do not know why the trick, The trick is T(IF(1, {“A”;”B”;”C”})) , works or what it does , ( yet https://excelxor.com/2014/09/05/inde.../#comment-2493 )
    ' I expect nobody does


    Code:
    '10 '
    '20   ' Trying to understand XLORX advanced "coercing" of INDEX and VLOOKUP to to coerce a return of more than one value from the array passed to it
    '30   ' https://www.mrexcel.com/forum/excel-questions/806702-visual-basic-applications-evaluate-range-vlookup-3.html
    '40   ' https://excelxor.com/2014/09/05/index-returning-an-array-of-values/
    Sub RangeEvaluteVLookUp() '   https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-14.html#post4603375
    50    Worksheets("XLORX").Activate ' Make XLORX demo Worksheet active as code refers to active Worksheet
    60   Dim vTemp As Variant ' For anything when not sure
    70   Dim arrEval() As Variant ' For returned Field ( Array ) of Variant types.
    80    Let Range("N22:N24").FormulaArray = "=VLookUp(I22:I24, I22:K24, 3, 0)"                  ' CSE type 2 , no problem
    90 '
    100   Let vTemp = Evaluate("=VLOOKUP(I22:I24,I22:K24,3,0)")                                   ' Returns 1   Double in Variant
    110   Let vTemp = Evaluate("=Index(VLOOKUP(I22:I24,I22:K24,3,0),0,0)")                        ' Returns {1} Double in Variant
    120   Let vTemp = Evaluate("=If(Row(),VLOOKUP(I22:I24,I22:K24,3,0))")                         ' Returns {1} Double in Variant
    130   Let vTemp = Evaluate("={""B"";""C""}" & "&" & "{1}")                                    ' Returns {"B1";"C1"} Strings inn Variant
    140   Let vTemp = Evaluate("={""B"";""C""}" & "&" & 1 & "")                                   ' Returns {"B1";"C1"}
    150   Let vTemp = Evaluate("={""B"";""C""}" & "&" & "VLOOKUP(I22:I24,I22:K24,3,0)")           ' Does not kill Interception.  :confused:  ' Returns {"B1";"C1"}
    160   Let vTemp = Evaluate("={""B"";""C""}" & "&" & "If(Row(),VLOOKUP(I22:I24,I22:K24,3,0))") ' Does not kill Interception.  :confused:  ' Returns {"B1";"C1"}
    170   Let vTemp = Evaluate("=VLOOKUP({""A"";""B"";""C""},I22:K24,3,0)")                       ' Returns 1   Double in Variant
    180   Let vTemp = Evaluate("=If(Row(),VLOOKUP({""A"";""B"";""C""},I22:K24,3,0))")             ' Returns {1} Double in Variant
    190   Let vTemp = Evaluate("={""B"";""C""}" & "&" & "If(Row(),VLOOKUP({""A"";""B"";""C""},I22:K24,3,0))") ' ' Returns {"B1";"C1"} Strings inn Variant
    200   Let vTemp = Evaluate("={""B"";""C""}" & "&" & "If(Row(),VLOOKUP({""A"";""B"";""C"";""D"";""E""},I22:K24,3,0))") ' ' Returns {"B1";"C1"} Strings inn Variant
    210 '
    220  ' I know an answer, T(IF(1, {"A";"B";"C"})) , as I have it from here: https://excelxor.com/2014/09/05/index-returning-an-array-of-values/     . The answer appears to have no known logic or understanding. It is currently more or less the only known way to achieve what is likely to be achieving making the second argument, in this case {"A";"B";"C"} , actually look at that point as {"A";"B";"C"} . The "trick" turns back on Interception resulting in an error in the substrate without CSE and the more usual  results inn evaluate(" ")
    230
    240   Let vTemp = Evaluate("=VLOOKUP(T(IF(1,{""A"";""B"";""C""})),I22:K24,3,0)")              ' Returns 1   Double in Variant
    250   Let vTemp = Evaluate("=If(Row(),VLOOKUP(T(IF(1,{""A"";""B"";""C""})),I22:K24,3,0))")    ' Returns {1;2;3} Doubles in Variants
    260 '
    270  ' I do not know why T(IF(1, {"A";"B";"C"})) works or what it does  , ( yet    https://excelxor.com/2014/09/05/index-returning-an-array-of-values/#comment-2493    )
    280 '
    290 '
    End Sub

    ' I do not know why the trick, The trick is T(IF(1, {“A”;”B”;”C”})) , works or what it does , ( yet https://excelxor.com/2014/09/05/inde.../#comment-2493 )
    ' I expect nobody does '_- ¯\(?)/¯?_(?)_? ¯\(ツ)/¯ʅ_(ツ)_ʃ



    https://www.excelforum.com/tips-and-...ml#post4605327
    https://www.excelforum.com/developme...ml#post4603535
    http://www.excelfox.com/forum/showth...0172#post10172

    Last edited by DocAElstein; 03-15-2017 at 06:18 AM.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

Similar Threads

  1. Intersection of Overlapping Ranges:Space Operator
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 01:02 AM
  2. Vlookup Multiple Values By Adding Formula With Loop In VBA
    By Safal Shrestha in forum Excel Help
    Replies: 15
    Last Post: 04-22-2013, 05:19 PM
  3. Replies: 10
    Last Post: 11-27-2012, 08:57 PM
  4. Replies: 4
    Last Post: 08-14-2012, 03:47 AM
  5. Help with a Vlookup and SUMIF
    By Lucero in forum Excel Help
    Replies: 4
    Last Post: 07-24-2012, 05:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •