Spreadsheet Functions to return multiple values when they usually return just one and VLookUp
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"
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.
Row\Col
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.
Reply With Quote
Bookmarks