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 |
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 |
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
Reply With Quote
Bookmarks