Excel VBA Interception and Implicit Intersection and VLookUp

**DocAElstein** · 03-15-2017, 04:49 AM

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

**DocAElstein** · 03-15-2017, 05:00 AM

[

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,