Results 1 to 4 of 4

Thread: Excel VBA Interception and Implicit Intersection and VLookUp

  1. #1
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3

    Excel VBA Interception and Implicit Intersection and VLookUp

    What is Implicit Intersection
    As far as I know there is no official documentation on this and blogs on it are sparse.
    The phrase Implicit Intersection is generally used to explain when a formula does what might not initially be expected, ( in most cases this means that it surprisingly “works” in certain cells in an Excel spreadsheet! ) .

    What is Excel VBA Interception
    I made that title up myself.
    It is a theory or idea I have about how Excel works which explains the results in an Excel Spreadsheet which are covered often by the phrase Implicit Interception. It also helps explain and predict some other interesting things in VBA. My suggestion is that what is meant is a by product of Excel VBA Interception. Excel VBA Interception is an integral part of the basic way that Excel works. The by product of Implicit Intersection results from an attempt to “hold” cell values in a way to help speed up calculations in Excel and VBA.
    I tend to view in my theory Excel spreadsheet operation and VBA as the same. That view point is based on the idea of that much of what happens in a spreadsheet is based on pre written VBA type coding.
    _This post offers some theory, or a model, that can explain the Interception Idea.
    _The following post shows how this can explain what is typically referred to as Implicit Intersection, ( I might argue this is a specific Implicit Intersection type)
    _The third and final post attempts to show a working example

    Excel is a cell, mostly. ( What I see and how).
    Of course that is an over simplification, but most often that is what anyone would think about when talking about Excel. Most VBA codes are designed to change things in cells in a spreadsheet. Using them and their contents is fundamental then to Excel and VBA. I am thinking that Excel only has a model of one cell that includes all the stuff of a cell. That information is stored in a massive thing ( http://www.excelforum.com/showthread...11#post4551080 ) called a Range object.
    A spreadsheet is made to look like lots of cells on our screen by the software holding similar information as in the one cell at a specific offsets in memory locations. Those offsets are somehow synchronised to the rate at which the information is put on the screen. So we are fooled into thinking we have a sheet with cells spread out across our screen. This last point is important. In my explanations I will often talk about extra help matrix holding values. In reality they will not actually be that. They will be some mathematical formula that actually optimises how the information is held. The same is done with the formulas used to produce the spreadsheet that we see: They are somehow optimised, for example, such that empty cells do not exist. Otherwise our Excel File would always be enormous even with nothing in it Just as we “visualise” the spreadsheet, I am visualising other matrixes that I discuss. I think my imaginary matrixes would be sort of technically where you effectively get to by the “dependency tree”, which again in the final “thing” is a formula based on certain “offset type coordinates”

    Excel has been written such that when a “change” is made “anywhere” in the spreadsheet, then all cells are “updated”. ( That is one reason you often here that VBA codes that “do things” to a worksheet can be slow as every interaction “slams the brakes on” whilst the whole worksheet is updated. Often in a code you will see Screen Updating turned off until the end of the code when all the updating is then done in one go rather than at every interaction. All the “VBA Array stuff” is about is like, for example, making an internal copy matrix of cell values, doing any processing you want to on them internally, then “pasting them out” to the worksheet in one go. You can do that all in one go stuff efficiently as you just like use the same single offset value for all the values to put them in the place for those things in the referenced Range object ). With this in mind, I expect some attempt at making Excel efficient in doing that may have been made. I think the effect observed and attributed to “Explicit intersection” could be a by product from that.

    Referring to cells and groups of cells areas. Offsets and Resize to “full area”
    Excel’s way of organising and “getting at” the cells and constructing Range objects
    Cells can be organised into groups of cells. These are organised in exactly the same form as a single cell. In other words a Range object can be made up of groups of cells or Range objects. Whether one imagines one “sees” one or more cells is all based on some offset or rather sets of offsets.
    Imagine in the screenshot below that the yellow is the entire spreadsheet. That itself could be a Range object, ( indeed in VBA we have a Property, .Cells , of a worksheet, which when applied to a Worksheet object returns a Range object containing all the cells in a worksheet ).
    “Getting to”, “seeing”, “returning some Property, (value” ).. etc.. is all based on a “offset referencing coordinate system”. This has two basic forms.
    _ a simple ( Up/down , left/right ) to “get to any point going in any direction within the Worksheet”, and
    _ a similar but restricted to from top left, to bottom right to define a full rectangular area
    Using such a system it is possible to “get to” or “construct to see” any combination of cells in a worksheet. So in the screenshot below the Red and Blue areas could be organised into a single Range object. In other words a Range object is organised into groups of contiguous cells. ( These are called Areas ). It can of course be just one Area or even a single cell. ( http://www.excelforum.com/showthread...11#post4551484 http://www.excelforum.com/showthread...154829&page=13 )
    Row\Col
    A
    B
    C
    D
    E
    1
    2
    3
    4
    5
    Based on the last section we have then a system based on going +ve and –ve in two directions to “get there” , then once there we go from a top left to a bottom right to make a “seen rectangular area of contiguous (contiguous means here all there, no spaces ) cells”. ( It comes then maybe as no surprise that in VBA Programming one finds the parallel concepts of a Offset and Resize Property. Furthermore one finds these a very efficient way to do things directly rather than adding an ““in between step” for simplicity of understanding” such as including different variable for different Range objects. )
    See here http://www.excelforum.com/showthread...154829&page=13 for the parallel VBA concepts_..
    _ going +ve and –ve in two directions –--- Range.Item.Property , and
    _ from a top left to a bottom right to make an area ---- Range(“ “) Property.

    Intercepts. ( after Enter in a cell with a formula )
    Everything that we visualise is going to be realised by a Offset to the “one Excel cell”. We see a cell as a result of some synchronisation to a continuum whose “refresh” or “update” is based on going from top left to right, then next row, from left to right, etc. finally ending up at bottom right . It makes sense to use this “positional information” in some efficient way. My idea, which in the reality is realised by a complex mathematical calculation, can be thought of in a similar way to as we imagine a spreadsheet as a matrix. Any single cell reference used in any formula, that is to say appearing in a string after a = , will present a matrix which is “revealed” as the value “seen” by the window which in this case is the single cell.
    The screen shot below is intended to represent 2 different update runs in a worksheet, ( not necessarily the same worksheet ). Imagine that yellows are the cell which we might see in a snapshot in time as a spreadsheet has its displayed values updated. Imagine the Values are in my imaginary help matrix which I am suggesting is created for any single cell reference.
    A1 A1 A1 A2 A2 A2
    A1 A1 A1 A2 A2 A2
    A1 A1 A1 A2 A2 A2
    My idea of how Excel works is to display what is at the intercept of the Yellow and the help matrix. If you type in =A1 or =A2 in any cell it will ”work”. I expect this is very efficient. I think implicit intersection is also a good phrase for what is going on.

    I am thinking that if I have a reference to any 2 dimensional range area, such as A1:B2, the help matrix is created differently ( or in the reality a different mathematical process is going on which can be visualised as such a matrix)
    A1 B1
    A2 B2
    My idea of how Excel works is to display what is at the intercept of the Yellow and the help matrix. I would expect this intercept idea to cause some sort of error in this case as there is nothing to intercept. Indeed if you try anything of the form =A1:B2 in any cell then it will error.

    Controlled Shift before Enter in a cell with a formula
    To still get my basic idea to work you would somehow need to add some additional action to effectively do some effective Shifting of the yellow so that the correct cell in the help matrix is Covered before doing the same intercepting process. This would need to be repeated for as many effective single cell references in the are represented by the 2 dimensional range area referenced. We can now understand possibly why our cells and Range objects are organised such that a Range object construction is similar for single and multi cell areas. The same process used in updating an entire worksheet can be utilised to carry out this multi cell reference evaluation. In doing so it is probably a good guess to say that the returned values will start in origin which in this case is the yellow cell and will extend in to the right and down directions from that origin. This is available to us. As it is somewhat inefficient, ( doing a sort of 2 step Shift along and vertical ), this is not the default that would be implied and applied up until now after the use of Enter. The keys chosen as an alternative are correspondingly Control ( Ctrl ) + Shift + Enter. Such an Entry is known as
    CSE type 1 Entry.
    You will find writing in a references such as = A1:B2 as follows “works” ( does not error) if you do this
    _a)_ Select any cell in the worksheet ( other than in the range ( A1:B2 )
    _b)_Hit F2 or click in the formula bar ( or don’t bother )
    _c)_Type in =A1:B2
    _d)_ Hold down Ctrl + Shift , then hit Enter ( or hit simultaneously Ctrl+Shift+Enter)

    CSE type 2 Entry
    If you try the CSE type 1 Entry with a range of values in cells A1:B2 you will notice that only the value for top left ( A1 ) is displayed. As noted the values are effectively there offset to the right and down. The yellow cell displaced as a snapshot in the Worksheet updating process will correspondingly be at the top left of the range returned by the evaluation. It is a reasonable assumption that the intercept “partner” of the defined cell where the formula is needs to be extended to cover that range. So simply repeat the CSE type 1 entry procedure, but at step _a)_ select any 2x2 range in the worksheet ( other than one containing any cell in A1:B2 ) . This is known as CSE type 2 Entry.
    After carrying this out, if you select any of the cells in which you CS Entered the reference, the following will be shown in the formula bar
    ={A1:B2}
    The { } is just the spreadsheet convention for an Array of values.
    You can repeat the exercises above with ={1, 2; 3, 4} in a cell in place of a reference. The results are similar but will not error without a CS Entry. This is because no Controlled Shift is required to obtain that Array of values. The process here is the multi evaluation which Excel is set to do in general when an Array is in any formula. The CS Entry was previously required to return that Array. The CS Entry is required here only for display of all values.
    As a last note of interest: Excel and VBA basically use the same processes: In VBA there is Function
    Evaluate(strEval)
    This basically allows you to put a string, strEval, such as this form “=A1:B2”, into the argument and basically what happens is that done by for that =A1:A2 in the cell. There is no requirement for any CSE action: The Function returns the Array directly. As it is not an entire spreadsheet updating action, the intercept efficiency thing is not needed
    This basic process can also be used from within the worksheet as follows:
    Type any reference such as =A1:B2 in any cell. Select ( Highlight ) the display it in the formula bar. (The reference can be entered by Enter or by CS Entry ) . Hit F9. This calls into play the basic Excel evaluation procedure and the results are shown. Important: Hit Esc or Ctrl+z to return to formula view.
    Last edited by DocAElstein; 02-09-2017 at 04:49 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. #2
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3

    Excel VBA Interception and Implicit Intersection and single Breadth Arrays

    Excel VBA Interception and Implicit Intersection and single Breadth Arrays


    If the last post is understood, then what is often considered as Implicit Intersection can be explained as a by product of what Excel does in terms of my imaginary help matrix, when we consider a similar idea for the case of a reference of a particular form. It is the sort of “middle case”:
    To refresh: In the last post we looked at the two “extreme cases” : That of
    _ a multiple 2 dimensional range, like = A1:G5, or
    _ a single cell reference, like =C1.

    We now consider the case of a single “breadth” range. By single “breadth”, I am talking about a range like A1:C1 or A1:A2 etc.
    We considered already for the case of a 2 Dimension range that we could visualise a full range help matix
    A1 B1
    A2 B2
    We said that effectively a Shift was needed in “horizontal” and vertical directions before the effective “revealing” of the cell “snap shotted in time” could be done. The imaginary help matrix, I suggested, was empty elsewhere, so nothing was revealed by a simple = in a cell. We were imagining that the = “made visible” the help matrix at the same point as that of the cell when “seen” by a screen update for that cell.

    The idea for the single reference case was that the help matrix had the single value from the referenced cell in all its “cells”.
    B2 B2 B2 B2
    B2 B2 B2 B2
    This made the intersect very fast.


    My guess is that the corresponding imaginary help matrix for a single “breadth” range such as =A1:A2 could look like somewhere between the two, like this:
    Help Row\Help Col
    A
    B
    C
    D
    1
    A1 A1 A1 A1
    2
    A2 A2 A2 A2
    3
    ( A1 is the value in cell A1, A2 is the value in cell A2 from the spreadsheet we “see”. The above screenshot is my imaginary help matrix. Yellow is the position in the spreadsheet cell for a cell containing the reference, in this case =A1:A2)

    It may be obvious the situation here. The CS Entry will work just as in the case of the 2 dimensional Array case, and Excel will with the returned array, follow its normal multiple evaluation , ( but going just along or just down to select the values and place in a return array for output. ) So we effectively only have to Control a Shift in one direction
    Possibly at this stage, as an aside, stepping back and adding some clarity to what is going on with a simple example is helpful here:
    Any range reference in a cell ( we are just considering one here, =A1:A2 ) , will as a result of a CS Entry have an array returned mirroring in dimensions and size the range referred to, but having the values from that range in it. The complete formula will then be evaluated talking in turn the single values from the corresponding position on any array ( only one this case of the simple formula of a single reference, =A1:A2 ) , and the total results collected in a similar size and dimensioned array.
    For example, consider the array {1, 2 } and { 3, 4 } as those which are returned from two references in a formula in a cell of = B1:C1 * C2:D2
    Excel will return finally { 3, 8 }
    Graphically consider some values in the first 2 rows of a spreadsheet, then if I do a type 2 CSE entry of that formula in, say in the range A3:B3, this is the sort of results I will have in the spreadsheet:
    Values:
    Row\Col
    A
    B
    C
    D
    1
    1
    2
    2
    3
    4
    3
    3
    8
    Formulas:
    Row\Col
    A
    B
    3
    {=B1:C1*C2:D2}
    {=B1:C1*C2:D2}
    Excel can be thought as initially returning the arrays, {1, 2 } from B1:C1 and { 3, 4 } from C2:D2, as instructed by the CS Entry, and then finally it does these evaluations
    ______ 1x3 __ , __ 2x4 ( columns then next row, - in this case just 1 row )

    Coming back to the example in the original screenshot:
    If we do no CS Entry , the default process comes in which effectively imagineatary “reveals” the help matrix.
    It will not give us a value in every cell in the worksheet. But as can be seen in the first screen shot we will get in the yellow cell the value from cell A2. ( remember the formula under consideration in that yellow cell would be =A1:A2 written in the spreadsheet a t the corresponding position in the imaginary spreadsheet on out screen ).
    If the formula were CS Entered, the value would be A1 in that cell, ( or in any cell other than A1 or A2 anywhere in the spreadsheet).
    In a cell in row 1 we would get A1 with or without CSE.
    For any row other than row 1 and row 2 we would get an error without CSE
    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/

  3. #3
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3

    Example: VLookUp and Intercept and Implicit Intersection for single breadth ranges

    Example: VLookUp and Intercept and Implicit Intersection for single breadth ranges
    .

    I am not too convinced that this is such a good example as I cannot see any advantages. But possibly I am wrong. If it does have, for example, speed advantages over a normal formula then some of my theories are probably out of wach.
    But as this is often given as an example, I will demo it. Any comments or any better examples of using a formula such that it uses Implicit Intersection for single “breadth” ranges would be very welcome.

    I will not go into great detail of the basic VLookUp formula and typical forms in this post. ( I have done that in the next post for revision and reference ) . In this post I will start with three columns holding 3 typical versions of the formula working on the same Look Up values and Look Up Tables, and then add a forth, the often given Implicit Intersection version.

    So in the Window below are the first of a 7 row VLookUp formula in 3 versions in columns B C and D. Column E will then be considered for the Implicit Intersection version.
    Code:
    First row formula
     
    Row\Col
    B
    3
    =VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    Row\Col
    C
    3
    {=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)}
    Row\Col
    D
    3
    =VLOOKUP({"Chocolate-europe aroma";"Chocolate-Cookies";"Banana-Chocolate-Split";"Limette-Käsekuchen";"Erdbeere-Quark";"Erdbeere-Mix";"Jamaica Sun";"Waldbeeren"},$A$16:$C$33,3,FALSE)
    Look Up values and returned formula Values seen in cells
    Row\Col
    A
    B
    C
    D
    2
    Given Look Up Value
    Simple Formula. First argument single Look Up value. Draged down
    Column range reference first argument - Type 2 CS Entry
    First argument array. Type 2 CS Entry
    3
    Chocolate-europe aroma
    4
    4
    4
    4
    Chocolate-Cookies
    0
    0
    0
    5
    Banana-Chocolate-Split
    10
    10
    10
    6
    Limette-Käsekuchen
    16
    16
    16
    7
    Erdbeere-Quark
    8
    8
    8
    8
    Erdbeere-Mix
    0
    0
    0
    9
    Jamaica Sun
    6
    6
    6
    10
    Waldbeeren
    0
    0
    0
    Look Up Table
    Row\Col
    A
    B
    C
    14
    LOOKUP Table
    Column 2
    Column 3
    15
    Look Up ValueProduct Name
    16
    Haselnuß-Walnuß-aromatisiert
    17
    Tiramisu
    2
    18
    Chocolate-colonial blend
    19
    Chocolate-europe aroma
    4
    20
    Chocolate-Cookies
    21
    Jamaica Sun
    6
    22
    Himbeere-Joghurt
    23
    Erdbeere-Quark
    8
    24
    Erdbeere-Mix
    25
    Banana-Chocolate-Split
    10
    26
    Waldbeeren
    27
    Kirsche
    12
    28
    Kirsche-grüner Apfel
    29
    Kirsche-Ananas
    14
    30
    Stracciatella
    31
    Limette-Käsekuchen
    16
    32
    grüner Apfel-Quark
    33
    Blutorange-Quark
    _...........................

    The characteristic , or as I would say the By product, of Intercept or Implicit Intersect for a single “breadth” Array that we utilise here is the returned single value in certain cells for a reference such as =A3:A10.
    It might slowly become apparent that I did not randomly pick the rows to be used for the normal formulas. Considering the imaginary help matrix discussed in post 2 for the reference = A3:A10, we will have for that help matrix corresponding to having the formula in any cell ( other than cells A3 through to A10 ) the following:
    Code:
    1
    2
    3
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    4
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    5
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    6
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    7
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    8
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    9
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    10
    Waldbeeren
    Waldbeeren
    Waldbeeren
    Waldbeeren
    Waldbeeren
    Waldbeeren
    11
    12
    13
    So, the outcome of this is that a reference, such as that in the first argument of our VLookUp formula, se to =A3:A10 will return ( without CS Entry ) the Look Up value we require. So this formula
    =VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE) or =VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
    in rows 3 to 10 in any spare column, such as column E will give the same results as in the other columns.

    The argument for using Implicit Intersection in such a way is that it is very quick.

    But I do not see that it is quicker than the formulas in column B

    May be I will.
    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/

  4. #4
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3

    Excel VLookUp. VLookUp Review. Simple formula and CSE

    This Post is to support the last Post. It is a review of a simple application of VLookUp.
    The VLookUp is reviewed in
    _1) Simple single value first argument form
    _2) Multiple value first argument form

    The example used is the same as that used in the demo of Intercept and Implicit Intersection for single breadth ranges in VLookUp in the last Post

    _1) Simples use of VLookUp ( non CSE )
    _1a) Single formula, like written in cell B3 this: = VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    Consider the final Screenshot below where the first 10 rows are to be used for our VLookUp formulas, and the range A16:C33 is the Look up table used in the VLookUp formula. ( It might help if you copy that to a spare worksheet when following the explanations. Take care to copy to the correct range area. Alternatively the worksheets “XLORX” and “Intercepts” in the uploaded file have a mixture of the various formulas and tables )

    Just to refresh what is going on: VLookUp is:
    https://support.office.com/en-us/art...8-93a18ad188a1
    Simplified VLOOKUP function says,
    _ =VLOOKUP( argument1 , argument2 , argument3 , argumant4 )
    _ =VLOOKUP( argument1 ,
    argument2 ,
    argument3 ,
    argumant4 )
    _ = VLOOKUP(A3 , $A$16:$C$33 , 3 , FALSE)
    _ = VLOOKUP(A3 ,
    $A$16:$C$33,
    3,
    FALSE)
    _ =VLOOKUP(Value you want to look up,
    range where you want to lookup the value,
    the column number in the range containing the return value,
    Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE
    )
    or
    [ Put this cell where the formula is ] equal to VLookUp(What is in a column to the right of the same word as is written here in the first column looking_..... ,
    _..... Look in this Look Up Table ,
    use this column number counting from the left in that Look Up Table ,
    False option here means look for the exact match in the words here in the formula first argument and in the first column in the Look Up Table )
    or
    = VLookUp(What is in a column to the right of the same word that is written here in the first column in.. ,
    Look in A16:C33 ,
    use this column number counting from the left in A16:C33 ,
    False )
    or
    [ I want returned here_.... ] = VLookUp( Use this word ,
    Find it here A16:C33 ,
    _...................return what is in this column number counting to the right ,
    False )

    _..................

    or
    just look at the screen considering the rows shown in red and follow this single formula through with the following explanation:
    ____ = VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    ____ = VLOOKUP(LookUpValue,LookUpTable,ColumnNumber,0)
    _0)_ Following on from the first post we will recognise a single cell reference in the formula, =A3. Alan Interception theory gives for that, very quickly, the word Chocolate-europe aroma
    This first argument, A3 ín the above example, is normally a single value. The whole point of our discussions in this thread is how we can go about using multiple values in that Function.
    A note to the second argument: In general for the VLookUp Function, the second argument is taken as a fixed Array which the coding behind the function gets if it is a reference. It likely does use processes we have discussed, but that is not relevant to our discussions here: As far as our discussions are concerned it is a fixed array looking like the Look Up table, so like the following could be typed in instead of $A$16:$C$33
    {"Haselnuß-Walnuß-aromatisiert","","";"Tiramisu","","2";"Chocolate-colonial blend","","";"Chocolate-europe aroma","","4";"Chocolate-Cookies","","";"Jamaica Sun","","6";"Himbeere-Joghurt","","";"Erdbeere-Quark","","8";"Erdbeere-Mix","","";"Banana-Chocolate-Split","","10";"Waldbeeren","","";"Kirsche","","12 ";"Kirsche-grüner Apfel","","";"Kirsche-Ananas","","14";"Stracciatella","","";"Limette-Käsekuchen","","16";"grüner Apfel-Quark","","";"Blutorange-Quark","",""}

    Back to the example workthrough:
    _0(i)_ In that cell where the formula is ( B3 ) , I put a number from the Look Up Table
    _0(ii)_ To get the number I
    _0(ii)a)_ Look for the word match of the word from the first VLookUp argument, Chocolate-europe aroma to the words in the LookUpTable, A16:C33.
    _0(ii)b)_ Once found, look to the right in the same row in column 3
    _0(ii)c)_ return the value in that column, 4
    _0(ii)d)_ The returned value of 4 is what the formula returns on hitting Enter

    _....
    1b) Multiple Formula use in typical Table of several rows. ( A3 is a relative reference! )
    Often, as in the final screenshot we are interested in getting a column of results.
    The formula is using the column letter and row number cell notation , like A1, A16:C33. &D3 etc. As noted the second argument is generally taken as the fixed Look Up Table.
    The use of the $ in Excel in a reference like, $A$16:$C$33, fixes the range which is then known as an absolute reference. It would only change in a situation when, for example, you insert a row before the Look Up Table, such as row 11 in the screenshot. In this case Excel would guess that you still want to a use the same Look Up Table, and so would change the range to $A$17:$C$34.
    Although not immediately obvious, a reference like A3 is defined as a relative reference. So A3 is referencing the cell relative to the position of where ( the cell ) the reference is in. Excel is just written that way. It is not always stated at an early stage in learning. So the point is often lost as to what happens if the formula is copied to, for example, one row down. If this is done the reference held by Excel remains relative to the cell it is in That is to say it refers in the case of A3, when place in C3, to the cell in the same row, but two columns offset to the left.
    Hence if the formals are copied , ( or dragged http://www.excelfox.com/forum/showth...t-99#post10024 ) down, then you will get like
    VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    VLOOKUP(A4,$A$16:$C$33,3,FALSE)
    VLOOKUP(A5,$A$16:$C$33,3,FALSE)
    etc: .. you see we preserve the relative column position of 2 to the left. The row was in the same ( so relatively speaking 0 rows up or down from ) row in which the formula is in.
    _1b) is a very typical use of the formula.

    _2) Multiple value first argument form
    The same results can be achieved with the use of multi values in the function in the place of where a single value argument is used. We mentioned that Excel usually lets us do that.
    _2b) Multi range reference argument. ( CSE )
    This was discussed in the first couple of posts as the usual way to get multi values out in such a spreadsheet usage.
    Type 2 CSE Entry is required as follows
    _2b(_a)_ select a 8x1 range in the worksheet, such as C3:10
    _2b(_b)_ Hit F2 or clicked in the formula bar
    _2b(_c)_ Type in this formula VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)
    _2b(_d)_ Hold down Ctrl + Shift , then hit Enter ( or hit simultaneously Ctrl+Shift+Enter)

    _2a) Multi value Array of values.
    We can simply use this formula,
    VLOOKUP({"Chocolate-europe aroma";"Chocolate-Cookies";"Banana-Chocolate-Split";"Limette-Käsekuchen";"Erdbeere-Quark";"Erdbeere-Mix";"Jamaica Sun";"Waldbeeren" }$A$16:$C$33,3,FALSE)
    As discussed in the first couple of posts, the general CS Entry is not now required to get the ( single in this case ) Controlled Shift , as the Array of values is already there, but The type 2 CS Entry is never the less required once again to extend the area effectively “revealed” to get the extended available values


    _..............................

    First Row of formulas
    (__¬___ Formula in B3 is to be dragged down _____ Column C formula is for type 2 CS Entry
    Row\Col B C
    3 =VLOOKUP(A3,$A$16:$C$33,3,FALSE) = VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)


    Example Look Up Values in Column A and returned values from Formula in column B and C
    Code:
    Row\Col A B C
    1 Produnt Name
    2 Given Look Up Value Returned by formula in cell Returned by formula in cell
    3 Chocolate-europe aroma 4 4
    4 Chocolate-Cookies 0 0
    5 Banana-Chocolate-Split 10 10
    6 Limette-Käsekuchen 16 16
    7 Erdbeere-Quark 8 8
    8 Erdbeere-Mix 0 0
    9 Jamaica Sun 6 6
    10 Waldbeeren 0 0
    11
    12
    13
    Look Up Table
    Code:
    Row\Col A B C
    14 LOOKUP Table Column 2 Column 3
    15 Look Up ValueProduct Name
    16 Haselnuß-Walnuß-aromatisiert
    17 Tiramisu 2
    18 Chocolate-colonial blend
    19 Chocolate-europe aroma 4
    20 Chocolate-Cookies
    21 Jamaica Sun 6
    22 Himbeere-Joghurt
    23 Erdbeere-Quark 8
    24 Erdbeere-Mix
    25 Banana-Chocolate-Split 10
    26 Waldbeeren
    27 Kirsche 12
    28 Kirsche-grüner Apfel
    29 Kirsche-Ananas 14
    30 Stracciatella
    31 Limette-Käsekuchen 16
    32 grüner Apfel-Quark
    33 Blutorange-Quark
    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
  •