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

Thread: Excel VBA Interception and Implicit Intersection and VLookUp

  1. #11
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    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 04:59 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  2. #12
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    [
    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

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
    https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195
    https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-19-2024 at 01:24 PM.

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, 12:32 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, 04:49 PM
  3. Replies: 10
    Last Post: 11-27-2012, 08:27 PM
  4. Replies: 4
    Last Post: 08-14-2012, 03:17 AM
  5. Help with a Vlookup and SUMIF
    By Lucero in forum Excel Help
    Replies: 4
    Last Post: 07-24-2012, 05:03 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
  •